TransferSpreadsheet Question? (1 Viewer)

Hello All,
I am using Access 2000. I am using the feature TransferSpreadsheet and if the path of the spreadsheet is
present then the database works fine. If the spreadsheet
path is not the same I get the VBA Run-time error as such:

Run-time error '3011'
the Microsoft Jet database engine could not find the object
'C:\TBLCLINETBLANK.XLS'. Make sure the object exists and that
you spell its name and the path name correctly.

How can I fix this problem with a message for the user to
ensure that the spreadsheet path is present at the designated
location. Thanks in advance for assistance.

You need to use error trapping to catch the error before it triggers the automated response. I am not sure if this is a form_error or sub procedural error, therefore you'll need to experiment.

Private Sub btnExample_Click()
On Error GoTo Err_btnExample_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 0, 2, acMenuVer70

Exit_btnExample_Click:
Exit Sub

Err_btnExample_Click:
If Err.Number = 3011 then
Msgbox "Your custom error message",0,"Arggghhhhh!, Are you dumb or something?"
Else
Msgbox Err.Number & vbCrLf & Err.Description
End if
Resume Exit_btnExample_Click
End Sub

It might be easier to use the Dir() function first to establish the file is there before you start. Read VBA help for DIR()

If Dir("X:\Data\Files\Test.doc") = "" Then
Msgbox "File does not exist"
Else
Msgbox "File exists"
End If

You can check for the existence of a file using a function like this:

Function FileExists(strFile) As Boolean
  &nbsp FileExists = Dir(strFile) <> ""
End Function

Call it like this: FileExists("C:\filename.xls"). It will return true if the file exists, false otherwise.

If you're looking to verify the existence of a path use a function like this:

Function PathExists(strPath) as Boolean
 &nbsp On Error Resume Next
 &nbsp PathExists=(GetAttr("F:\") and vbDirectory) = vbDirectory
End Function

That function will return TRUE if the Path exists

Oops, that last code snippet should read:

Function PathExists(strPath) as Boolean
 &nbsp On Error Resume Next
 &nbsp PathExists=(GetAttr(strPath) and vbDirectory) = vbDirectory
End Function

TransferSpreadsheet questions?

Hello Fornatian and dcx693,
Thanks for your quick responses. I have tried it and it works great. By the way, have you encounter the same
problem that I'm having when the user enters data into the spreadsheet and either skips any number of rows or delete any number of rows and saves the spreadsheet. When I use the transfer spreadsheet method I get the spreadsheet however,
when I import the data into the database table I get the empty rows the user either skipped or deleted during user entry. Is there a fix for it? Thanks for your assistance and response in advance.