VBA Macro to Export Data from Excel Spreadsheet to CSV

Question

Situation: Here is what i have working now. The VBA code will save a copy of the current sheet as an archive file in .CSV format. I'm able to assigned it to a button and it will do it on a sheet by sheet basses. This means what ever sheet the button is assigned to it will export that sheet and label it accordingly.

Help Needed: I need help altering the code so that i can also select the file path or provide it a file path directly so i don't have to do it manually.

This is what i found online and tried. Link

Sub Export_CSV()

    '***************************************************************************************
    'purpose:   export current spreadsheet to csv.file to the same file path as source file
    '
    ' !!NOTE!!  files with same name and path will be overwritten
    '***************************************************************************************
 
    Dim MyPath As String
    Dim MyFileName As String
    Dim WB1 As Workbook, WB2 As Workbook
    
    Set WB1 = ActiveWorkbook

    '(1) either used range in active sheet..
    'ActiveWorkbook.ActiveSheet.UsedRange.Copy
    
    '(2) or alternatively, user selected input range:
    Dim rng As Range
    Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8)
    Application.ScreenUpdating = False
    rng.Copy

    Set WB2 = Application.Workbooks.Add(1)
    WB2.Sheets(1).Range("A1").PasteSpecial xlPasteValues
    
    MyFileName = "CSV_Export_" & Format(Date, "ddmmyyyy")
    FullPath = WB1.Path & "\" & MyFileName
    
    Application.DisplayAlerts = False
    If MsgBox("Data copied to " & WB1.Path & "\" & MyFileName & vbCrLf & _
    "Warning: Files in directory with same name will be overwritten!!", vbQuestion + vbYesNo) <> vbYes Then
        Exit Sub
    End If
    
    If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
    With WB2
        .SaveAs Filename:=FullPath, FileFormat:=xlCSV, CreateBackup:=False
        .Close False
    End With
    Application.DisplayAlerts = True
End Sub

Perhaps this may give you an idea: https://stackoverflow.com/q/30575923/4961700 — Solar Mike, Mar 18 '23 at 19:07

score 3 · Accepted Answer · answered Mar 18 '23 at 19:25

Dim myfolder As String   
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Please select a folder"
    .Show
    .AllowMultiSelect = False
    If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
        MsgBox "You did not select a folder"
        Exit Sub
    End If
    myfolder = .SelectedItems(1) 'Assign selected folder to myfolder
End With

VBA Macro to Export Data from Excel Spreadsheet to CSV

1 Answers1