Importing multiple Excel workbooks from Access

[u473]

Function TS_Import()
On Error GoTo TS_Import_Err
Dim TS As Integer

' Sample Worbook names : 00498, 40194 etc...

DoCmd.SetWarnings False
For TS = 1 To 99999
DoCmd.TransferSpreadsheet acImport, 8, "Staff", "L:\Time\" &
Format(TS, "00000") & ".xls", True, "Summary!"
Next TS

DoCmd.SetWarnings True
TS_Import_Exit:
Exit Function

TS_Import_Err:
Resume Next
Resume TS_Import_Exit

End Function

'Where did I go wrong ? Thank you for your help. Celeste Edwards.

[Ken Snell]

What is wrong?

--

Ken Snell
<MS ACCESS MVP>

"u473" <u4**@aol.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Function TS_Import()
On Error GoTo TS_Import_Err
Dim TS As Integer

' Sample Worbook names : 00498, 40194 etc...

DoCmd.SetWarnings False
For TS = 1 To 99999
DoCmd.TransferSpreadsheet acImport, 8, "Staff", "L:\Time\" &
Format(TS, "00000") & ".xls", True, "Summary!"
Next TS

DoCmd.SetWarnings True
TS_Import_Exit:
Exit Function

TS_Import_Err:
Resume Next
Resume TS_Import_Exit

End Function

'Where did I go wrong ? Thank you for your help. Celeste Edwards.

[u473]

It does not produce the expected result. No imported workbook.
I suspect my format criteria is the culprit.

Thank you for your help.

C.E.

[Ken Snell]

One problem is that you're using an Integer variable for numbers that are
larger than what an Integer can handle (32,767); you're trying to use it up
to 99,999. Change TS to a Long variable type.

--

Ken Snell
<MS ACCESS MVP>

"u473" <u4**@aol.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

It does not produce the expected result. No imported workbook.
I suspect my format criteria is the culprit.

Thank you for your help.

C.E.