TransferSpreadsheet ERROR

[almaroc]

I have a command button that is supposed to automatically upload all the excel files to a table in Access 2007. but i get this error " could not find the Object 'c:\todays\*.xl s'". the directory is correct so i don't know what hte problem is. here is my code

Private Sub Upload_Click()
On Error GoTo Err_Upload_Click
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Usage", "C:\Todays\*.xls"
Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command0_Click:
Exit Sub

Err_Upload_Click:
MsgBox Err.Description

End Sub

[TheSmileyCoder]

I haven't tested it, but Im fairly sure that you need to specify the ACTUAL file name, not simply a joker (the asterisk).

[almaroc]

What i am trying to do is load all the files in the folder at once not one at a time. all the files are of the same type. Excel files.

[TheSmileyCoder]

The TransferSpreads heet is not meant for that. Then you will have to loop through the files in the folder, and import them 1 at a time, with the TransferSpreads heet function.

[almaroc]

is there a function that counts the number of files in a folder

[NeoPa]

You probably want to explore what you can do with the Dir() function. Be careful with it though, as it's a bit complicated and easy to get wrong.

[almaroc]

I tried the Dir() but i still get the error. what i don't understand is that somehow it's omitting the subfolder. the error says COULD NOT FIND THE OBJECT 'Z:\Mach.xls' even though the path is actually 'Z:\Todays\'
here is my code

Private Sub Upload_Click()
Dim MyFile
MyFile = Dir("Z:\Todays\*.xls")
Do While MyFile <> ""
On Error GoTo Err_Upload_Click
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Usage", MyFile
Screen.PreviousControl.SetFocus
'DoCmd.FindNext
MyFile = Dir
Loop

Exit_Command0_Click:
Exit Sub

Err_Upload_Click:
MsgBox Err.Description

End Sub

[NeoPa]

You're not using the returned value correctly. I did rather assume you would at least do a little bit of checking on Dir() before getting confused. It's not like I failed to point out it isn't that straightforward .

The value returned from Dir() is never a fully-qualified path string, but always just the file name and extension of the file or folder found. Your line #6 should pass a value of :

"Z:\Todays\" & MyFile

PS. Having said that, your code otherwise handles the situation well, so fix that and all should work perfectly.

[almaroc]

thanks. that took care of that however i get this error FIELD F1 DOES NOT EXIST IN DESTINATION TABLE. I made sure the field names are the same so i so not know what's causing this. any ideas?

[NeoPa]

Almaroc:
any ideas?

Sure. Get all the relevant details together and post a separate question on this topic. It certainly can't be dealt with in this thread.

I suspect though, that once you've done the work you need to do for the question not to be deleted, then you will probably already have found your answer. If not though, I'm sure we'll be happy to help you with it.