CODEWITHSUNDEEP
excelcsvvba
Abhinav Varma
Abhinav Varma

Reputation: 23

Converting XLS/XLSX files in a folder to CSV

I have written the following code in VBA. When debugging, I am not able to find any problems. It is not creating nor converting any file into .CSV.

Sub SaveToCSVs()
    Dim fDir As String
    Dim Wb As Workbook
    Dim wS As Worksheet
    Dim csvWs As String, csvWb As String
    Dim extFlag As Long '0 = .xls & 1 = .xlsx extension types
    Dim fPath As String
    Dim sPath As String, dd() As String
    fPath = "C:\Users\DA00358662\Documents\XLSCONV\*.*"

    sPath = "C:\Users\DA00358662\Documents\XLSCONV\"
    fDir = Dir(fPath)
    extFlag = 2
    Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then
            extFlag = 0
        Else
            extFlag = 2
        End If
        On Error Resume Next
        If extFlag = 0 Then
            fDir = Dir
            Set Wb = Workbooks.Open(fPath & fDir)
            csvWb = Wb.Name
            dd = Split(csvWb, ".")
            For Each wS In Wb.Sheets
                wS.SaveAs dd(0) & wS.Name & ".csv", xlCSV
            Next wS
            Wb.Close False
            Set Wb = Nothing
            fDir = Dir
            On Error GoTo 0
        End If
    Loop
End Sub

Upvotes: 2

Views: 19141

Answers (2)

Verzweifler
Verzweifler

Reputation: 940

The Moment you concatenate fPath and fDir to open your Workbook, you get something like:

"C:\Users\DA00358662\Documents\XLSCONV\*.*MyWorkbook.xls"

Note *.* in the middle ruining your day. I think you want to use sPath here?

Upvotes: 0

Fabrizio
Fabrizio

Reputation: 662

with this code (standard for my use) you can find that you need (modify as your need). In short the code ask which directory to loop and for each file, with the corresponding extension, in this directory it open file, save as csv in the some directory, and close the original file.

Sub SaveAsCsv()
Dim wb As Workbook
Dim sh As Worksheet
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then Exit Sub

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myPath & myFile)
    nameWb = myPath & Left(myFile, InStr(1, myFile, ".") - 1) & ".csv"
    ActiveWorkbook.SaveAs Filename:=nameWb, FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    'Get next file name
      myFile = Dir
  Loop
'Reset Macro Optimization Settings
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
End Sub

Upvotes: 7

Related Questions