[AccessD] Normalizing Excel Data

Why not do it through ADO rather than import, you can retrieve 
individual sheets too.

    'Open the ADO connection to the Excel workbook
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Results\Orders1.xls;" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""

Then just move the Excel fields into Access Tables.

ExcelADO demonstrates how to use ADO to read and write data in Excel 
workbooks
http://support.microsoft.com/kb/278973/EN-US/

and
http://support.microsoft.com/kb/257819/EN-US/

Here are some slightly different methods but still ADO based
I tested by exporting  Northwind Products table to xls file.

Sub test()
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Set oRS = New ADODB.Recordset
Set oConn = New ADODB.Connection
With oConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open "C:\Excel\ExcelADO\Results\Products.xls"
    '....
   
    'Set oRS = cConn.Open("Select * from [Sheet1$A1:B10]", oConn, 
adOpenStatic)
With oRS
 .Open "Insert Into tbltest SELECT * FROM [Products$A1:B10]", _
         oConn, adOpenStatic
 '.Open "SELECT * FROM [Sheet1$] WHERE LastName = 'Doe'", _
 '        cnnDB, adOpenDynamic, , adCmdText
 Debug.Print oRS.RecordCount
 .Close
End With
    .Close
End With

End Sub



Sub ImportExcelToAccess()
Dim sqlString As String
Dim strexcelpath As String
Dim dbs As Database
Set dbs = CurrentDb
strexcelpath = "C:\Excel\ExcelADO\Results\Products.xls"

'Import to Excel
'Assumes Access table does not already exist

sqlString = "SELECT * INTO [tblExcelNew] FROM [Excel 8.0;DATABASE=" & _
             strexcelpath & ";HDR=Yes;IMEX=1].[Products$A1:B100]"
'export to Excel
sqlString = " INSERT INTO [Excel 
8.0;Database=C:\temp\MyWorkbook.xls;].[MySheet1$E6:G65536]" & _
            "SELECT * FROM MyTable WHERE MyKeyCol = 99"
           
'Assumes Access table exists
'sqlString = "INSERT INTO [tblExcelNew] (Product, UnitPrice, 
UnitsInStock)" & _
' " SELECT Product, UnitPrice, UnitsInStock from" & _
' "[Excel 8.0;DATABASE=" & strexcelpath & ";HDR=Yes;IMEX=1].[Products$];"

Debug.Print sqlString
dbs.Execute sqlString
Set cnn = Nothing

End Sub

ewaldt at gdls.com wrote:

>This will be a simplified version of what I need. I believe I can expand it
>appropriately, if I can be helped in this simplified version.
>
>Let's say we have an Excel spreadsheet with the following data:
>
>ID    Name  Data_A      Data_B      Data_C
>100   Tom   11    22    33
>101   Mary  44    55    66
>102   John  77    88    99
>
>Now, what I want to do is to import this into Access, where I've already
>normalized tables as follows:
>
>tblMain
>Main_ID     Long integer, autonumber
>Main_Num    Single
>Main_Name   Text
>
>
>tblData
>Data_ID     Long integer, autonumber
>Data_Num    Single
>Data_Value  Single
>
>
>What I need to create from these tables and the appropriate relationship is
>as follows:
>
>Data_Num    Data_Value
>100.01            11
>100.02            22
>100.03            33
>101.01            44
>101.02            55
>etc.
>
>What I am doing is creating a new ID number for each Name-Data combination,
>and entering its given data in that record. For each value in DataA, the
>new ID number is formed by adding .01 to the Main_Num for the corresponding
>Name record.
>
>This is required by business rules, not by logical database rules. Sigh.
>Anyway, I'm looking for logical ways to (1) set this up initially, (2)
>import the Excel data, which is, of course, flat file, and (3) have it
>available afterwords for normal database usage, meaning inputting data
>directly, rather than through importing.
>
>I hope this is clear (I wouldn't place any bets on it, though). Any help
>would be sincerely appreciated. I've been working on it for quite a while,
>but just can't seem to get a handle on it.
>
>TIA,
>
>Thomas F. Ewald
>FCS Database Manager
>General Dynamics Land Systems
>(586) 276-1256
>
>
>
>
>
>This is an e-mail from General Dynamics Land Systems. It is for the intended recipient only and may contain confidential and privileged information.  No one else may read, print, store, copy, forward or act in reliance on it or its attachments.  If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada