Solved

Use spreadsheet header info for Access table data

Posted on 2014-04-15
13
800 Views
Last Modified: 2014-04-18
I have an excel spreadsheet that comes with header info that you can see in the attached spreadsheet.  I would like to capture the contents of lines 3 - 7 after the colon and put those contents into a table with the associated fields.  This table would be overwritten with new contents every time a new/different BOM is imported into the Access routine.  Currently I bring the raw BOM into a table then take the fields using Like "<FieldName>:*".  I take the whole field including the field name and dump it into separate tables.  Getting them all into one table with 5 fields would be optimum.
0
Comment
Question by:gibneyt
  • 7
  • 6
13 Comments
 

Author Comment

by:gibneyt
ID: 40001879
BOM Header
HLM-BOM-Header.xlsx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40003418
I think you should add some data to those headers to give us a better understanding of the parsing you require.
0
 

Author Comment

by:gibneyt
ID: 40003855
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40004001
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.
0
 

Author Comment

by:gibneyt
ID: 40004138
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?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40004253
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:gibneyt
ID: 40008776
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40008936
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

Open 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];

Open in new window

0
 

Author Comment

by:gibneyt
ID: 40009205
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.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40009292
That's not a problem.
* Pass the folder name into a routine and in that routine, iterate the .xls files in that folder.  You can use the Dir() function or instantiate a filesystemobject.
* For each .xls file, do the following before refreshing the tabledefs collection.  This will point to the current workbook in the iteration and reattach it to the database.
dbengine(0)(0).TableDefs("BOMdata").SourceTableName = "BOMdata"
dbengine(0)(0).TableDefs("BOMdata").Connect ="Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & parmFolderpath & "\" & strCurrentBOMworkbookname

Open in new window


=======================
Note: such bulk imports can be done without linked tables.  A query can refer to a workbook by including the path in and IN clause.  You could execute one query for each workbook in the folder.
dbEngine(0)(0).Execute "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 BOMdata IN " & Chr(34) & parmFolderpath & "\" & strCurrentBOMworkbookname 
 & Chr(34) & "EXCEL 5.0;"

Open in new window


If you went this route, you can skip the creation of the named range and use the A1 notation to refer to the data range.  This would be quicker, since you could open each workbook as read-only and then close it, saving you half the I/O and the ("BOMdata") name add.
Example:
dbEngine(0)(0).Execute "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 [Sheet1$A9:P30] IN " & Chr(34) & parmFolderpath & "\" & strCurrentBOMworkbookname  & Chr(34) & "EXCEL 5.0;"

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40009308
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 "

Open in new window

0
 

Author Closing Comment

by:gibneyt
ID: 40009498
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40009524
@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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now