CODEWITHSUNDEEP
excelvba
Stormtalon
Stormtalon

Reputation: 47

Excel - exporting as csv and saving to another location

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:

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

Upvotes: 2

Views: 438

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

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

Upvotes: 4

Related Questions