Import/Export Multiple Excel Files to Access

[Ken Snell \(MVP\)]

Post the details of the macExcelInput macro.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Beep Beep]

Ken:

Thanks for your patience. You can probably tell this is the first time doing
something like this in Access. I am fairly familiar with doing it in Excel.
Having said that here are the details:

Action: Run Code Comment: Function name = MacExcelInput()
Action Argument: Open Function - Function Name = ExcelImport()
Action Argument: View = Datasheet
Action Argument: Data Mode = Edit
Action Argument: Run Macro = Macro Name - macExcelImport.

Hope this helps.

 

[Ken Snell \(MVP\)]

Your MacExcelInput macro should have just a single action in it:

Action: RunCode
Function Name: ExcelImport()

 

[Beep Beep]

Hey Ken:

I fixed the macro as you suggested and here is the latest error message:

The expression you entered ExcelImport() has a funtion name that Microsoft
Access can't find.

 

[Ken Snell \(MVP\)]

My posted function uses the name ImportExcelMultipleFiles()

Did you keep that name, or did you change it to ExcelImport()

You must use the actual name of the function in the macro's argument, and
the function must be in a regular (not a form) module.

 

[Christopher Simpson]

Ken,
I just wanted to say THANKS! This was an awesome macro and worked like a
charm. Saved me hours of time on this project and will be useful in the
future as this sort of thing comes up from time to time.

Thanks again.

Christopher

 

[Christopher Simpson]

Do you have the trailing backslash for this?
you have:
strPath = "C:\Excel\January 2008 Reports"

shouldn't it be:
strPath = "C:\Excel\January 2008 Reports\"

 

[Ken Snell \(MVP\)]

Do you have the trailing backslash for this?
you have:
strPath = "C:\Excel\January 2008 Reports"

shouldn't it be:
strPath = "C:\Excel\January 2008 Reports\"

Excellent catch, Christopher. I had missed the absence of the trailing slash
in beep beep's code. Thanks.

 

[Ken Snell \(MVP\)]

Ken,
I just wanted to say THANKS! This was an awesome macro and worked like a
charm. Saved me hours of time on this project and will be useful in the
future as this sort of thing comes up from time to time.

You're welcome. It's not actually a macro, though... in ACCESS, a macro is
not VBA code (unlike an EXCEL macro, which is VBA code). You can find more
examples of EXCEL data import/export at the website in my signature. Good
luck with your project.