Set Workbook Filename without opening file

[A A Ron]

I have a question about setting a workbook filename. I do not want to open the file, as I have around 15 different workbooks that I am setting variables for. With each workbook I later want to attach these to email messages and send them out to different individuals.

Is it true that I will have to open them while I set the filename? Is there a way to set them without opening. I know I can open and close them, but that is a bit of a pain.

Any help on this would be greatly appreciated.


Thanks in advance.

 

[MUKESHY12390]

Hey Ron... would explain in detail what do mean by "setting a workbook filename".

Do you want just fileName and Complete Path??

 

[A A Ron]

Hey Ron... would explain in detail what do mean by "setting a workbook filename".

Do you want just fileName and Complete Path??

sure, I have multiple workbooks that I am setting their file path like this:

Sub SendEmail()


Dim cBA As Collection
Dim rng As Range
Dim cell As Range
Dim FPDall As Workbook
Dim FPD As Workbook
Dim VPcompare As Workbook
Dim LA_CPF As Workbook
Dim CPF As Workbook
Dim LA As Workbook
Dim NA As Workbook
Dim EAME As Workbook
Dim Dist As Workbook
Dim CFS As Workbook
Dim InfoServ As Workbook
Dim AP As Workbook
Dim Ins As Workbook
Dim NA_CMF As Workbook
Dim Legal As Workbook
Dim wb As Workbook
Dim M_Y As Variant
Dim Month As Variant
Dim Year As Variant
Dim FilePath As Variant
Dim filename As Variant


Dim ws As Worksheet
Dim vNum As Variant


Call OptimizeCode_Begin


M_Y = Format(Date - 30, "mmmm yyyy")
Year = Format(Date - 30, "yyyy")
Month = Format(Date - 30, "mm")
FilePath = "A:\Data Sources\MASTER SCORECARDS - LIVE\"
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Super User Report")


    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    
Set FPDall = Workbooks.Open(FilePath & Year & "\" & M_Y & "\" & Month & " " & Year & " Adams - FPD All Metrics" & ".xlsx")

I am trying to set the path for the workbook FPDall here, and it works to set it like this, but it seems like it has to open. I have 14 other workbooks I will need to set, which I will be attempting to add them as attachments to emails to certain people in the company later in the code.

I hope I have answered your questions. I am somewhat newer to VBA (more of a googler than a coder at this point). Thank You.

 

[A A Ron]

Any ideas anyone........?

 

[MUKESHY12390]

still not clear.... I'll try again

what is the reason, why you open "FPdall" ?

Is there any data you would like copy or anything ?

 

[A A Ron]

I think I figured it out.

I was trying to set the variable to be the workbook without opening the said workbook. I am trying to attach the workbook to an email that I will be setting up.

What I did was to just set the entire filepath while attaching the workbook, without setting any variables at all.

Thank You.

 

[MUKESHY12390]

glad you found solution.

 

[wilt823]

I have a situation similar to the one on this thread, but it sounds like his way of fixing it is not a way that will work for me...so here is my issue:

I have 3 template workbooks and I am trying to dim and set a workbook with a variable as the file path & name for each one, but I do NOT want to open the 3 files at the time the variable is set, as in: set template=workbook.open(path file name.xlsx). Instead, I have some if/else statements later in the macro which help determine which of the 3 templates to open and continue the macro. Here's the code I have so far, and after running it stops on the set template line with error msg 9 subscript out of range:

Dim template As Workbook
Dim tempIMB As Workbook
Dim tempORM As Workbook

Set template = Workbooks("C:\Services\Billing\Invoices\MM-YYYY TXX Invoice TEMP.xlsm")

Set tempIMB = Workbooks("C:\Services\Billing\Invoices\MM-YYYY TXX Invoice TEMPIMB.xlsm")

Set tempORM = Workbooks("C:\Services\Billing\Invoices\MM-YYYY TXX Invoice TEMPORM.xlsm")

then , like I explained above, I have some if/else statements after this which determine which template the macro will open to continue running the macro. I have tested that the file name is accurate, and if I use the code: workbook.open(filename), it works perfectly, I just don't want any of these 3 files opened until the macro knows which one is actually needed.

Is there a way to do this?
Any help would be greatly appreciated.

 

[wilt823]

More info which may be helpful:
The macro is in a fourth workbook which contains all of the data used to populate the above templates; so the criteria in the if/else statements to determine which template to open is in the fourth workbook.
Win 10, office 2013

Thank You