Excel - exporting as csv and saving to another location

user_15112784 0
Viewed 59

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
vba excel
1 Answers

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
user_22333080
Related