Can't Find Excel-Workbook Even Though Exists

[AngeliqueAPF]

Hi,

I inserted a button in one Access-form, that is bound to a VBA-module intended to transfer the values of the form's controls to an already existing Excel-worksheet. My problem is, that I simply cannot seem to open this Excel-file, even if this should be a rather simple task.

Below, I inserted the relevant (!) parts of the code.

Private Sub Command_Click() 

Dim AppExcel As New Excel.Application
Dim strName As String
Dim strPath As String Dim wbook As Excel.Workbook

strPath = "Y:\...\...\"
strName = Dir("Y:\...\...\*filename*.xlsx")

On Error Resume Next 

Set wbook = AppExcel.Workbooks(strName) 

On Error GoTo Fehler 

If wbook Is Nothing Then
	Set wbook = AppExcel.Workbooks.Open(strPath & strName)
End If 

wbook.Visible = True

(…….) 

Exit Sub 

Fehler: MsgBox Err.Description 

End Sub

The error message tells me, the file couldn't be found, even though, the Dir-Function can perfectly find it, which is why the error message can return the name of the file.

I tried some alternations and the following was already checked:
-Excel-library is referenced in Access
-if I skip the asterisk around the filename it doesn't work either
-if I choose "AppExcel = CreateObject(„ExcelApplication“)" over "New Excel.Application", the error message just tells me, the object wouldn't support the method
-the workbook was also saved in a different path, which didn't work either

Do you know something I could try?

Thanks for your replies!

 

[isladogs]

Hi Angelique and welcome to AWF.

Add the line

Debug.Print strPath & strName

This will show, in the VBE Immediate window, the full path of the file you are trying to open and I think it will be obvious to you why it's failing.

 

[Solo712]

Private Sub Command_Click() 

Dim AppExcel As New Excel.Application
Dim strName As String
Dim strPath As String Dim wbook As Excel.Workbook

[COLOR="Red"]strPath[/COLOR] = "Y:\...\...\"
[COLOR="red"]strName[/COLOR] = Dir("Y:\...\...\*filename*.xlsx")

On Error Resume Next 

Set wbook = AppExcel.Workbooks(strName) 

On Error GoTo Fehler 

If wbook Is Nothing Then
	Set wbook = AppExcel.Workbooks.Open([COLOR="red"]strPath & strName[/COLOR])
End If

Your problem is hilighted in red. You should do simply

Set wbook = AppExcel.Workbooks.Open(strName)

because the variable strName contains strPath.

Best,
Jiri

 

[AngeliqueAPF]

Thanks for both of your answers!

Unfortunately I forgot to add to the OP, that I already tried to open the workbook with only the "strName"-Variable. The above code was just a variation.

Do you have another idea where the problem could be? It seems to be computer-related or directory-related, as I tried a similar version on another PC where it worked (again both Access-versions were referenced with the Excel-library)?

 

[JHB]

Set wbook = AppExcel.Workbooks(strName)

To get the above line to work, you need the reference to the already open Excel application.

Set AppExcel = GetObject(, "Excel.Application")

 

[isladogs]

Even just using strName will not work with the code you showed in post 1 as that too was incorrect

As already mentioned do Debug.Print on that.

If Excel isn't already open, try using CreateObject instead of GetObject.

Also check the Excel reference isn't marked as MISSING

 

[AngeliqueAPF]

Thanks for your further replies!

The GetObject-Method also didn't do the trick for me (as well as CreateObject, which I already used before with a closed Excel-App).

I don't understand why the variable "strName" should be a wrong reference to the file in combination with the path, as it should be only the file-name, as Debug.Print shows (Dir-function)?

I now tried a few other things and it works! (at least as isolated code on this PC and not the one with the actual project, as I don't have access to this PC for the next days).


1) I changed the "strName"-variable to be a compound string (although I don't know, why this would make a difference, as the Dir-function also found the file in the first place without the "&").

strName = Dir("C:\...\...\...\...\" & "*filename*.xlsx")

2) I needed to add "AppExcel" or "wbook" before some Excel-objects further down in the code (like "Row" or "Worksheets("...")), as my code is in Access, obv.


(Also, I added "wbook = Nothing to the beginning of the code, as my code never reached the end and maybe the variable needed to be reset until it worked?)

Kind regards
Angelique

 

[isladogs]

Glad you got it working though I'm not clear exactly what you did

The following may help explain what I was referring to originally ...
Place in a standard module, run it & observe the results in the Immediate window.
I'm just using Notepad as an example that is on all Windows PCs

Sub TestPath()

    Dim strPath1 As String, strPath2, strPath3 As String
    
    strPath1 = "C:\Windows\Notepad.exe"
    strPath2 = Dir("C:\Windows\Notepad.exe")
    strPath3 = Environ("Windir") & "\" & strPath2
    
    Debug.Print "strPath1 = " & strPath1
    Debug.Print "strPath2 = " & strPath2
    Debug.Print "strPath3 = " & strPath3
    
End Sub

My point is that normally I wouldn't use Dir to get a file path
Nor would I put wildcards in the expression used ....

Also, in case you're not familiar with the Environ variable, try running this to gets lots of useful info about your PC

Sub ListEnvironVariables()
    Dim strEnviron As String
    Dim i As Long
    For i = 1 To 255
        strEnviron = Environ(i)
        If LenB(strEnviron) = 0& Then Exit For
        Debug.Print i & " " & strEnviron
    Next
End Sub

 

[JHB]

Is "filename" a placeholder you have inserted here on this site instead of the correct file name you are searching for or is it a variable?
If variable then:

..\...\*" & filename & "*.xlsx"

 

[Rx_]

This link was for a different purpose, but it might be useful to determine if the file can be opened. There is some useable copy / paste vba that might be useful.
https://www.access-programmers.co.uk/forums/showthread.php?t=280097


This also gets into a slightly different error that doesn't trap. Since the code is reported to work on some PC but not one PC, the questions about the possible mapping differences could be a factor.
It reminded me about a problem that needed to be solved when the file path exceeded a limit. The limit is not 255 since Excel has some hidden gems.
https://www.access-programmers.co.uk/forums/showthread.php?t=292913