vba code to get workbook name and enter into macro

ottasight

New Member
Joined
Feb 15, 2009
Messages
46
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Here's one option...

Code:
Sub GetName()
    Dim wbSource As Workbook
    Dim sFile As String
    
    sFile = Application.GetOpenFilename
    If sFile <> "" Then
        Workbooks.Open sFile
        Set wbSource = ActiveWorkbook
    End If
End Sub

Assumes that the user will browse to a file, open it, and that file will have the variable assigned

Denis
 
Upvote 0

Mr. Walnuts

Board Regular
Joined
Aug 8, 2005
Messages
176
Don't mean to bother; you said it assumes the user opens the workbook. can a macro run on a workbook that ISN'T opened?
 
Upvote 0

ottasight

New Member
Joined
Feb 15, 2009
Messages
46
Hi
Not sure how to use your solution. here's what i have

Private Sub Elect_Click()
Windows("MASTER EST BOOK menu dev B .xls").Activate
Sheets("est elect").Select
Range("A1").Select
ESTIMATEMENU.Hide
End Sub
EstimateMenu is a form with control buttons to take me to various sheets.
I also have another form that takes me to sheets in another workbook
"MASTER EST BOOK menu dev B" is the current name of workbook. I need to change name to individualize it (I.E. "Smith remodel"). When i change the name the name is not changed in the macro, so the buttons no longer work. I'm also looking for a way to put sheet names on a drop down list on the ribbon and quick toolbar
 
Upvote 0

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Don't mean to bother; you said it assumes the user opens the workbook. can a macro run on a workbook that ISN'T opened?

Yes, but it depends what you want to do. If you want to copy that file to another location or perhaps delete it, no need to open. If you want to manipulate data it's much faster if you open the file.

So, you could do something like this (which won't open the file):
Code:
Sub GetName()
    Dim wbSource As Workbook
    Dim sFile As String
    
    sFile = Application.GetOpenFilename
    If sFile <> "" Then
        Set wbSource = sFile
    End If
End Sub

Denis
 
Upvote 0

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If the workbook is already open, do this:

Code:
'Save the file as whatever
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Range("a1").Value & ".xls"
Set wbSource = Activeworkbook
'make the downstream code refer to wbSource

Denis
 
Upvote 0

ottasight

New Member
Joined
Feb 15, 2009
Messages
46
Hi
Didn't know if you seen my reply. Here's what i have.


Private Sub Elect_Click()
Windows("MASTER EST BOOK menu dev B .xls").Activate
Sheets("est elect").Select
Range("A1").Select
ESTIMATEMENU.Hide
End Sub
EstimateMenu is a form with control buttons to take me to various sheets.
I also have another form that takes me to sheets in another workbook
"MASTER EST BOOK menu dev B" is the current name of workbook. I need to change name to individualize it (I.E. "Smith remodel"). When i change the name the name is not changed in the macro, so the buttons no longer work. I'm also looking for a way to put sheet names on a drop down list on the ribbon and quick toolbar.
thanks for the help
<!-- / message -->
 
Upvote 0

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
My last post assumed that you had the workbook name entered in range("A1") on a worksheet. I just provided the required snippet to give the general idea.

I have created an add-in that refreshes the sheet list and lets you navigate there, using a drop-down list on the ribbon. I'll post the link when I get home.

Denis
 
Upvote 0

ottasight

New Member
Joined
Feb 15, 2009
Messages
46
Thanks Denis
The Ribbon add in works great. I would still like a solution to the example i posted, because it would save me a lot of mouse movement and clicking. It would allow me to go to sheets in an unactive workbook without going to the ribbon. ............thanks for the help
 
Upvote 0

Forum statistics

Threads
1,191,671
Messages
5,987,958
Members
440,122
Latest member
branhill

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top