VBA get file name of open workbook

[EMcK01]

I think this should be straightforward but can't quite get what I am looking for.

I have my vba script in one workbook and want to use it to reference a workbook that I manually open, the name of the second workbook will change so needs to be able to suit this.
I have used the following code to close all open workbooks except where the vba is held.

   Sub CloseAllWorkbooks()

   Dim WkbkName As Object


          For Each WkbkName In Application.Workbooks()
          WkbkName.Save
            If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
          Next
End Sub

Changing the line

WkbkName.Save

to

WkbkName.Name

gets me the file names, however I want to ensure it ignores the file name of where the script is held, how do I update for this?

Thanks,
EMcK

 

[Joe4]

If you kick off the Macro from the workbook holding the VBA code, this should work:

Sub CloseAllWorkbooks()

    Dim McrWkbkName As String
    Dim WkbkName As Object

'   Capture name of macro workbook before looping through open workbooks
    McrWkbkName = ActiveWorkbook.Name
    
    For Each WkbkName In Application.Workbooks()
        WkbkName.Save
        If WkbkName.Name <> McrWkbkName Then WkbkName.Close
    Next
    
End Sub

 

[EMcK01]

Hi, Thanks for the quick reply.

I don't think I make myself clear in my first post. I am looking to change the code to get the open workbook file name of the second file only (that doesn't host the via script) only, I don't want to save or close it.

i won't need the

WkbkName.Save

what do I need to change on the

If WkbkName.Name <> McrWkbkName Then WkbkName.Close

to ensure it only returns the name of the second workbook?

If WkbkName.Name <> McrWkbkName Then WkbkName.Name

gives me an error

 

[Norie]

If all you want is to see the workbook name use a message box.

If WkbkName.Name <> McrWkbkName Then MsgBox WkbkName.Name

If you want to do more with the name and/or the workbook itself we'll need more info.

 

[EMcK01]

Hi,

not wanting the filename to display as a MessageBox. would just like to have a reference for the FileName

This code works, however it loops through both so I don't know if its good luck that it ends up on the file name that doesn't host the vba script or not.

   Dim WkbkName As Object    Dim GetFileName As String
    
          For Each WkbkName In Application.Workbooks()
          GetFileName = WkbkName.Name
          Next

 

[Joe4]

It looks like you have your VBA code to loop through ALL open workbooks. So conceivably, you could have more than 2 workbooks open.
If that is the case, how exactly are we to determine which workbook name you want to capture?

Note that I showed you how to capture the name of the ActiveWorkbook above like this:

'   Capture name of macro workbook before looping through open workbooks
    McrWkbkName = ActiveWorkbook.Name

If you run that RIGHT after you open a workbook by VBA code, it will capture the name of the newly opened workbook (as when you open a workbook, it, by default, becomes the active workbook).

 

[EMcK01]

Apologies if I'm making this more complicated than it needs to be.

i will have an open workbook that will hold the vba script. I will then manually open a second workbook, what I am looking for is to return the second file name.

what I am trying to ensure is the file name returned is not the one holding the vba script. As I noted the last script I posted appears to do this but I don't know if it's good luck or if the loop starts with the file that's hosting it, and therefore will always return the filename of the second file that is open.

 

[Norie]

The workbook with the code can be referred to with ThisWorkbook so if there are only 2 workbooks open you can get a reference to the other workbook, i.e. the one opened manually, with this code.

Dim wbOtherWB As Workbook

    For Each wbOtherWB In Application.Workbooks
        If Not wbOtherWB Is ThisWorkbook Then
            Exit For
        End If
    Next wbOtherWB

    If wbOtherWB Is Nothing Then
        MsgBox "No other workbooks open!", vbExclamation
        Exit Sub
    End If

    ' this next line is just an example/confirmation
    MsgBox "The workbook opened manually is named " & wbOtherWB.Name & "."

    ' you can now do whatever you want with the workbook opened manually using the reference wbOtherWB

Note, if there are more than the 2 workbooks open this code will return the first one that VBA finds that isn't the workbook with the code in it but it will never return the workbook with the code in it - if that makes sense.

 

[EMcK01]

Belated response Norie, but thats ideal. Thanks very much.

 

[Pinaceous]

Good Day,


I'm using the vba code referenced in post #8 and Excel provides that:

Is there a way expressed through vba to prevent this workbook from being opened manually?

Please let me know!

Thank you,
pinaceous