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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
416
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,652
Office Version
2010
Platform
Windows
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.
Code:
Public facml As Workbook
An alternative is to pass it by reference.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,249
Messages
5,467,529
Members
406,544
Latest member
semoredhawk

This Week's Hot Topics

Top