VBA Excel Cannot open external workbook

Question

I found a nice, although incomplete, solution here: Excel VBA open workbook with part of its name

and formulated the code based on my example

Option Explicit

Sub NewWorksheet()

Dim sFound As String, fPath As String
Dim WB1 As Workbook

fPath = ThisWorkbook.Path
sFound = Dir(fPath & "Advanced Risk Management Report*.xlsx")
If sFound <> "" Then
Set WB1 = Workbooks.Open(fPath & Found)
End If


End Sub

Unfortunately, the code doesn't work. There is no error either. What could be missing here?

Does it help: sFound = Dir(fPath & "\Advanced Risk Management Report*.xlsx"). Path and filename must be separated. Also when Open — Black cat
– Black cat, Commented Jan 20 at 11:29
The problem is that ThisWorkbook.Path returns an empty string (at least on my copy). I changed the Dir match to "*.xlsx" which meant that it did find some .xlsx files on my machine but since Workbooks.Open requires a fully qualified long filename and because fPath is empty it then fails Runtime Error '1004' . — Martin Brown
– Martin Brown, Commented Jan 20 at 11:48
ThisWorkbook.Path returns an empty string if the workbook is not yet saved. — FunThomas
– FunThomas, Commented Jan 20 at 11:49
check this line too Set WB1 = Workbooks.Open(fPath & Found) — Rosetta
– Rosetta, Commented Jan 20 at 11:50
I'm guessing you haven't got Option Explicit at the top of your module - you search for sFound and try and open Found. Without Option Explicit the code will just happily create a variant variable called Found and assign it an empty string. You also don't put the path separator in the Dir call so fPath is .....\My Documents rather than `...\My Documents\`. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jan 20 at 14:05

Darren Bartrup-Cook · Accepted Answer · 2025-01-20 14:17:21Z

1

First select Tools > Options and tick Require Variable Declaration.
In all new modules going forward you'll get Option Explicit at the top of the module.

Now just update your code as below:

Option Explicit

Sub NewWorksheet()

    Dim sFound As String, fPath As String
    Dim WB1 As Workbook
    
    fPath = ThisWorkbook.Path & "\" 'Added & "\"
    sFound = Dir(fPath & "Advanced Risk Management Report*.xlsx")
    If sFound <> "" Then
        Set WB1 = Workbooks.Open(fPath & sFound) 'Updated Found to sFound.
    End If

End Sub

answered Jan 20 at 14:17

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel Cannot open external workbook

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related