CODEWITHSUNDEEP
vba
T.Els
T.Els

Reputation: 63

Get name and path of opened Workbook using VBA

I just wanted to ask how I get the name and path of the actual opened Workbook with VBA?

I tried it as captured in the snippet below but keep getting a compiler error.

My document-type is .docm.

What am I doing wrong?

My code snippet:

Sub TestFileOpened()
Dim strPath As String, strPathAndName As String

    strPath = Application.ThisWorkbook.Path
    strPathAndName = strPath & Application.ThisWorkbook.Name
    MsgBox strPathAndName 

    ' Test to see if the file is open.
    If IsFileOpen(strPathAndName) Then ....

Upvotes: 2

Views: 6762

Answers (2)

Preston
Preston

Reputation: 8177

Assuming that you want the location of the open word document, try the below:

Sub TestFileOpened()
Dim strPath As String, strPathAndName As String

    strPath = ThisDocument.Path
    strPathAndName = strPath & "\" & ThisDocument.Name
    MsgBox strPathAndName 

    ' Test to see if the file is open.
    If IsFileOpen(strPathAndName) Then ....

(Note not to forget to add the backslash between file path and name)

Upvotes: 1

R3uK
R3uK

Reputation: 14537

Apparently, you are trying to use some Excel code from Word, so first you need to get a hold on Excel App :

Dim oExcel as Excel.Application
Dim wB as Excel.WorkBook
Dim strPath As String
Dim strPathAndName As String


On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    Set oExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0


Set wB = oExcel.Workbooks(1)
strPath = wB.Path
strPathAndName = strPath & "\" & wB.Name
MsgBox strPathAndName 

' Test to see if the file is open.
If IsFileOpen(strPathAndName) Then ....

Upvotes: 2

Related Questions