Importing Multiple files

  • Thread starter Thread starter PJFry
  • Start date Start date
P

PJFry

I have been given the mind-numbing job loading 102 Excel files into Access.
The files are all identical in format. I remember reading about a way to
code Access to start with the first file in a directory and import each file
into a table until the last file is reached.

Can anyone help me with this?

Thanks!

PJ
 
Hi PJ

The Dir() function, when passed a file path with wildcards, will find the
first file in a folder that matches the wildcard spec. Subsequent calls to
Dir() with no arguments will return the next file matching the spec, until
there are no more and a zero-length string is returned.

So you need code like this:

Dim sPath as string, sFile as string
sPath = "C:\My Folder\Excel Files\"
sFile = Dir( sPath & "*.xls" )
Do Until Len(sFle) = 0
' import the file sPath & sFile
sFile = Dir
Loop

I assume you know how to do the import part. Post back if you need more
help with that.
 
Graham,

Code makes perfect sense. Thank you.

I am getting strange error when I run it. I get error 3011 and the dialog
says that it cannot find the file. The strange part is that it does not seem
to be looking in the correct place. It is saying that it cannot find the
file in a different directory than sFile indicates.

Any thoughts on that one?

Here is the code... (importing Excel 2007 file)

Sub ImportShipping()

Dim sPath As String, sFile As String

sPath = "C:\Shipping\"

sFile = Dir(sPath & "*.xlsx")

Do Until Len(sFile) = 0

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sFile, False
sFile = Dir

Loop

End Sub


Thanks!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



Graham Mandeno said:
Hi PJ

The Dir() function, when passed a file path with wildcards, will find the
first file in a folder that matches the wildcard spec. Subsequent calls to
Dir() with no arguments will return the next file matching the spec, until
there are no more and a zero-length string is returned.

So you need code like this:

Dim sPath as string, sFile as string
sPath = "C:\My Folder\Excel Files\"
sFile = Dir( sPath & "*.xls" )
Do Until Len(sFle) = 0
' import the file sPath & sFile
sFile = Dir
Loop

I assume you know how to do the import part. Post back if you need more
help with that.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

PJFry said:
I have been given the mind-numbing job loading 102 Excel files into Access.
The files are all identical in format. I remember reading about a way to
code Access to start with the first file in a directory and import each
file
into a table until the last file is reached.

Can anyone help me with this?

Thanks!

PJ
Click to expand...
Click to expand...
 
sFile only contains the file name, not its path.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sPath & sFile, False


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


PJFry said:
Graham,

Code makes perfect sense. Thank you.

I am getting strange error when I run it. I get error 3011 and the dialog
says that it cannot find the file. The strange part is that it does not
seem
to be looking in the correct place. It is saying that it cannot find the
file in a different directory than sFile indicates.

Any thoughts on that one?

Here is the code... (importing Excel 2007 file)

Sub ImportShipping()

Dim sPath As String, sFile As String

sPath = "C:\Shipping\"

sFile = Dir(sPath & "*.xlsx")

Do Until Len(sFile) = 0

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sFile, False
sFile = Dir

Loop

End Sub


Thanks!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



Graham Mandeno said:
Hi PJ

The Dir() function, when passed a file path with wildcards, will find the
first file in a folder that matches the wildcard spec. Subsequent calls
to
Dir() with no arguments will return the next file matching the spec,
until
there are no more and a zero-length string is returned.

So you need code like this:

Dim sPath as string, sFile as string
sPath = "C:\My Folder\Excel Files\"
sFile = Dir( sPath & "*.xls" )
Do Until Len(sFle) = 0
' import the file sPath & sFile
sFile = Dir
Loop

I assume you know how to do the import part. Post back if you need more
help with that.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

PJFry said:
I have been given the mind-numbing job loading 102 Excel files into
Access.
The files are all identical in format. I remember reading about a way
to
code Access to start with the first file in a directory and import each
file
into a table until the last file is reached.

Can anyone help me with this?

Thanks!

PJ
Click to expand...
Click to expand...
Click to expand...
 
Hi PJ

If you take another look at my reply you will see:
' import the file sPath & sFile
Click to expand...

Dir() returns only the file name, without the path, so you need to
concatenate sPath to get the full file name:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sPath & sFile, False

Sorry I didn't make that clearer.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

PJFry said:
Graham,

Code makes perfect sense. Thank you.

I am getting strange error when I run it. I get error 3011 and the dialog
says that it cannot find the file. The strange part is that it does not
seem
to be looking in the correct place. It is saying that it cannot find the
file in a different directory than sFile indicates.

Any thoughts on that one?

Here is the code... (importing Excel 2007 file)

Sub ImportShipping()

Dim sPath As String, sFile As String

sPath = "C:\Shipping\"

sFile = Dir(sPath & "*.xlsx")

Do Until Len(sFile) = 0

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sFile, False
sFile = Dir

Loop

End Sub


Thanks!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



Graham Mandeno said:
Hi PJ

The Dir() function, when passed a file path with wildcards, will find the
first file in a folder that matches the wildcard spec. Subsequent calls
to
Dir() with no arguments will return the next file matching the spec,
until
there are no more and a zero-length string is returned.

So you need code like this:

Dim sPath as string, sFile as string
sPath = "C:\My Folder\Excel Files\"
sFile = Dir( sPath & "*.xls" )
Do Until Len(sFle) = 0
' import the file sPath & sFile
sFile = Dir
Loop

I assume you know how to do the import part. Post back if you need more
help with that.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

PJFry said:
I have been given the mind-numbing job loading 102 Excel files into
Access.
The files are all identical in format. I remember reading about a way
to
code Access to start with the first file in a directory and import each
file
into a table until the last file is reached.

Can anyone help me with this?

Thanks!

PJ
Click to expand...
Click to expand...
Click to expand...
 
Very good. Thanks!

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



Graham Mandeno said:
Hi PJ

If you take another look at my reply you will see:
' import the file sPath & sFile
Click to expand...

Dir() returns only the file name, without the path, so you need to
concatenate sPath to get the full file name:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sPath & sFile, False

Sorry I didn't make that clearer.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

PJFry said:
Graham,

Code makes perfect sense. Thank you.

I am getting strange error when I run it. I get error 3011 and the dialog
says that it cannot find the file. The strange part is that it does not
seem
to be looking in the correct place. It is saying that it cannot find the
file in a different directory than sFile indicates.

Any thoughts on that one?

Here is the code... (importing Excel 2007 file)

Sub ImportShipping()

Dim sPath As String, sFile As String

sPath = "C:\Shipping\"

sFile = Dir(sPath & "*.xlsx")

Do Until Len(sFile) = 0

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sFile, False
sFile = Dir

Loop

End Sub


Thanks!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



Graham Mandeno said:
Hi PJ

The Dir() function, when passed a file path with wildcards, will find the
first file in a folder that matches the wildcard spec. Subsequent calls
to
Dir() with no arguments will return the next file matching the spec,
until
there are no more and a zero-length string is returned.

So you need code like this:

Dim sPath as string, sFile as string
sPath = "C:\My Folder\Excel Files\"
sFile = Dir( sPath & "*.xls" )
Do Until Len(sFle) = 0
' import the file sPath & sFile
sFile = Dir
Loop

I assume you know how to do the import part. Post back if you need more
help with that.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have been given the mind-numbing job loading 102 Excel files into
Access.
The files are all identical in format. I remember reading about a way
to
code Access to start with the first file in a directory and import each
file
into a table until the last file is reached.

Can anyone help me with this?

Thanks!

PJ
Click to expand...
Click to expand...
Click to expand...
 
Back
Top