Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Get Workbook Name

hi
i am using visual basic and i'm trying to return the name of current workbook in a cell, this is what i have so far.

	VB:
	
 bookname() 
    Sheet8.Select 
    Range("a1").Select 
    Selection.Formula = "=MID(CELL("filename"),FIND("[",CELL("filename"))+1,(FIND("]",CELL("filename"))+1)-FIND("[",CELL("filename"))-2)" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Not really sure what im doing wrong, can anyone help?

TY


Post your answer or comment

comments powered by Disqus
Hello,

I noticed the copy method of worksheets create a new workbook if the before and after parameter are not included. Is there a reliable way to get the name of this new workbook? Reliable as in, not using the ActiveWorkbook function (the user could click a different workbook since its creation, on a slow machine).

Thanks,
Nomel

I am writing vba codes in ArcMap, a GIS application with VBA embedded in it. I have managed to open excel through code from this application. However, I am having problems writing code to get the name of the opened workbook. Any help will be appreciated.

Below is the code I have written that I need to finish

Public Function
BOS_ExcelWorkbookStatus()
       'Query status listbox
        theKey = "PRM_Workbook"
        theText = BOS_StatusListBoxQuery(theKey)
        
        'Set Status
        theStatus = 0

        'Valid Name
        'If theText <> "" Then
    
            'Get BaseName
            theBaseName = avGetBaseName(theText)
        
            'Create an instance of Excel
            Dim XL As Excel.Application
            Set XL = New Excel.Application
    
            'Open a DDE channel to Excel application
            XL.DisplayAlerts = False
            theCNumber = XL.DDEInitiate("Excel", "System") 
            XL.DisplayAlerts = True

            'Get name of opened workbook
            theWBName = "This is where I need to write code to get the name   
                                of opened workbook"

            'Valid workbook
            if theBaseName = theWBName then
                 theStatus = 1
            end if

      end if 
      
     'Close a DDE channel to Excel application
     XL.DDETerminate theCNumber

     'Return Status
     BOS_ExcelWorkbookStatus = theStatus

End Function
Brenda

Hello:

I need some help I have to document many workbooks I have to through them get the name of the sheets,content, SQL staments, and create a table with that information, any idea "CODE" please help.

Robert

I would like to create a routhine from access that get the Name of all
workbook open in my Windows session.

Is it possible to populate a cell with the name of a worksheet based on its position in the workbook? For example, can I get the name of the 5th worksheet in a workbook into a cell, and if the name of that worksheet changes, the reference to it changes, too?

Also, can I pass the name of a worksheet into a cell in that worksheet?

Thanks!

I have 2 worksheets in the same workbook
On worksheet "PIR-DT MTH" there is a cell (i.e. "E3") which contains a string value representing a range of cells, eg. "C4:L32"
This range value changes due to specific triggers which aren't important
In "PIR-DT MTH", there is a named range called "PIR1DB" with the same cell range that is stored in the cell "E3"
On the other worksheet "PIR-DT CAT", there is a pivot table "PIR1DTCAT" which has it's data range defined as the named range "PIR1DB" on the previous worksheet
So, the jist of the idea is that when the content in cell "E3" on "PIR-DT MTH" changes, I update the range that the workbook Name "PIR1DB" refers to
When the named range is updated, the pivot table "PIR1DTCAT" will be refreshed to show data for the changed range
I've toiled over this code over and over and over and all the variables show the correct values (using the VB Debug window)
But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all

The call to the function looks like this:

	VB:
	
[COLOR=DarkRed]Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")[/COLOR] 
 
The actual Function looks Like this (I have placed the actual values In comments at the End of the line so you can follow
what Is happening): 
 
Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _ 
    ObjWkSheetName As String, InfoType As String, _ 
    IRNumber As String, RangeInfoCell As String) 
     
    Dim myexcel As Object 
    Dim myworkbook As Object 
    Dim sourceworksheet As Object 
    Dim objworksheet As Object 
    Dim PivotTableName As String 
    Dim PivotSourceData As String 
    Dim NameRef As String 
     
    Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application
    Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook
    Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet
    Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet
     
    PivotTableName = "PIR" & IRNumber & InfoType ' [COLOR=DarkRed]PIR1DTCAT[/COLOR]
     
    If sourceworksheet.Range(RangeInfoCell).Value = "None" Then ' [COLOR=DarkRed]C4:L33[/COLOR]
         'There is no downtime data, hence the pivot table will not be updated
    Else 
        PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value '
