Asigning Files names to imported tables

[Soma_rich]

Hi,
I am trying to import any spreadsheet that is in a given folder in this case C:\temp\CI.

I can only seem to import 1 at a time how do a loop? and how do I make the table name the same as the XLS file it is importing?

Private Sub Command0_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, StrFileName, "C:\Temp\CI\*.xls", True

End Sub

 

[Fuga]

I use the Dir() function for finding the files and I would think there is a name method for currentdb.tabledefs.

I'm sorry I don´t have time to test it right now, but check out the help for dir() and the tabledefs collection.

Fuga.

 

[Soma_rich]

Thank you Fuga that has put me on the right track DIR() gives me a file name but it defaults to C:\documents and settings\user rather than the loaction I am trying to get it to look in.

Private Sub Command0_Click()

strtablename = Dir("C:\Temp\CI\*.xls")
strfilename = Dir("C:\Temp\CI\*.xls")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strtablename, strfilename, True

Do While Len(strfilename) > 0
Loop

End Sub

any ideas how I get this to work?

 

[Fuga]

Sorry for the delay.

This might have to do with the importing function. I´ve had similar annoying problems.

Try making a manual import of the files first (thus pointing in the right folder). Then run the loop and see if that helps.

You will probably need to check your loop as well. It looks like it doesn´t do anything.

You need to put DIR() inside the loop in order to find all files in the forlder.

Fuga.