MS VBA: Export to CSV fails on some PCs not others

[Wikus_]

Hello
We're having an issue with a simple piece of VBA code which we are using to export a couple of sheets from an MS Excel workbook as CSV files. The code works perfectly on my PC and one other, but on two other people's PCs it fails to save the two CSV files. I have stepped through the code piece by piece on one of the failing PCs and everything seems to work, up until the line where the "SaveAs" method is called. There are no error messages given, and the files don't save.

We are all using MS 365 for Business - version info as follows: Microsoft® Excel® for Microsoft 365 MSO (Version 2209 Build 16.0.15629.20200) 64-bit.

The files are being saved to a shared OneDrive folder. Thinking it was a OneDrive sync issue we tested it on a failing PC in their C: drive - still no success. Code is below - thanks in advance!

Sub ExportAsCSV()

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook

    Set CurrentWB = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.UsedRange.Copy

    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
 
    MyFileName = CurrentWB.Path & "\" & CurrentWB.ActiveSheet.Name & ".csv"

    Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    TempWB.Close SaveChanges:=False
    Application.DisplayAlerts = True

End Sub

 

[Joe4]

Welcome to the Board!

Try re-testing it on those computer WITHOUT disabling the "DisplayAlerts" so you can see what messages that it wants to return to you.
And verify the file name with a MsgBox, i.e.

Sub ExportAsCSV()

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook

    Set CurrentWB = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.UsedRange.Copy

    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
 
    MyFileName = CurrentWB.Path & "\" & CurrentWB.ActiveSheet.Name & ".csv"
    MsgBox MyFileName

    'Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    TempWB.Close SaveChanges:=False
    'Application.DisplayAlerts = True

End Sub

Run that code on the offending computers, and verify the file name it is trying to save the file to (and verify it is valid/correct), and then see what messages now pop up.
This might help shed some light on what is going on.

 

[Wikus_]

Welcome to the Board!

Try re-testing it on those computer WITHOUT disabling the "DisplayAlerts" so you can see what messages that it wants to return to you.
And verify the file name with a MsgBox, i.e.

Sub ExportAsCSV()

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook

    Set CurrentWB = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.UsedRange.Copy

    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
 
    MyFileName = CurrentWB.Path & "\" & CurrentWB.ActiveSheet.Name & ".csv"
    MsgBox MyFileName

    'Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    TempWB.Close SaveChanges:=False
    'Application.DisplayAlerts = True

End Sub

Run that code on the offending computers, and verify the file name it is trying to save the file to (and verify it is valid/correct), and then see what messages now pop up.
This might help shed some light on what is going on.

Thanks Joe!

Reran the code on both my machine and an offending machine. In both cases the filenames came up in message boxes, and they were identical on both machines. On my machine, the two CSV files saved successfully, but on the offending machine, saving didn't occur. No other error messages came up. Any ideas?

 

[Joe4]

You commented out the two "Application.DisplayAlerts..." lines, like I showed above, right?
If you did, and it is not returning any messages, I cannot really say what the problem is, other than the fact that perhaps that user does not have access to save files to that directory.

 

[Wikus_]

You commented out the two "Application.DisplayAlerts..." lines, like I showed above, right?
If you did, and it is not returning any messages, I cannot really say what the problem is, other than the fact that perhaps that user does not have access to save files to that directory.

Sure did, and the issue is persisting!
I checked permissions on the online version of the OneDrive folder and it seems my other users have sufficient permissions, but I may be looking in the wrong place.

 

[Wikus_]

Something to add here that may/may not be a factor. I am calling this ExportAsCSV Sub only upon exiting from Excel, using a Sub located in the "ThisWorkbook" object. This sub allows me to run ExportAsCSV on multiple tabs in one go. Could there be an issue with this setup? The Sub from ThisWorkbook is copied below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

answer = MsgBox("Generate input files and replace existing files?", vbYesNo)
If answer = vbNo Then Exit Sub

Dim ws As Worksheet
WkSheets = Array("<filename1>", "<filename2>")

For Each ws In Sheets(Array("<filename1>", "<filename2>"))
ws.Select
Call ExportAsCSV
Next ws

End Sub

 

[Joe4]

"Workbook_BeforeClose" event procedure VBA code MUST go in the "ThisWorkbook" module in order for it to run automatically, so I don't think that is the problem.

I am not sure if it makes any difference, but if you have the "ExportAsCSV" procedure in the "ThisWorkbook" module, you may want to try creating a general module and moving it there.
Try that and see if that makes any difference.