I'm trying to write code in VBA in Excel that opens a desginated directory, finds all xls and xlsx files and then converts them to csv. I found two sets of code that do one or the other perfectly, but I can't make it work.
I'm sure I'm just missing one step but the more I tweak the code the less it works:
Sub select_rows() strPath = "C:\temp\pydev" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False Set objFso = CreateObject("Scripting.FileSystemObject") Set objFolder = objFso.GetFolder(strPath) For Each objFile In objFolder.Files If (objFso.GetExtensionName(objFile.Path) = "xls" Or objFso.GetExtensionName(objFile.Path) = "xlsx") Then Set objWorkbook = objExcel.Workbooks.Open(objFile.Path, ReadOnly) ' Include your code to work with the Excel object here MsgBox (objFile.Name) ' this is just scaffolding to make sure it opens the right files. Dim WS As Excel.Worksheet Dim SaveToDirectory As String Dim CurrentWorkbook As String Dim CurrentFormat As Long CurrentWorkbook = objFile.FullName ' CurrentFormat = ThisWorkbook.FileFormat ' Store current details for the workbook SaveToDirectory = "C:\temp\" For Each WS In CurrentWorkbook.Worksheets Sheets(WS.Name).Copy ActiveWorkbook.SaveAs Filename:=SaveToDirectory & objFile.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close savechanges:=False ThisWorkbook.Activate Next Application.DisplayAlerts = False ' ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat Application.DisplayAlerts = True ' objWorkbook.Close True 'Save changes End If Next objExcel.Quit End Sub