[COLOR=DarkRed]'PIR-DT MTH'!C4:L33[/COLOR]
        NameRef = "PIR" & IRNumber & "DB" ' [COLOR=DarkRed]PIR1DB[/COLOR]
        myworkbook.Names.Add NameRef, PivotSourceData 'This is the line which doesn't work
        objworksheet.PivotTables(PivotTableName).PivotCache.Refresh 
         
        myworkbook.ShowPivotTableFieldList = False 
        Application.CommandBars("PivotTable").Visible = False 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Changing the Named range doesn't work
The source variable (i.e PivotSourceData) would have the correct information in but and after the line is executed and you check the source range for PIR1DB is says something completely different like "='PIR-DT MTH'!BJ9:BS38"
I have no clue where it gets this from
And everytime you run the code, the strange range changes
Even when I hardcode the value (see below) , it still doesn't work
myworkbook.Names.Add "PIR1DB", "='PIR-DT MTH'!C4:L33"

This is just weird, the fact that hardcoding the values doesn't work means something is drastically wrong somwhere
Am I using any predefined VB words?
Is the sky falling?

Hi

Just recently (not sure why it has happened), when I open an excel file from a shortcut I already had placed on my desktop, it opens the file and suffixs the workbook name with a 1 (eg Test.xls would open as Test1.xls).

This means that it is not the actual file open, but a copy (so when I click on save I get the save as box).

Does anyone know why this is happening and more importantly how to fix (please!!!)

Aaron

how do I get the current application workbook name?
Like myworkbook.xls...?
I need to place this into a variable to be used later.
thanks

Apologies for forcing m@cro into the thread title but I thought the title was a little ambiguous without it... delete if you prefer but please don't ban!

Further to this thread: http://www.ozgrid.com/forum/showthread.php?t=62912

