error handling when importing Excel into Access

[Tigger]

I am importing an excel spreadsheet into an Access table using the following code:
Dim FiletoImport As String
Dim dbs As Database
Set dbs = CurrentDb
FiletoImport = "l:\HR Current.xls"
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="(SE)_tbl_HR_Data_Raw", Filename:=FiletoImport, hasfieldnames:=True, spreadsheettype:=5

it works fine but I need error handler for when the file is not yet in the right location i.e. the person using the db has not saved the file in the correct location but still tries to run the import. Furthermore I would like to change the file name to not be proceeded by the letter "l" but by a wilde card character in order for the db to work on other people's pc who might not have named their drive "l".

Thanks in advance...

 

[Guus2005]

You could store a variable in your tables which are user depended. A local table named PARAM containing the fields ID(string*50) and VALUE(string*255) should do the trick.
ID would be "DriveToImport"
VALUE would be the users drive from where to import the spreadsheet from.

note: VALUE is a reserved word. Use it enclosed by []

Wildcards would work on a Unix/Linux machine but not on Windows. It needs an actual existing drive letter.

With this code you can check if the file exists or not.

If len(dir(FiletoImport))=0 then
    msgbox FiletoImport & " not found",vbexclamation,application.name
    exit function 'exit sub
endif

HTH

 

[Tigger]

sorry don't understand your reply. Am new to Access. Do I need to create a table to use the code you copied at the bottom? Is there no easier way to make Access check whether the spreadsheet is there or not?

Have tried the code and now I have this:

Dim FiletoImport As String
Dim dbs As Database
Set dbs = CurrentDb
If Len(Dir(FiletoImport)) = 0 Then
MsgBox FiletoImport & " not found", vbExclamation, Application.Name

End If
FiletoImport = "l:\HR Current.xls"
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="(SE)_tbl_HR_Data_Raw", Filename:=FiletoImport, hasfieldnames:=True, spreadsheettype:=5
Exit Sub

It seems to ignore the if statement and simply says the object cannot be found. Runtime error 3011

You could store a variable in your tables which are user depended. A local table named PARAM containing the fields ID(string*50) and VALUE(string*255) should do the trick.
ID would be "DriveToImport"
VALUE would be the users drive from where to import the spreadsheet from.

note: VALUE is a reserved word. Use it enclosed by []

Wildcards would work on a Unix/Linux machine but not on Windows. It needs an actual existing drive letter.

With this code you can check if the file exists or not.

If len(dir(FiletoImport))=0 then
    msgbox FiletoImport & " not found",vbexclamation,application.name
    exit function 'exit sub
endif

HTH

 

[Guus2005]

The first part describes a way to store user depended data. Which you can use or not.

The code provided shows a quick way to determine whether a file exists or not.

Dim FiletoImport As String
Dim dbs As Database
Set dbs = CurrentDb
FiletoImport = "l:\HR Current.xls"

'[mycode]
If len(dir(FiletoImport))=0 then
    msgbox FiletoImport & " not found",vbexclamation,application.name
    exit function 'exit sub
endif
'[/mycode]

DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="(SE)_tbl_HR_Data_Raw", Filename:=FiletoImport, hasfieldnames:=True, spreadsheettype:=5

 

[Tigger]

Hi

Tried your code and now the whole lot looks like this:

Dim FiletoImport As String
Dim dbs As Database
Set dbs = CurrentDb
FiletoImport = "l:\HR Current.xls"
'[mycode]
If Len(Dir(FiletoImport)) = 0 Then
MsgBox FiletoImport & " not found", vbExclamation, Application.Name

End If
'[/mycode]
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="(SE)_tbl_HR_Data_Raw", Filename:=FiletoImport, hasfieldnames:=True, spreadsheettype:=5
Exit Sub

If I put the exit function statement in then it comes up with the following error: "exit function not allowed in sub or property".
If I leave it out then it comes up with the statement that it can't find the file followed by another error message quoting error 3011 which is also "cannot find the file in question".Help!

 

[Tigger]

Sorted!

now reading like this:
Dim FiletoImport As String
Dim dbs As Database
Set dbs = CurrentDb
FiletoImport = "l:\HR Current.xls"
'[mycode]
If Len(Dir(FiletoImport)) = 0 Then
MsgBox FiletoImport & " not found", vbExclamation, Application.Name
Exit Sub

End If
'[/mycode]
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="(SE)_tbl_HR_Data_Raw", Filename:=FiletoImport, hasfieldnames:=True, spreadsheettype:=5
Exit Sub