[VBA] RE GetOpenFileName, I want to point Excel to a file, and then reference that in other modules

[RockandGrohl]

Hi all,

Doing a new sheet for reporting, I want to report on a campaign. Let's say our campaign is called "70. London"

I use GetOpenFilename to point Excel to this, and then reference that file as 'facml'

So now when I talk about facml in that module, it knows the file name and location, so if I need to open it, I can just write "facml.open" false (for read only)

That's all well and good in that module, but what about in other modules?

Let's say I have another module that needs to open facml and grab some information from it, will I have to do the "get open filename" process again, or can I just point it to my existing facml that I already defined in the other module?

A potential workaround I'm seeing is to write the path and filename string for facml in a cell that's out of sight, and then say facml = cell, to then open that filename.

Thanks.

 

[yky]

You can declare facml outside any function and sub and with the PUBLIC keyword. That makes facml a global variable, which can be used by all modules and, thus, could be hard to track in a big project.

Public facml As Workbook

An alternative is to pass it by reference.