Spreadsheet data import

[LJG]

Hi Guys,

Have in import routine that calls a number of sheets, how can I avoid an
error message if the file does not exist?


This is my code:

DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User1.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User2.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User3.xls", True, "" etc

Thanks
Les

 

[Allen Browne]

Use Dir() to see if the file does exist, perhaps?

 

[Nikos Yannacopoulos]

Try something like:

Dim strSS As String
For i = 1 To 12 'or whatever the number
strSS = "C:\database\dataimports\Prospects_User" _
& i & .xls"
If Dir(strSS) <> "" Then
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects", _
strSS, True, ""
End If
Next


HTH,
Nikos

 

[Guest]

Use the error handler, incase you get a certain error number make it continue
to the next line

Function FunctionNAme()
On error goto FunctionNAme_Error

DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User1.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User2.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User3.xls", True, "" etc

FunctionNAme_Exit:
Exit function
FunctionNAme_Error:
If Err = CheckTheErrorNumber then
resume next
else
msgbox error
endif
resume FunctionNAme_Exit

 

[LJG]

Hi Offer,

Thanks for that, not too good at VBA yet...how would I enter the handler,
this is what I have tried but it does not work?

Function FunctionNAme()
On Error GoTo FunctionNAme_Error
Private Sub Command95_Click()

If MsgBox("Have you backup your database.", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
If MsgBox("New Data has been, " & _
"now about to update, do you need to " & _
"backup your data?.", vbYesNo) = vbNo Then


DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User1.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User2.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User3.xls", True, ""

FunctionNAme_Exit:
Exit Sub
FunctionNAme_Error:
If Err = CheckTheErrorNumber Then
Resume Next
Else
MsgBox Error
End If
Resume FunctionNAme_Exit

Call MsgBox("All data is now imported.", vbInformation Or vbDefaultButton1,
"MAM's Data Import Routine")
End If
End If
End Sub

Thanks

Les

 

[Guest]

Try this
====================================

Private Sub Command95_Click()
On Error GoTo Command95_Error

If MsgBox("Have you backup your database.", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
If MsgBox("New Data has been, " & _
"now about to update, do you need to " & _
"backup your data?.", vbYesNo) = vbNo Then


DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User1.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User2.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects",
"C:\database\dataimports\Prospects_User3.xls", True, ""

Call MsgBox("All data is now imported.", vbInformation Or vbDefaultButton1,
"MAM's Data Import Routine")
End If
End If

Command95_Exit:
Exit Sub
Command95_Error:
msgbox Err
If Err = CheckTheErrorNumber Then
Resume Next
Else
MsgBox Error
End If
Resume Command95_Exit
End Sub
====================================
In the "Command95_Error" section I put a msgbox that will display the error
number, then replace the "CheckTheErrorNumber " with the number displayed.
Now remove that msgbox, It just to display the error number.

 

[LJG]

Hi Nikos

Thanks for that, works perfectly