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

 

[Norie]

Jack

Thought it might be something along those lines, and what I suggested, ie creating a reference to the workbook the code is in, is probably a good idea.

It would be very easy to do.

Set wbThis = ThisWorkbook ' creates a reference to the workbook the code is in

This reference can then be used in subsequent code whenever you want to refer to this workbook.

 

[JackDanIce]

Already got that, called MainFile. I think its just me and my lack of experience! All seems to work now, will wait for the litmus test when someone else runs it tonight...

 

[JackDanIce]

Hi all,

A continuation of this, in my second module, I declare two variables before the "Sub" line:

Global MainFile As Variant
Global SummaryFile As Variant

(These are two files I switch between which are not stored in the previous array I asked about)

Because I use them between different modules, I wanted to declare them as global variables so the same variable name could be passed between different modules for ease of use.

I can switch to the main file fine. In a spreadsheet I have about 6 modules attached to a button during which I use following bits of code:

Workbooks(MainFile).Activate
Workbooks(SummaryFile).Activate

And both switch to the required workbook fine. However, after the button is pressed by the user and the 6 modules finish running, there is a 2nd button to press (linked to a new set of modules but in the same workbook as the first lot) which calls the SummaryFile but at this point, the macro doesn't recognise this variable.

I suspect once the first group of modules finishes running and completely exited (stopped), when the user presses the next button, I need to redeclare my SummaryFile module?

Annoyingly this summary file is saved daily with a different name otherwise I'd simply put the 2nd button/bunch of macros into the summary file and have the user run it from there.

Any suggestions?

Thanks,
Jack

 

[Norie]

Jack

Why do you have these separate modules and global variables?

 

[JackDanIce]

Because I can then quickly identify what each module is doing and I thought if I wanted to pass variables between them I had to define them as global (as opposed to local) By the way, what's the difference between a global variable and a public one? I know, I do need to get a book.

 

[JackDanIce]

Double posted by mistake, ignore this message by order of this message

 

[Norie]

Jack

There is a way to pass values - parameters.

I don't have any examples to hand, I'm just about to step out into the cold but I'll try and post something later.

In the meantime you might want to look in Help, or a book.

 

[JackDanIce]

Or Google as I've been doing for the past 3 weeks! Thanks Norie, will check that out.