Excel - exporting as csv and saving to another location

Question

I'm trying to make a command button in Excel, but after a lot of searching and trying different codes on the internet I can't seem to find one that suits my personal needs. I want this to happen after I press the button:

Export and save the opened Excel file to another file location, I.e. c:/test.
The name of the document should be the same as the Excel file.

So far the best code I have found after scavenging the internet is this. The problem is that the end user has to physically press "save" in order to save the file.

Dim folderPath As String Dim csvFile As String folderPath = "C:\Users\username\Documents" csvFile = Application.GetSaveAsFilename(InitialFileName:=folderPath, _ FileFilter:="CSV Files (*.csv), *.csv", Title:="Save As CSV") If csvFile <> "" And csvFile <> "False" Then Application.ScreenUpdating = False ActiveSheet.Copy On Error Resume Next ActiveWorkbook.SaveAs fileName:=csvFile, FileFormat:=xlCSV, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False, ConflictResolution:=xlUserResolution On Error GoTo 0 'Close .csv workbook, leaving original workbook open ActiveWorkbook.Close SaveChanges:=False Application.ScreenUpdating = True End If End Sub

FaneDuru · Accepted Answer · 2021-01-30 16:42:35Z

Please, try the next simplified code. The user does not have to press anything, except to run the code. It will anyhow save only the active sheet:

Sub SaveCSV() Dim folderPath As String, csvFile As String folderPath = "C:\test" 'if test folder does not exist, it will be created: If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath 'The csv file name is obtained by replacing the active workbook extension: csvFile = folderPath & "\" & Split(ActiveWorkbook.Name, ".")(0) & ".csv" ActiveWorkbook.SaveAs filename:=csvFile, FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False End Sub

Thanks a lot for the help! :) Really appreciate it. I still have two small small problems left, when the workbook is saved as a csv: - it is not possible to continue to work on the file after the button is pressed, Does it have to do with ActiveWorkbook.Close SaveChanges:=False ? - If there already exists a file in the folder named the document name, I want it do automatically to be written over, instead of the dialogue frame opening up and asking to write over the file name.
@Stormtalon: Glad I could help! But we here, when somebody answer our question, tick the code left side check box, in order to make it accepted answer. In this way, somebody else searching for a similar issue will know that the solution works... :)

Collectives™ on Stack Overflow

Excel - exporting as csv and saving to another location

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related