Home Cannot find filepath that clearly exists.... Disabled macros, and verified directory
Reply: 0

Cannot find filepath that clearly exists.... Disabled macros, and verified directory

user1967 Published in June 19, 2018, 4:32 pm

I have checked similar issues, but the solutions I have seen don't really address this problem. I am trying to temporarily change the file access of a linked Excel sheet for a supervisor on a network drive. After doing some testing, I was able to validate that 2 directories I was testing are recognized. However, when trying to set a workbook object to a particular .xlsx file in those folders, I kept getting an error indicating Excel couldn't find the file (note the exact Excel name was copied and pasted in). Here is a sample of the code:

 Dim wb As Excel.Application
Dim s As String
If Me.Check9.Value = False Then
    s = CurrentPath & "\OurFile.xlsx"
    s = "\\--UserProfile---Data\My%20Documents\Access%20Database%20Work"
End If
If Dir(s, vbDirectory) = "" Then
    MsgBox ("Good")
    Excel.Application.EnableEvents = False
    s = s & "\DummyQueryTested.xlsx"
    '*** Here is where the error is; we disabled macros above ***'
    Set wb = Workbooks.Open(s)
    wb.ChangeFileAccess Mode:=xlReadWrite
    MsgBox ("Bad")
    Set wb = Workbooks.Open(s)
End If
Excel.Application.EnableEvents = True

A few last notes:

  • There are On_Open macros, so that is why I tried using the EnableEvents=False command. The thought was those were preventing the files from opening
  • I can't just move the file to another location because this is a file that is opened by an Access database as a link, and the company needs these files to be in the locations they are currently residing in.
  • Finally, you can see I tested the network as well as local file paths and that is what the "Check9.Value" test was for...to test 2 different filepaths.

Any help would be appreciated. I have seen similar issues, but not exactly like this one. Is there any way to test further if there is some permission, or some setting these files have that might be causing problems?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.336243 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO