Excel: Multiple File Run, Save Each File name as variable

Question

I have a code that works for one file. It reads the name of the .xlsx file and saves the Application.GetOpenFilename as a variable. This is then used later in the code.

I want to be able to run this code on multiple files... example:

User selects files names "x","y", and "z"

Sub Test()
  Dim myvariable as variant, i As Integer
  myvariable = Application.GetOpenFilename(FileFilter:="Excel files (*.xlsx), *.xlsx", MultiSelect:=True)

  On Error GoTo ERRORHANDLER
  For i = 1 To UBound(myvariable)
    '''code to do stuff to my variable'''
  Next i

  Exit Sub

ERRORHANDLER:
  MsgBox "No files were selected, action cancelled."

End Sub

This code should run each time, doing the stuff to "x", then "y", then "z" but I keep getting the ERRORHANDLER message.

Update: When I put MsgBox ("Hello") in the "code to do stuff" it does print it for as many files as I select. When I write MsgBox (myvariable) I get that error, so I assume it is because Excel doesnt know how to save each individual filename as the variable during each loop through the code. –

Any help would be appreciated!!

Comment out the error handling - where exactly does it fail and what is the error? You code attributes all types of error to "no files selected": that's not necessarily accurate. — Tim Williams
Hi Tim, I commented it out, and get the "Run time error 13, Type Mismatch" (I also tried changing the i = 1 to i = 0 as someone commented below, but got the same run time error. — mitchmitch24
When I put MsgBox ("Hello") in the "code to do stuff" it does print it for as many files as I select. When I write MsgBox (myvariable) I get that error, so I assume it is because Excel doesnt know how to save each individual filename as the variable during each loop through the code. — mitchmitch24

Imran Malek Imran Malek · Accepted Answer · 2017-10-04T13:16:53

It is always good to print error description unless you know the exact error. You can find out the error by putting following line under your error handler.

MsgBox Err.Description

I tried your code with multiple selection after putting the simple msgbox and it does seem to work for me. Check if you are missing i to refer the array.

MsgBox (myvariable(i))

Excel: Multiple File Run, Save Each File name as variable

2 Answers