VBA to save multiple tabs as CSV, then save primary file as original name

DEllis81

Board Regular
Joined
Jan 19, 2009
Messages
106
Good Morning!

I have an excel file - with multiple tabs generated via power query - that i would now like to convert those individual tabs to CSV. For every CSV file created - changes the name of the active file to my designated csv name. Then at the end - I want to save the file back to the original name. However I get an error message with multiple potential reasons -

Code:
Sub SaveFiles()
'
' SaveFiles Macro
'
    Application.DisplayAlerts = False
    
    Dim MasterDirectory As String
'   MasterDirectory = ActiveWorkbook.Sheets("DirectorySetup").Range("A4")
    Dim MasterFileName As String
    MasterFileName = ActiveWorkbook.Sheets("DirectorySetup").Range("C4") & ".xlsm"
    
    Dim FBSImportDirectory As String
    FBSImportDirectory = ActiveWorkbook.Sheets("DirectorySetup").Range("A7")
    
    Range("D1").Select
    ActiveWorkbook.Save
    Sheets("PackerImport").Select
    Range("A2").Select
    ChDir "C:\FBSPacker\FBSImportFiles"
    ActiveWorkbook.SaveAs Filename:= _
        FBSImportDirectory & "PackerImport" & ".csv", FileFormat:=xlCSV, _
        CreateBackup:=False
    ActiveSheet.Name = "PackerImport"
    Sheets("GroupMap").Select
    Range("D1").Select
    ChDir "C:\FBSPacker"
    ActiveWorkbook.SaveAs Filename:=MasterDirectory & MasterFileName, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        
    Application.DisplayAlerts = True
    MsgBox Prompt:="Files Saved"
End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DEllis81

Board Regular
Joined
Jan 19, 2009
Messages
106
Sorry - my original post was inadvertantly sent and I exceeded the 10 minute limit. Then I see I copied the wrong VBA example. Following example is problematic - the intial code provided is from a differing workbook, that appears to be working.


Good Morning!

I have an excel file - with multiple tabs generated via power query - that i would now like to convert those individual tabs to CSV. For every CSV file created - changes the name of the active file to my designated csv name. Then at the end - I want to save the file back to the original name. However I get an error message with multiple potential reasons with the reason I suspect - "The workbook you are trying to save has the same name as a currently open workbook."

The resulting error occurs the two lines after the ChDir "\\SERVER2\Shared\Daryl\Fuel" (6 lines from bottom)

Also - what I don't understand - I have the same basic macro in another file, that seems to be working. Any thoughts? Always appreciate the help and support from forums!

Sub SaveFiles()
'
' SaveFiles Macro
'

'
Application.DisplayAlerts = False
Sheets("DataLog").Select
Range("A1").Select
ActiveWorkbook.Save
Sheets("GSF Application Import").Select
ActiveWorkbook.SaveAs Filename:= _
"\\SERVER2\Shared\Daryl\Fuel\Import\GSF Application Import.csv", FileFormat:= _
xlCSV, CreateBackup:=False
Sheets("GFF AP Import").Select
ActiveWorkbook.SaveAs Filename:= _
"\\SERVER2\Shared\Daryl\Fuel\Import\GFF AP Import.csv", FileFormat:=xlCSV, _
CreateBackup:=False
Sheets("GSF AR Import").Select
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"\\SERVER2\Shared\Daryl\Fuel\Import\GSF AR Import.csv", FileFormat:=xlCSV, _
CreateBackup:=False
Sheets("DataLog").Select
ChDir "\\SERVER2\Shared\Daryl\Fuel"
ActiveWorkbook.SaveAs Filename:="\\SERVER2\Shared\Daryl\Fuel\FuelLog.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox Prompt:="Files Saved"

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,465
Messages
5,624,894
Members
416,064
Latest member
PaulBr2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top