Converting XLS/XLSX files in a folder to CSV
17,087
Solution 1
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?
Solution 2
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
Author by
Admin
Updated on June 15, 2022Comments
-
Admin almost 2 years
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