Run Time Error '76' - Path Not Found

[kingbob]

Hello All,

I have picked up a new job at my company which requires maintaining some.xls files. Most of these files were built by my predecessor who was extremely experiencedin Excel.

My knowledge is not as strong but I am learning as fast as I can.

I am getting this error message in a spreadsheet and was wondering if anyonecould confirm if the syntax is correct, or how I could confirm the path of whatthe xls is trying to open.

Also, can anyone recommend any good online tutorials for the purpose of learning VBA and Excel

Bob.

[patel]

You only can know if this path exists, remove ThsWorkbook.Path and put the right path

[Kenneth Hobs]

Welcome to the forum!

Before opening a file, be sure that it exists.
[vba]If Dir("drive:\path\filename.ext")="" then Msgbox "File Does Not Exist."[/vba]
If you debug by F8 one line at a time, you can see that ThisWorkbook.Name also includes the file extension of ThisWorbook. e.g. x:\Excel\Access Logs\text.xlsm.log

I doubt that that is an existing log file.

Here is an FSO method to get the basename of a file.

Sub ken()
  Dim s As String
  s = ThisWorkbook.Path & "\" & GetBaseName(ThisWorkbook.Name) & ".xlsm"
  MsgBox s & vbLf & "File above, exists?  " & (Dir(s) <> "")
End Sub

Function GetBaseName(filespec As String)
  Dim fso As Object, s As String
  Set fso = CreateObject("Scripting.FileSystemObject")
  s = fso.GetBaseName(filespec)
  Set fso = Nothing
  GetBaseName = s
End Function

For VBA and Excel, there are many sites that can help though they are two different subject areas. Lots of MVP's offer help. http://mvps.org/links.html#Excel

e.g.
http://www.contextures.com/tiptech.html