name this tune, vba code problem

[ottasight]

Hi
i need a vba code that inserts the already opened workbook name in a variable. i.e. if workbook named "data" is opened. i need the code to enter the name "data" into a variable when it is opened. The workbook name will change from time to time, so i can not put data = name = variable. i need the code to read what workbook name i just opened, and put it into the variable because the name may change from data to tuesday, to apples, to whatever ad infinitum. thanks for all the help

 

[GTO]

Greetings,

It is not clear, leastwise to me, as whether you are just wanting to get ThisWorkbook's name (ie - the name of the workbook that the code is in), or whether you are trying to get the name of a second or third workbook opened, whilst your coded workbook is open.

If just checking ThisWorkbook's name, in ThisWorkbook Module:

Option Explicit
Public strMyName As String
Private Sub Workbook_Open()
    strMyName = ThisWorkbook.Name
End Sub

If checking for other opening workboooks, you'll need a class mod.

Hope this helps,

Mark

 

[SydneyGeek]

If you are opening a file and capturing its name, check out
Application.GetOpenFileName.

Denis

 

[Legacy 68668]

Hi
i need a vba code that inserts the already opened workbook name in a variable. i.e. if workbook named "data" is opened. i need the code to enter the name "data" into a variable when it is opened. The workbook name will change from time to time, so i can not put data = name = variable. i need the code to read what workbook name i just opened, and put it into the variable because the name may change from data to tuesday, to apples, to whatever ad infinitum. thanks for all the help

I guess you are opening the workbook programatically...

How is it possible to open without knowing the file name ?

Or if you are opening it manually then
try

Dim myName As String
myName = Workbooks(Workbooks.Count).Name

 

[ottasight]

Thanks GTO
your solution for the opened workbook worked fine. but i still have a problem. i opened workbook1 after renaming it. the vba code worked fine the name was changed in all the vba code. now i need to be able to access the code in workbook1 when i open and am working in workbook2. is there a way. and i'm a fairly new beginner in vba code, so what is class mod and please go into more detail on accessing code from an inactive workbook1 when working in active workbook2.......thanks again for the help.....and thanks to SydneyGeek and Seiya

 

[GTO]

...your solution for the opened workbook worked fine. but i still have a problem. i opened workbook1 after renaming it. the vba code worked fine the name was changed in all the vba code....

You are most welcome and I think I understand that part...

...now i need to be able to access the code in workbook1 when i open and am working in workbook2. is there a way...

...please go into more detail on accessing code from an inactive workbook1 when working in active workbook2.......

This I am unclear on. Lets just refer to the workbook that you are writing the code in as WB1 or better yet, ThisWorkbook. Other workbooks can be called WB2, WB3 and so on...

Upon manually (that is, the user is opening) ThisWorkbook, we have code that grabbed its name. Now - is the code in ThisWorkbook programatically opening WB2, or did you open WB2 manually?

I think we want to be clear on that first part before trying to address the latter.

If you have a sub/function that stands on its own (not a bunch of mystery stuff referred to), that you have partially written, maybe include it as well, as this might shed some light.

Hope to help,

Mark

 

[ottasight]

Hi Mark
this is what i have
i manually open two workbooks; "master est book", "masterdatabook"
master est book does all the estimating
masterdatabook has all the pricing
this is done so that one data book serves many estimating books
-------------------------------------------------------------------
this belongs to a vba module that is in the "master est book" workbook.
the buttons are on worksheets in the two workbooks
the buttons bring up a user form with buttons that take me to worksheets in the active workbook or moves me to a worksheet in the unactive workbook

Sub EstimateMenu_Click()
EstimateMenu.Show
End Sub
Sub DataMenu_Click()
DATAMENU.Show
End Sub
-----------------------------------------------------------------------
I'll call the below codes 1 and 2.
code 1 is one of many on a user form called "EstimateMenu".
code 2 is one of many on a user form called "DataMenu".
Both user forms reside in the "Master Est Book" workbook.

Private Sub Drywall_Click()
Windows("MASTER EST BOOK menu dev B .xls").Activate
Sheets("est drywall").Select
Range("A1").Select
EstimateMenu.Hide
End Sub

Private Sub Drywall_Click()
Windows("MASTERDATABOOK.xls").Activate
Sheets("drywall").Select
Range("A1").Select
DATAMENU.Hide
End Sub
-------------------------------------------------------------------
i have 30-40 worksheets per book. Using buttons to get around is better than scrolling the workbooks at the bottom of excel. Both codes reside in the "master est book" workbook. Everything works fine if the workbook names don't change. However, i need to individualize the workbooks. I.E. need to change"Master Est Book" to "Smith Remodel" to keep jobs separate. Your solution worked fine. I could move around the renamed "master est book" with no problem. When i swapped to the "masterdatabook". I could not move around it nor swap back to the
renamed "master est book'. I also tried copying the code to the "masterdatabook" with no success. It appears that "masterdatabook" can not reaD the code in the renamed "master est book". Is there a way for it to do that. Once again thanks for the much needed help.....Lou

P.S. I think i can write the code to open a second workbook, but just in case i can't could you write it ........thanks

 

[ottasight]

help help...........thanks

 

[SydneyGeek]

As I mentioned earlier, you can use Application.GetOpenFilename. Something like...

Sub OpenFile()
    Dim strWb As String
    Dim wbTarget As Workbook
    Dim wbMaster As Workbook
    
    Set wbMaster = ThisWorkbook
    strWb = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*")
    Workbooks.Open strWb
    Set wbTarget = ActiveWorkbook
    '... then continue with the rest of your code
End Sub

To refer to the workbook you opened, use wbTarget. To return to the master, refer to wbMaster.

Denis