vba code to get workbook name and enter into macro

[ottasight]

Hi
I need vba code to retrieve the workbook name and place it into macros. the workbook name does not change in the macro if the name of the workbook changes. so i need to assign the name to a variable then use the variable name as the workbook name............thanks

 

[SydneyGeek]

I also have another form that takes me to sheets in another workbook

I guess I'm missing a bit of information...
Does that form have the full path to the workbook, or just the workbook name?
If it's not the full path, how do you know the directory?
Also, I was assuming that you wanted to save the open workbook because the first option, opening a workbook, didn't do the trick.

you could adjust the GetName routine at the top of the post. Instead of

    sFile = Application.GetOpenFilename

you could use

    sFile = Me.MyControlname.Text

Once the file is open you will need to go to the dropdown menu to select the sheet. I don't know of a way to avoid that step.

Denis

 

[ottasight]

Hi Denis
sorry for the confusion
i have two workbooks. one has the price data for various things, lumber,windows,nails plumbing etc.. the other workbook is the one that the estimating is done in. i created two forms, which i put in both workbooks. one takes me to sheets in the data book and one for the sheets in the estimating book. now the data book doesn't get renamed, rather it is accessed by estimating workbooks.
---------------------------------------------------------------------
Private Sub Elect_Click()
Windows("MASTERDATABOOK.xls").Activate
Sheets("elect").Select
Range("A1").Select
DATAMENU.Hide
End Sub
---------------------------------------------------------------------
Private Sub Elect_Click()
Windows("MASTER EST BOOK menu dev B .xls").Activate
Sheets("est elect").Select
Range("A1").Select
ESTIMATEMENU.Hide
End Sub
The 1st code is connected to a buttonon a userform, which will take me to the price sheet for electrical items
the 2nd code is setup the same way and takes me to the estimating sheet for electrical items
EstimateMenu and Datamenu are userforms with control buttons to take me to various sheets.

"MASTER EST BOOK menu dev B" is the current name of workbook. I need to change name to individualize it (I.E. "Smith remodel"). However when i change the name the name is not changed in the macro, so the buttons no longer work, because it is looking for the original name. So, I'm looking for a vba code that will lookup the name, assign it to a variable. then use that variable in the code where i now have the original name. So I'm looking for something like this:
1. get present name of workbook say it is "Jones Remodel"
2. assign it to a variable say "newname"
3. use "newname" in this line,
Windows newname.Activate
4. this I think should allow the buttons on the userforms to work no
matter what the workbook name is.
I've looked at the "name" object description and didn't see how to use it
once again thanks for the help<!-- / message -->

 

[ottasight]

Hi Again
Forgot to say. the name is not on the spread sheet. the code will have to get the name from the excel program...........thanks

 

[irvy]

I too would love the answer to this problem. I have a userform that requests 3 files to be open. I need to be able to switch from one workbook to another to retreive data and combine into a new workbook.

I have been unsuccessful in getting the code for the workbook name to transfer out of the userform into the macro. I don't unload the userform, merely hide it.

Please help.