CODEWITHSUNDEEP
excelvbacsvexport-to-csvfile-format
Stewart Gray
Stewart Gray

Reputation: 33

Save file as csv, keeps coming up as .xlsx

I am trying to output a copy of a workbook as a CSV File. I have the code below but on saving it comes up in file type as Excel workbook.

Sub SAVE_CSV()

Dim FileName As String
FileName = "CSV Import File"

Dim fPth As Object
Set fPth = Application.FileDialog(msoFileDialogSaveAs)

With fPth
    .InitialFileName = FileName
    .Title = "Save Your Import File"
    .InitialView = msoFileDialogViewList
    If .Show <> 0 Then
        ThisWorkbook.SaveAs FileName:=.SelectedItems(1) & "*.csv", FileFormat:=xlCSV
    End If
End With

Upvotes: 0

Views: 448

Answers (2)

Error 1004
Error 1004

Reputation: 8220

Replace with:

ThisWorkbook.SaveAs FileName:=.SelectedItems(1) & ".csv"

Follow the below logic:

  1. Path - Directory
  2. "\"
  3. File Name
  4. File type - .csv

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

You know the name you want to save it as, so maybe it's just a case of selecting the right folder: 

Sub SAVE_CSV()

    Dim FileName As String
    FileName = "CSV Import File Again"

    Dim fPth As Object
    Set fPth = Application.FileDialog(msoFileDialogFolderPicker)

    With fPth
      .InitialFileName = "C:\Users\Testing\Documents\Can be deleted\" 'Change as required.
      .Title = "Save Your Import File"
      .InitialView = msoFileDialogViewList

      If .Show <> 0 Then
        ThisWorkbook.SaveAs FileName:=.SelectedItems(1) & "\" & FileName & ".csv", FileFormat:=xlCSV
          End If
    End With

End Sub

Upvotes: 1

Related Questions