CODEWITHSUNDEEP
excelvba
mitchmitch24
mitchmitch24

Reputation: 383

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

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!!

Upvotes: 0

Views: 347

Answers (2)

Imran Malek
Imran Malek

Reputation: 1719

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))

Upvotes: 1

adarti
adarti

Reputation: 87

In the loop declaration should be:

For i = 0 To UBound(myvariable)

Upvotes: 0

Related Questions