Loop problem with macro enabled workbook

307Fergie

New Member
Joined
Jan 13, 2019
Messages
7
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

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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top