Hi,
I'm attempting to use a script I found on the net for a macro to batch convert a large number of XLS files to CSV format, specifically a particular sheet from each file, then output these CSV files to a separate folder with the same name as the original XLS files but with the .csv extension.
However, whenever I run the macro I get a "Run-time error '9': Subscript out of range" error, and in debug mode it highlights this line:
Workbooks(vFile).Close SaveChanges:=False
Why would I be getting this error when just trying to close the file?
I'm a complete newbie to this so sorry if it's something blatantly obvious and simple!!
Any help would be appreciated!!
The complete script is as follows:
Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim stFile As String, vFile As Variant
Dim stDirectory As String
Dim stCSV As String
Dim stCSVDir As String
' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file
stDirectory = "C:\Dave\Completion reports\" ' name of directory to look in
stCSVDir = "C:\Dave\Completion reports\CSV\" ' where to put the CSV files
' use Dir function to find XLS files in Directory
stFile = Dir(stDirectory & "*.XLS")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile <> ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop
' now process the files
Application.DisplayAlerts = False ' no messages about overwriting
For Each vFile In aFiles
Workbooks.Open stDirectory & vFile
Sheets("PLUMA ").Select
stCSV = Application.Substitute(vFile, ".xls", ".csv")
Workbooks(vFile).SaveAs stCSVDir & stCSV, FileFormat:=xlCSV
Workbooks(vFile).Close SaveChanges:=False
Next vFile
Bookmarks