I have a macro enabled workbook through which the user can select a single .csv file, which is then edited by a macro in the relevant module, and the edited version saved alongside the original, in the original folder. I am now trying to change it so the user can select multiple files and have each processed in the same way. Unfortunately I have no got this correct and it stalls at the end of the module macro. Not sure why. I should point out that I am very new to this, the original macros I’m working with were written by a colleague who is no longer with us. I was involved in this but only as a user, not writing the code. I have had to use textbooks and online to try and understand this and modify his code. This is my first attempt at a multi selection code and I have clearly misunderstood something. No sure if it the save code, although I can’t see why this should change, or the loop. Any help would be greatly appreciated.
Here is the relevant code from the single select version which works
And my attempt at a multi selection version, which doesn’t!
Here is the relevant code from the single select version which works
Code:
### TO OPEN FILE ###
vfile = Application.GetOpenFilename("Excel Files (*.csv*), *.csv", 1, "Select Excel File", "Open", False)
If TypeName(vfile) = "Boolean" Then 'If Cancel then exit
Exit Sub
End If
'Open the selected file
Workbooks.Open vfile, Local:=True
‘ Identify source of file to call appropriate module
‘1 CT24
Application.Goto Reference:="R1C2"
If ActiveCell.Value = 0 Then
' This identifies the file as from a CT24 as being the only file type with no data at R1C2 and calls the appropriate macro
Call Module1.ct24
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If
'2. Datong Rapids or GC-101
Application.Goto Reference:="R1C7"
z = ActiveCell.Value
If Mid(z, 1, 4) = "AUTO" Then
Call Module2.GC101
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
Else
Call Module3.Datong
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If
End Sub
### TO SAVE at end of Module 1 macro ###
MyFileName = vfile
length = Len(MyFileName) - 4
MyFileName = Mid(MyFileName, 1, length)
MyFileName = MyFileName & "- Stopped.csv"
Application.DisplayAlerts = False
ActiveSheet.Columns("A:O").EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:=MyFileName
Application.DisplayAlerts = True
MsgBox "All Done - Next Please"
ActiveWorkbook.Close False
Exit Sub
Exit Sub
Err1:
MsgBox "Unexpected error - In " & info, , "Tell Fergie!"
Exit Sub
End Sub
And my attempt at a multi selection version, which doesn’t!
Code:
### TO OPEN ###
vfile = Application.GetOpenFilename("Excel Files (*.csv), *.csv", _
Title:="Select files", MultiSelect:=True)
If TypeName(vfile) = "Boolean" Then 'If Cancel then exit
Exit Sub
End If
If IsArray(vfile) Then
For i = LBound(vfile) To UBound(vfile)
Set wbkToCopy = Workbooks.Open(Filename:=vfile(i), Local:=True)
Application.Goto Reference:="R1C2"
If ActiveCell.Value = 0 Then ' the CT24 file has all data in Column A
Call Module1.ct24
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If
'2. Datong Rapids or GC-101
Application.Goto Reference:="R1C7"
z = ActiveCell.Value
If Mid(z, 1, 4) = "AUTO" Then
Call Module2.GC101
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
Else
Call Module3.Datong
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If
Next i
End If
'MsgBox "All Done - Next Please"
Exit Sub
End Sub
### TO SAVE ###
'Save file as filename + Stopped as csv
MyFileName = vfile
length = Len(MyFileName) - 4
MyFileName = Mid(MyFileName, 1, length)
MyFileName = MyFileName & "- Stopped.csv"
Application.DisplayAlerts = False
ActiveSheet.Columns("A:O").EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:=MyFileName
Application.DisplayAlerts = True
ActiveWorkbook.Close False
Exit Sub
Exit Sub
Err1:
MsgBox "Unexpected error - In " & info, , "Tell Fergie!"
Exit Sub
End Sub