Use spreadsheet header info for Access table data

BOM Header
HLM-BOM-Header.xlsx

I think you should add some data to those headers to give us a better understanding of the parsing you require.

The fields would be alphanumeric and should be no larger than 50 characters.  There could be space delimited sentences or single character entries.  My preference would be to treat each record as a complete entry minus the left side up to and including the colon/space.  I do believe each of the rows in the attached spreadsheet has a space included after the colon on each of those 5 records.  Here are some possible entries.

PCB Name: AD9148-EBZ Rev B (09028B)                                                                                    
PCB Part Number: 35-000539-02    A0539-2013                                                                                    
Design Code: 09028                                                                                    
Rev:       1.0                                                                              
BOM Generated: 11/22/2013

In your routine that does the iteration, add a recordset definition that points to the new multi-column table.

Before you start iterating, invoke the recordset's .AddNew method.
In each iteration, assign a field value.
After you stop iterating, invoke the recordset's .Update method.

I don't think I can do what you suggest because of the way I am getting the spreadsheet into the db.  I have created a Link to the spreadsheet then use tblHLM_BOM-Link to build tblHLM_BOM_RAW with all the spreadsheet data.  Here's the SQL for the Make Table query  qrytblHLM_BOM_RAW-Build:

SELECT [tblHLM_BOM-LINK].F1 AS [ITEM#], [tblHLM_BOM-LINK].F2 AS Qty, [tblHLM_BOM-LINK].F3 AS LOCATION, [tblHLM_BOM-LINK].F4 AS WW_PN, [tblHLM_BOM-LINK].F5 AS DESCRIPTION, [tblHLM_BOM-LINK].F6 AS [VALUE], [tblHLM_BOM-LINK].F7 AS JEDEC_TYPE, [tblHLM_BOM-LINK].F8 AS TOL, [tblHLM_BOM-LINK].F9 AS VOLTAGE, [tblHLM_BOM-LINK].F10 AS WATTAGE, [tblHLM_BOM-LINK].F11 AS MFG, [tblHLM_BOM-LINK].F12 AS MFG_PN, [tblHLM_BOM-LINK].F13 AS HLM_STATUS, [tblHLM_BOM-LINK].F14 AS STOCKROOM_WL, [tblHLM_BOM-LINK].F15 AS DNI, [tblHLM_BOM-LINK].F16 AS Comments INTO tblHLM_BOM_RAW
FROM [tblHLM_BOM-LINK];

I do this because it may come to pass that other fields could be added to the spreadsheet and I would like to capture them on the fly.  I have not made that possible but it is something I would like to work towards.

So the five fields I am looking for along with all the item numbers are in F1/ITEM#.  I then parse out PCB Name and PCB Number to two tables with similar queries:

SELECT tblHLM_BOM_RAW.[ITEM#] INTO tblPCB_Name
FROM tblHLM_BOM_RAW
WHERE (((tblHLM_BOM_RAW.[ITEM#]) Like "PCB Name:*"));

I am not married to the above structure but it is already built and working.  I still strive to have the data portion of those 5 fields parsed into one table with corresponding headers.

I just tried to use one query to get the BOM data into one table but could not figure it out though I am sure there is a way.

Is there a way to incorporate your suggestion into the above process?

Is your link to the entire worksheet or do you start with row 9?
Did you merge the cells or did someone else do that?

My gut feeling is to simplify your process.
Use Excel automation to move the row/column data from sheet1 into the top of sheet2.
Then populate an array variable with the A1:A7 values.
Save and close the workbook.

You should then be able to import the row/column data directly without having to use column aliases.

You then iterate the array variable to populate the columns you want from the header data.  You will probably use the Split() function to separate the heading from the colon separator.

The link is to the entire worksheet.

The spreadsheet is the massaged output from a schematic capture program.  I have no control over either process.

Simple is preferable but I can only develop what I know.  I haven't used and don't know how to do what you suggest.

Tim

This might be a simpler approach.
1. add a named range to your worksheet, associated with the row/col data.  In this example, I used the name BOMdata.
2. Change the tblHLM_BOM-LINK table definition from the worksheet name to the range name (with column headers).
3. Add the following PrepareBOMworkbook() routine to a module in your Access database.
4. When you call the function, it will return a 2D array of values from the BOM header cells.
5. You can iterate the array and parse each item, inserting the values into your header table.  The data seems simple enough to parse with a Split() function.  You will also need to Trim() the result.

Option Explicit


Public Function PrepareBOMworkbook()
    Dim oXL As Object
    Dim oWkb As Object
    Dim oWks As Object
    Dim oRng As Object
    Dim vHeaderData As Variant
    Const xlCellTypeLastCell = 11
    Set oXL = CreateObject("excel.application")
    Set oWkb = oXL.Workbooks.Open("c:\users\aikimark\downloads\HLM-BOM-Header.xlsx")
    Set oWks = oWkb.Worksheets("Sheet1")
    Set oRng = oWks.Range(oWks.Range("A9"), oWks.Cells.SpecialCells(xlCellTypeLastCell))
    oXL.Names.Add "BOMdata", oRng
    Set oRng = oWks.Range(oWks.Range("A3"), oWks.Range("A7"))
    vHeaderData = oRng.Value
    oWkb.Close True
    Set oRng = Nothing
    Set oWks = Nothing
    Set oWkb = Nothing
    oXL.Quit
    Set oXL = Nothing
    PrepareBOMworkbook = vHeaderData
    DBEngine(0)(0).TableDefs.Refresh
End Function

Select allOpen in new window

Now you can rework your import query and process.  The query will be much simpler, since the columns are now named.
Example:

SELECT [ITEM#], Qty, LOCATION, WW_PN, DESCRIPTION, [VALUE], JEDEC_TYPE, TOL, 
VOLTAGE, WATTAGE, MFG, MFG_PN, HLM_STATUS, STOCKROOM_WL, DNI, Comments 
INTO tblHLM_BOM_RAW 
FROM [tblHLM_BOM-LINK];

Select allOpen in new window

My issue with the above is the fact that I have many BOMs.  Each is named with <product numbers and rev>.xls.  When I am ready to import one of the BOMs into the Access routine I call one up and Save As HLM_BOM_MAX.xlsx; I keep a copy of the original and save a copy to the BOM import location defined by the link.

The A1 format range spec can be obtained after the first setting of the oRng variable, the range variable we used for the named range.  It might look something like this:

"FROM [Sheet1$" & oRng.Address & "] IN "

Select allOpen in new window

Wow.  Can you see my head spinning?  I might be able to incorporate half of that in the next month if I'm lucky...  Thanks so much, much more than I expected but then this is Experts-Exchange.  Like I said it'll take me a while so I won't leave you hanging ungraded.  I may come back to this topic and request pointers.

Regards,
Tim

@Tim

Can you see my head spinning?

Sort of.  As I was writing, I thought of what any reader might make of my "you can do it this way or that way or even this other way..." comment.  Your http:#a40009205 comment revealed the true nature of your problem that needed solving.  You weren't faced with getting the header information into several rows of a table.  You needed to import both the BOM detail and BOM Header data from an entire directory of workbook files.

You can also open a new question if you need help with your implementation.  If you do, please include a link to this question in your new thread and post a link to the new thread in this question thread.