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]

Here's one option...

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

 

[Mr. Walnuts]

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?

 

[ottasight]

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

 

[SydneyGeek]

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):

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

Denis

 

[SydneyGeek]

If the workbook is already open, do this:

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

Denis

 

[ottasight]

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 -->

 

[SydneyGeek]

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

 

[SydneyGeek]

Here's a link to the add-in. Hope you find it useful.

Denis

 

[ottasight]

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