Import all excel files in a particular folder into one master tabl

[Boss]

I wish to import all the files in the folder c:/test/

I prepared a macro but it is very troublesome to set the macro for 50 files.

Please help me with a code which imports data from worksheet "Full recon"
into one master table of MS Access. the format of all the files is same.

Thanks!
Boss

 

[strive4peace]

Hi Boss (bet you get a kick out of this when people respond to you <smile>),

try something like this:

'~~~~~~~~~~~~~~~~~

Sub ImportExcelTestfiles()

'set up Error Handler
On Error GoTo Proc_Err

Dim mFilename As String
Dim mPath As String
Dim booHasFieldnames As Boolean

'set this to false if your spreadsheets do not have column headings
booHasFieldnames = True

mPath = "C:\test\"

mFilename = Dir(mPath & "*.xls")

Do While Len(mFilename) <> 0
DoCmd.TransferSpreadsheet acImport _
, _
, "Full recon" _
, mPath & mFilename _
, booHasFieldnames _
, "Full recon!"
mFilename = Dir
Loop

MsgBox "Done"

Proc_Exit:
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ImportExcelTestfiles"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub

'~~~~~~~~~~~~~~~~~

put your mouse on the TransferSpreadsheet keyword and press F1 to get
help for the TransferSpreadsheet

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Boss]

Hi,

I worked the best.. Thanks a lot...!!

BTW i didn't understood the meaning of

"Hi Boss (bet you get a kick out of this when people respond to you <smile>),
"

Thanks !
Boss

 

[strive4peace]

Hi Boss (ok, what is your name?)

you're welcome

what I meant was that everyone calls you 'Boss' ... they are the
underling and you are the top man <smile>

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Boss]

This was good..



Boss

Hi Boss (ok, what is your name?)

you're welcome

what I meant was that everyone calls you 'Boss' ... they are the
underling and you are the top man <smile>

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*