Method SaveAs Fails with 1004 error unless destination is same location as PERSONAL.XLSB

[srohde60]

1 of 4 • DESIRED WORKFLOW

1. Open a one-sheet XLSX file (the source file) anywhere on my computer.
2. From the source file, run a macro from my PERSONAL.XLXB file that...
   1. copies the sheet in the source file
   2. saves it as a CSV file in the same folder as the source file.

Here's my code:

Sub SaveAS_CSV_to_same_folder_as_source()

' Copy a sheet in from a source XLSX file as a CSV file in the same folder as the source folder
' User runs this macro from the srouce XLSX file

    Dim sourceFile As Object
    Dim inputSheet As Object
    Dim outputFile As Object
    
    Dim destinationPath As String
    Dim curDateTime As String
    Dim myFileName As String

 
'Set object for raw data source XLSX file.
'NOTE: User runs this macro from the source XLSX file
    Set sourceFile = Application.ActiveWorkbook
       
'Set object for the sheet to export from the data source XLSX file
    Set inputSheet = Application.ActiveSheet

'Get the path for the source XSLX file
    destinationPath = sourceFile.Path

'Set the filename for the exported CSV file
    curDateTime = Application.WorksheetFunction.Text(Now, "yyyymmddThhmmss")
    myFileName = curDateTime & "_test.csv"
   
Application.DisplayAlerts = False
        
'Copy of the sheet in the source data sheeet to create the output file
    inputSheet.Copy
            
'Set output file object for the new workblook created just above
    Set outputFile = ActiveWorkbook

'Close the source file
    sourceFile.Close
            
'[Not sure this this is really needed since the full path is provided in the SaveAs statement below]
    ChDir destinationPath
            
'Save the exported CSV file
    ActiveWorkbook.SaveAs FileName:=destinationPath & "/" & myFileName, _
    FileFormat:=xlCSVUTF8, CreateBackup:=False

Application.DisplayAlerts = True
        
End Sub

2 of 4 • ISSUE

When the macro runs, the SaveAs method fails at line 44 with a 1004 error.

3 of 4 • WORKAROUND

I learned from this post (SOLVED - "Method 'SaveAs' of object '_Workbook' failed" (1004) when saving into same file location ) that SaveAs will work when the destination folder is the location of the PERSONAL.XLSB file.  Here's a version of my code above that does this successfully (line 21 is where I get the path of the PERSONAL.XLSB file) ...

Sub SaveAS_CSV_to_PERSONAL_macrobook_folder()
'
' Copy a sheet in from a source XLSX file as a CSV file in the same folder as PERSONAL.XLSB
' User runs this macro from the srouce XLSX file

    Dim sourceFile As Object
    Dim inputSheet As Object
    Dim outputFile As Object
    
    Dim destinationPath As String
    Dim curDateTime As String
    Dim myFileName As String

 
'Set object for raw data source XLSX file.
    Set sourceFile = Application.ActiveWorkbook
       
'Set object for the sheet to export from the data source XLSX file
    Set inputSheet = Application.ActiveSheet

'Get the path for PERSONAL.XLSB file
    destinationPath = ThisWorkbook.Path

'Set the filename for the exported CSV file
    curDateTime = Application.WorksheetFunction.Text(Now, "yyyymmddThhmmss")
    myFileName = curDateTime & "_test.csv"
   
Application.DisplayAlerts = False
        
'Copy of the sheet in the source data sheeet to create the output file
    inputSheet.Copy
            
'Set output file object for the new workblook created just above
    Set outputFile = ActiveWorkbook

'Close the source file
    sourceFile.Close
            
'[Not sure this this is really needed since the full path is provided in the SaveAs statement below]
    ChDir destinationPath
            
'Save the exported CSV file
    ActiveWorkbook.SaveAs FileName:=destinationPath & "/" & myFileName, _
    FileFormat:=xlCSVUTF8, CreateBackup:=False

Application.DisplayAlerts = True
   
        
End Sub

4 of 4 • QUESTION

Although it's nice to see that VBA can actually save a CSV file, albeit to the folder that contains the PERSONAL.XLSB file, I'd really like to save the CSV file to same folder that contains my one-sheet XLSX file.  How can I do this?  What's causing the 1004 error in my code provided in section 1 of 4 above?

Thanks!

[#2]

@srohde60 

The only issue I see is at line#44 where you are still using ActiveWorkbook.SaveAs, try to replace that line with the following one and see if this resolves the issue for you...

'Save the exported CSV file
    outputFile.SaveAs Filename:=destinationPath & "/" & myFileName, _
    FileFormat:=xlCSVUTF8, CreateBackup:=False

Otherwise, your code looks fine. I tested it and it worked for me without any issue.

[#3]

@Subodh_Tiwari_sktneer 

Thanks for your response and for catching my oversight with line #44; however, the issue persists, even after replacing ActiveWorkbook.SaveAs with outputFile.SaveAs.   

I'm not alone with the issue.  Just search online for "SaveAs Fails with 1004 error". 

For me, SaveAs works for other file formats (for example xlOpenXMLWorkbook), but not for CSV file types, i.e. xlCSVUTF8 xlCSVMac  and xlCSV. 

I'm using Excel for Mac, v 16.61.1 on macOS 12.3.1.