I have a macro enabled workbook which enables the user to select a .csv file and then edits it before saving the edited version alongside the original, in the original file location. This works fine. I am now trying to modify it so the user can select multiple files and have each processed in exactly the same way. Unfortunately this is stalling at the end of the macro module and I can't understand why. I should point out that I am very new to this. The macros were originally written by a colleague who is no longer with us. I was involved but only as a user, not actually writing the code. I have had to learn this from textbooks and online to try and understand his code and modify it. This is my first attempt at a multi selection version and I have clearly messed it up somewhere. Any help would be most appreciated.
This is the relevant code from the single selection which works.
### 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 SubAnd this is my attempt at modifying it for a multiple selection, which doesn't
v### TO OPEN ###
file = 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