importing multiple spreadsheets

[Mary10k]

Hello,
I have about 150+ text files which I need to import into Access. I have the following code I used earlier to import spreadsheets. Can anyone modify this or provide direction as to how I can automate importing 150+ spreasheets?

If Len(Trim(Nz(MyTextBox,"")&"")) = 0 Then
msgBox "please give file name"
Else
docmd....etc
End if

 

[AppStaff]

I don't think we have enough information to give you a specific answer. However, we can give you some direction.

First, keep in mind a text file is not a spreadsheet per se.

Docmd.transferspreadsheet would be the import command you'd want to use for a spreadsheet and for text files it would be docmd.transfertext. Please verify these commands in the help file as I didnt look them up.

I might suggest that you find some standard method for naming these files. The easiest would be numerically 1-150. You may need some preceeding text to make this a valid file name. Not sure about this.


Once you do this then you can create a function that imports the spreadsheet and use a for i =1 to 150 statement to loop through and import each file. Sorry but this is very raw and untested.

Create this in a module

Function ImportSSFile(i As Integer)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, i, i
End Function

Assign this to a control or in a module.

Sub ImportSpreadsheets()
Dim i As Integer

For i = 1 To 150

Call ImportSSFile(i)
Next i

End Sub


You will likely have some syntax issues and need to check to make sure that the file exists (Dir function should work). Error checking would be helpful also. HTH

 

[PHV]

Take a look at the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244