VB Script - Macro on multiple workshets, save to csv

Good Morning,

I am attempting to run what I thought what was some very basic code, and am running into a wall.

In a nutshell. Have a macro in one workbook. From that workbook I want to loop through worksheets in other workbooks (all in the same directory) then dump the contents of those worksheets into individual .csv files. The progam runs fine until it goes to save the .csv files.

The code is below. Any help would be greatly appreciated.

Option Explicit
Sub Open_Dir()

Dim FSO, FLD, FIL, XL
Option Explicit
Sub Open_Dir()

Dim FSO, FLD, FIL, XL
Dim strFolder, sFileName, sFull_File_Path

Dim newWks As Worksheet
Dim wks As Worksheet

'############ Set up the folder
strFolder = "C:\Backup2\"

'Create the filesystem object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder(strFolder)

' 'loop through the folder and get the file names
For Each FIL In FLD.Files

'Get the file name
sFileName = FIL.Name

'String together the path and file name
sFull_File_Path = strFolder & sFileName

'Set up the Excel file
Set XL = CreateObject("Excel.application")
XL.Application.Workbooks.Open sFull_File_Path
XL.Application.Visible = False

'Do your thing here
For Each wks In XL.ActiveWorkbook.Worksheets

Debug.Print wks.Name
'wks.Copy 'to a new workbook
'Set newWks = ActiveSheet
With wks
'Code stops here
.SaveAs FileName:=strFolder & wks.Name, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks

MsgBox "done with: " & ActiveWorkbook.Name

Debug.Print sFileName


'End the Excel file
Set XL = Nothing

'Get the next file
Next

End Sub

You are trying to save a SHEET using SaveAs whereas you can only save a WORKBOOK. Change to thus:


Debug.Print wks.Name
'wks.Copy 'to a new workbook
'Set newWks = ActiveSheet
With wks
.Copy ' Copies the sheet to a newworkbook
'Code stops here
Activeworkbook.SaveAs FileName:=strFolder & wks.Name, FileFormat:=xlCSV
Activeworkbook.Close savechanges:=False
End With
Next wks

Just a thought, your "Save as" is only based on the sheet name, if you have e.g. a "Sheet1" in several of your workbooks then they will eachoverwrite each other except the last one