automatically import multiple named-ranges from Excel to Access

[icystorm]

I have named-ranges in multiple Excel 2003 workbooks located in a
single directory. The named-ranges that I want to import from each
workgroup are identical. For example, cells $A$4:$R$199 are named
'HOUR12' in each workbook. I would like to import all of the named-
ranges, 'HOUR12', from each workbook into an existing Access 2003
table. The headers and formatting in each named-range are identical.

Is there existing code to accomplish this procedure automatically?

Thanks kindly.

Cheers,
Joseph

 

[Alex Dybenko]

Hi,
there is no automatic way, you have to write some code. You can get files in
a folder using Dir() function and import excel data using
docmd.transferspreadsheet

Also look at this article regarding range usage:
http://accessblog.net/2006/07/export-to-excel-range.html

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Ken Snell \(MVP\)]

And see examples of importing from EXCEL at my website:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm

 

[icystorm]

And see examples of importing from EXCEL at my website:http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm
....and...

Thanks much, Ken and Alex. I will examine both.

Cheers,
Joseph

 

[icystorm]

And see examples of importing from EXCEL at my website:http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm

Ken, as a followup, I employed the use of a couple of your modules and
they both worked great. The one I think I will settle on is the one
that imports the first worksheet of each workbook in single folder. I
simply created an extra "staging worksheet" in Excel to replicate all
of the data I want to export to Access and used your module. Perfect.

The only problem I encountered involved dates/times. For some reason,
all of the dates/times in Excel that appear as "12/05/2008 12:00 AM"
are transfered to Access as "12/05/2008", sans the time. All of the
other times in the remaining entry (e.g., NOT 12:00 AM") transfer with
the time intact. I even tried changing the format in Access, but the
time is simply not there to format for the cases of 12:00 AM.

Of course, this issue was not caused by your module. It happens during
a regular import too.

Any thoughts?

Cheers,
Joseph

 

[icystorm]

The only problem I encountered involved dates/times. For some reason,
all of the dates/times in Excel that appear as "12/05/2008 12:00 AM"
are transfered to Access as "12/05/2008", sans the time. All of the
other times in the remaining entry (e.g., NOT 12:00 AM") transfer with
the time intact. I even tried changing the format in Access, but the
time is simply not there to format for the cases of 12:00 AM.

Disregard on the time issue. I was using a faulty format line. All is
well now.

Cheers,
Joseph

 

[Ken Snell \(MVP\)]

ACCESS usually leaves off the time if it's the Midnight "12:00 AM" when it
displays the data. That is because the time is the fractional part of a
double-precision number that is stored as the date/time value in ACCESS --
e.g., 36578.00000000000. So the time is still in the data, just not
displayed. If you want to force the display of the time no matter what,
change the Format property of the table's field to "mm/dd/yyyy hh:nn AMPM".

--

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

And see examples of importing from EXCEL at my
website:http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm

Ken, as a followup, I employed the use of a couple of your modules and
they both worked great. The one I think I will settle on is the one
that imports the first worksheet of each workbook in single folder. I
simply created an extra "staging worksheet" in Excel to replicate all
of the data I want to export to Access and used your module. Perfect.

The only problem I encountered involved dates/times. For some reason,
all of the dates/times in Excel that appear as "12/05/2008 12:00 AM"
are transfered to Access as "12/05/2008", sans the time. All of the
other times in the remaining entry (e.g., NOT 12:00 AM") transfer with
the time intact. I even tried changing the format in Access, but the
time is simply not there to format for the cases of 12:00 AM.

Of course, this issue was not caused by your module. It happens during
a regular import too.

Any thoughts?

Cheers,
Joseph