Importing Multiple files

[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

 

[Graham Mandeno]

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.

 

[PJFry]

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.

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

 

[Douglas J. Steele]

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)

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.

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

 

[Graham Mandeno]

Hi PJ

If you take another look at my reply you will see:

' import the file sPath & sFile

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

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.

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

 

[PJFry]

Very good. Thanks!

--
Regards,

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

Hi PJ

If you take another look at my reply you will see:

' import the file sPath & sFile

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

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.

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

 

[Ken Snell [MVP]]

For future reference, see this article:

Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
(VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpFolderFiles