automate data converting (import) task from 2003 Excel to 2007 Access using VB

[WuJu]

I want to automate data converting (import) task from 2003 Excel to 2007 Access using visual basic. There is an obstacle. Data in Excel are different format than I want to have in Access table, so can’t just use built-in importing step in Access.

What is the best way to automate conversion(import)of excel to access when they are two different format?
If you could address this problem, I appreciate it.

Regards,

WUJU



FYI. The following is the brief info for my data format in excel and access.

Excel:
Hour 1 Hr2 Hr 3 .................... Hr24
Date1: data1 data2 data3 ................. data24
Date2: data25 data26 data27 ................ data48
Date3: ................

Access:
Date Hour Data field
Date1 1 data1
Date1 2 data2
Date1 3 data3
..................
Date1 24 data24
Date2 1 data25
Date2 2 data26
..............

 

[pbaldy]

Two ways come to mind. If you can link to the Excel file, you can create a UNION query that stacks the data the way you want. That's probably the simpler solution. Otherwise, you can use automation to step through the Excel file:

http://support.microsoft.com/kb/219151