Files stored in an array

[JackDanIce]

Hi,

Can someone please explain what is wrong with my code - I'm trying to open multiple files, pass them into an array and then work on them but the code below doesn't work (receive a Run-time error '9' message)


Public aFile As Variant

Sub MultipleFiles ()

MsgBox ("Please select multiple book files (holding CTRL as you click on flies) before you click open")

aFile = Application.GetOpenFilename(FileFilter:="Excel (*.xl*), *.xl*", _
Title:="Select required book level files", MultiSelect:=True)

Dim i As Integer

For i = LBound(aFile) to UBound(aFile)
Workbooks(aFile(i)).Activate
'Do something, then repeat with next file in array
Next i

End Sub

Thanks,
Jack

 

[Andrew Poulsom]

Instead of:

Workbooks(aFile(i)).Activate

try:

Workbooks.Open aFile(i)

 

[jim may]

For i = LBound(aFile) To UBound(aFile)
Workbooks.Open (aFile(i))
'Do something, then repeat with next file in array
Next i

 

[Norie]

Jack

You aren't opening any workbooks.

Try something like this.

Option Explicit

Sub MultipleFiles()
Dim wbOpen As Workbook
Dim aFile As Variant
Dim I As Long
 
    MsgBox ("Please select multiple book files (holding CTRL as you click on flies) before you click open")
    aFile = Application.GetOpenFilename(FileFilter:="Excel (*.xl*), *.xl*", _
                                        Title:="Select required book level files", MultiSelect:=True)

    For I = LBound(aFile) To UBound(aFile)
        Set wbOpen = Workbooks.Open(aFile(I))
        'Do something, then repeat with next file in array
    Next I
End Sub

 

[JackDanIce]

Thank you all, that seems to work but...

How do I select a specific file from the array after the looping is finished?

I'm trying to use Workbooks(aFile(i)).Activate (for whatever value of i) but this doesn't work either. I suspect I'm trying to access the file in the array incorrectly or using the wrong syntax?

Thanks,
Jack

 

[Andrew Poulsom]

aFile contains the full path of the workbook. The Workbooks collection object expects on the name.

That said, when you open a workbook it becomes the ActiveWorkbook. So why do you need to activate it?

 

[Norie]

Jack

What specific file do you mean?

What are you doing with the files?

 

[JackDanIce]

I think I've solved the problem. I was using a specific IF statement that should only execute on the first file and have got around this using a boolean flag.. just finding more bugs now! Thank you all for your help.

 

[Norie]

Jack

There is no If statement or boolean flag in what you've posted so far.

It really might help if you explained what you are trying to do.

Are you trying to collate data from multiple files in the workbook the code is in?

If you are it's probably a good idea to create a reference to that workbook right at the start of the code.

You shouldn't need/want to activate anything.

 

[JackDanIce]

Sorry, I know there wasn't, I was trying to keep the code simple as the rest of it mostly works fine.

I have a main macro file (with buttons) that any user can use with any drive mapping to the main server. I've set up a main macro file so that any user can use it as the code is local to that file.

Clicking start let's the user open a summary file and an unknown number of input files, all of which are in the same format. The macro cycles through each of the input files, checking they relate to the same working date and then extracting all the information into a temporary spreadsheet in the summary file.

From there the collected data is checked against the previous day's to identify any new rows and then vlookups are used to populate the main summary table, taking into account different categories the data has to be sorted into (so lots of vlookups, relative and absolute cell referencing etc). In between this, the user is given the choice to choose which date the data refers to, to avoid problems with weekends/non-working days.

Does this help anymore?

Ta,
Jack