2

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
Community
  • 1
  • 1
Abhinav Varma
  • 23
  • 1
  • 1
  • 5
  • !?!?!?!? hi you whant to save each sheets for each workbooks with the same name!??!?! because if I see your code (without error) it's that will happens – Fabrizio Oct 29 '15 at 15:50
  • wS.SaveAs dd(0) & wS.Name & ".csv", xlCSV is the actual line I am using there. That was just for seeing if atleast one .csv will be created. Please see the line, I have edited it. thank you for correcting. – Abhinav Varma Oct 30 '15 at 07:27

2 Answers2

7

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
Fabrizio
  • 662
  • 1
  • 7
  • 23
  • This answer really helped save me hours. Thank you so much! – p.campbell May 08 '17 at 17:20
  • Thanks, works perfectly besides minor bug, declare variable nameWb as string in the declaration statements – Miles Dec 02 '18 at 00:24
  • Note that you will get by default the English locale for Dates and Numbers. To preserve the same formats you get with manually clicking "Save as -> CSV" one can add a `,Local:=True` at the end of the `ActiveWorkbook.SaveAs` Line – Ralf Sep 20 '21 at 13:05
0

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?

Verzweifler
  • 930
  • 6
  • 16