Set Spreadsheet filename as Variable

[alchohaz]

Hello All

i am trying to set the open workbook name (Full Path) as a varible but seem to be coming up against walls every time i search foe the info. I don't want to open any dialogue boxes as in "Application.GetOpenFilename" or "Application.GetSaveAsFilename"

Am i confident that this should be really easy, but I am getting really frustrated with it!!!

Dim FilNam Long
FilNam = ActiveWorkbook.FullName

REASON:

I have a larger macro that is associted with a workbook, and the workbook filename is referenced in the VBA Code, so when i change the workbook filename the code fails as the old filename is still there... so i want the filename to be dynamic. - i am very much a novice on VBA, so go easy please

Thanks in advance

 

[combo]

ThisWorkbook is a reference (as workbook) to the workbook that executes the code. Is it what you need?

combo

 

[alchohaz]

Thanks Combo, i will try that and see how i get on, but i would guess that if working between 2 workbooks, that would only apply to the one active at that instant. Can i use the snapshot to set a variable?

ie, can i set ThisWorkbook as a variable - if so would you mind showing me the syntax.

thanks again
Al

 

[combo]

ThisWorkbook is a reserved object name, no need to set it. If you use it in a code in workbook A:

MsgBox ThisWorkbook.FullName

you will get path&name of workbook A, whatever workbook is active.

The solution depends on the situation, for instance:
1)
A is a master workbook (with code), you can ask user (Application.GetOpenFilename) for a workbook B and open it (Set wb=Workbooks.Open(Filename), wb is a variable that keeps reference to workbook B,
2)
A is a master workbook (with code), workbook B is open, you can ask user to point a workbook B (a list of Application.Workbooks, excluding ThisWorkbook), and assign a variable that keeps reference to workbook B.

combo

 

[alchohaz]

Thanks for your Help Combo

What the correct syntax for applying part 2 of your 2nd post?

Also is there NO WAY to set a Varaiable as the current open file name without the "GetOpenFilename" or "GetSaveAsFilename" as per my original post?

Thanks All

 

[combo]

What do you mean by 'current'?
If the situation is that there is a master workbook with code, user opens a set of workbooks and you need to ask him to point to one of them, you can build ah userform with combobox, the code to fill it in:

For Each wb In Application.Workbooks
    If Not wb Is ThisWorkbook Then ComboBox1.AddItem wb.Name
Next wb

You may need to test the 'Visible' property if you have hidden workbooks.
After user's selection you can use the combobox Value to set FNam (with combobox test, if Value="" then no selection, alternatively ListIndex=-1). BTW, it should be declared as String.

combo

 

[N1GHTEYES]

alchohaz, I'm not convinced this is really what you want, but in answer to your question, the code in your original post had an error which would prevent you from getting the workbook name.

You were trying to assign the fullname property to a Long. Try assigning it to a variable dimmed as a string instead.

Does this help?

Tony