I'm trying to develop a macro to perform some actions on a workbook after it's been saved, this using the ontime method, along the lines of:

	VB:
	
) 
     'Don't do it to personal.xls
    If Wb.Name = "PERSONAL.XLS" Then Exit Sub 
    wbspecialpath = wb.fullname ' actually I'm still not sure what to make wbspecialpath, but as an example
    Application.OnTime Now + TimeValue("00:00:01"), "'newsave " & """" & wbspecialpath & "'" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and

	VB:
	
) 
     'do something clever with the saved workbook
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is that the wbspecialpath string, as I pass it to newsave, doesn't necessarily have anything to do with the new name of the saved workbook once it's saved. Basically it's fine if I'm saving an already-saved workbook without changing the name I'm fine, but a new workbook or a "save as" mean I can't get the new workbook name into my newsave macro.

At the moment I'm thinking about using active workbook, but that seems "leaky" to me. Perhaps I'm being overly-cautious on that?

Any input gratefully received, kind regards, Neil Auto Merged Post Until 24 Hrs Passes;

Further to the above, activeworkbook definitely won't work if the save is part of the close routine.

I've successfully saved an array of string to a workbook name. How do I get it back out as an array of strings instead of as a "Variant/String"?

Hey

This is a simply quastion

Im looking for a way to check what a workbook name starts with

What is the code if i want to do that. Is there is examples. What should i google after

I want to check if the first 4 letters in the string is equel to example "JVIT"

Thanks in advance

Hello Everydody,

This is my first post, so I have to tell you I'm very happy I found you.

I have 5 (same time) opened workbooks.

I ask user (via VBA's inputbox) to select an area in worksheet "ws1" of workbook "wbx" (where 2

Hi and Many thanks in advance!!!

AT present I am using a public variant to know which one is the active workbook containing the running macro.

the macro starts when :
HTML Code:
Private Sub Workbook_Open()
Set ActiveWkb = ActiveWorkbook
End Sub
and then declaring the vatiable within a module this way:
HTML Code:


* * *

I would like to use one other system.

since all my macros are attached to a menu, and this menu is attached to a workbook.

the macros within this menu could and actually are prepared, some times, to work in other open workbooks.

but I would like to activate tha workbook containing the manu "the workbook that creates the menu" to be the active workboook, once the macro has finish the tasks.

* * *

is the re a way to know the name of the workbook, containing the running macros?
know a running macro's workbook name
without using public variables!

so if thisWorkbook.name="Thanks"

and this book contains a macro named "Macro1" within a menu.
when I press the menu to run the macro1

get the name of the book=thanks

kinf of:

msgbox application."running containing macro's workbook name"

thanks!!

Hi, I am trying to get data from a workbook called "MasterTable.xls" into a worksheet "Demographics" of workbook "Country Specific Parameters.xls" in VBA. But, VBA does not seem to resolve the Workbook name with spaces. Please guide what should I do? Thanks in advance. Below is the code that I wrote to get this done

With Range("['Country Specific Parameters'.xls]Demographic! K6")
.FormulaR1C1 = "=VLOOKUP(R6C11,[MasterTable.xls]Sheet1! R1C1:R300C10,10,FALSE)"
.Value = .Value
End With

Hi,

is there any way to show the Workbook name in a cell?

i can get it to show using the Footer but i would like it to show in a cell.

ie.

book1.xls

Regards

Hi,

After I opened a file *.XLS I would like to input all sheets name in a listbox.
I thought to get the count of the Sheets available, then to get sheet name (i) and so on...

but its not working...here is my code:

File1 = Application.GetOpenFilename("File Excel (*.xls), *.xls")
Workbooks.Open Filename:=File1

'This is not GOOD....
For I = 1 To Worksheets.Count
ListBoxSheetCompare1.AddItem = Worksheets(I).Name
Next I

--

Any idea howTo ?

Hi Guys.

Is there an easy way to get the Workbook name to be shown into a cell...
Also so when you save it as something else the cell is updated with the new workbook name?

Thanks

How do you make a summary page which would have the bookbook name as the
folder and the excel sheets listed under, so that when you look up your
file you get the folder (workbook name) and then the sheets are under
and you can click on any to open.

--
carole
------------------------------------------------------------------------
carole's Profile: http://www.excelforum.com/member.php...o&userid=34643
View this thread: http://www.excelforum.com/showthread...hreadid=544354

Hi,
I want to dump some date from a excel file to a database.So i am using
VB.NET , Excel 2003 to do so.I want to know how do i get the names of the
sheets present in a workbook and would like to know is there is any way to
identify the column names in a worksheet??????

Regards

Reny

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/04

How do I get the workbook name from a range object? I can easily get the worksheet name, but I can't find how to get the Workbook name. I can say:

to get the worksheet name, but I don't know what the equivalent call is to get the Workbook name.

Thanks

I need to get the name of the active workbook assigned to a variable in the
Macro that I am trying to write. Is this possible?

Thanks,

Hi all:

I have a workbook that has up to 30 sheets, i.e. one sheet for each day
of the month.
For ex.: Nov1, Nov2 and so on.

I'm averaging values in cell AA32 by

=AVERAGE(Nov1:Nov2!AA32)

As the month progresses a new sheet is added, i.e. I copy the last
sheet, say Nov2 and get Nov2(1)
I rename it to Nov3 and now I have to go to cell AA32!Nov3 and edit Nov2
in the average formula
to Nov3

Is there a way to get the name of an active sheet without using VBA?

Tanks in advance,
/mark

Hi
I have the following macro which at the moment specific the workbook name. If the users changes the workbook name they obviously get a debug error which I want to avoid as I want the macro to run regardless.

How can I do this?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Workbooks("Master GOSC Non-MIP Recommendations.xlsm")

If .Names("No_Explanation_Given").RefersToRange.Value > 0 Then
MsgBox "YOU MUST ENTER AN EXPLANATION FOR ANY RECOMMENDATION ABOVE THE GUIDELINES LIMITS"
Cancel = True
Exit Sub
End If

If .Names("Rec_no_rating").RefersToRange.Value > 0 Then
MsgBox "ONE-UP RATING MUST BE ENTERED FOR ALL EMPLOYEES WITH A RECOMMENDATION"
Cancel = True
Exit Sub
End If

If .Names("Contribution_to_missing_country").RefersToRange.Value > 0 Then
MsgBox "YOU MUST ENTER A COUNTRY FOR ALL CONTRIBUTIONS YOU ARE MAKING TO OTHER BU'S"
Cancel = True
Exit Sub
End If

If .Names("Contributions_from_missing_bu").RefersToRange.Value > 0 Then
MsgBox "YOU MUST ENTER THE CONTRIBUTING BU NAME FOR ALL CONTRIBUTIONS BEING MADE TO AN EMPLOYEE IN YOUR BU"
Cancel = True
Exit Sub
End If

If .Names("Rating_Missing").RefersToRange.Value > 0 Then
MsgBox "YOU HAVE NOT ENTERED A PMP ONE-UP RATING FOR ALL EMPLOYEES - PLEASE CLICK OK AND ENTER MISSING RATINGS"
Cancel = False
Exit Sub
End If

End With
End Sub

Thanks

Hello,

I am interested in finding a way to get the name of the workbook that has a Macro within it.

I wish this so that I can make sure the macro does not run (to do some formatting stuff) unless it is in a different excel document, but also want the user to be able to rename the Excel document that the macro is contained in.

Essentially I want to be able to do something where I return the string value of the workbook name of the workbook containing the macro, so I can use that in the code as a variable, instead of hard coding in a workbook name.

Using the .name function won't work (I don't think) because it only checks the workbook where the macro was run not the one that the macro is in (unless I am missing something about it :D).

Any help would be greatly appreciated!

Thanks,
googlebot


No luck finding an answer? You could always try Google.