automatically import multiple named-ranges from Excel to Access

I

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
 
I

icystorm

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

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
 
I

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.
Click to expand...

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

Cheers,
Joseph
 
K

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

automatically import multiple named-ranges from Excel to Access 1
Import multiple ranges from EXCEL to multiple tables 6
Import same Excel ranges from many workbooks 3
Import Excel ranges - clarify 12
Merging Multiple Named Ranges 5
Importing named ranges 1
Deleting all named ranges that have a workbook scope 3
Add fields when importing from EXCEL 4

Top