Needing to parse and assemble data from thousands of workbooks to one worksheet

[RichKat]

Hello,
We have used an Excel template for years to do garage door quotes for customers. The template is set up to auto-number and save the sheet to a directory. This has worked but now we would like to put all of those individual sheets into a single Access Database. The sheets have all the same pieces of data in the same place every time but it isn't structured in a column/row format. Below is what the table looks like. Any suggestions on how I could parse thousands of these Workbooks (one sheet per workbook) into rows in a single Excel worksheet, or better yet, into records in Access?

[TABLE="width: 686"]
<colgroup><col><col span="4"><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD="colspan: 5"]Blue Ridge Structures[/TD]
[TD]Quote #[/TD]
[TD]2155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"] [/TD]
[TD="colspan: 3, align: left"]

<tbody>
[TD="colspan: 3"]DATE[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]Phone[/TD]
[TD="colspan: 5"] [/TD]
[TD="colspan: 3"]March 15, 2017[/TD]
[/TR]
[TR]
[TD]Qty[/TD]
[TD="colspan: 5"]Description[/TD]
[TD]List[/TD]
[TD]Price[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 2"]Model #[/TD]
[TD="colspan: 3"]2250[/TD]
[TD]$458.00[/TD]
[TD]$368.69[/TD]
[TD]$368.69[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Insulated?[/TD]
[TD]No[/TD]
[TD]Res/Co[/TD]
[TD]Residential[/TD]
[TD] [/TD]
[TD]Feet[/TD]
[TD]Inches[/TD]
[/TR]
[TR]
[TD]Size[/TD]
[TD] [/TD]
[TD]9[/TD]
[TD]X[/TD]
[TD]7[/TD]
[TD]Width[/TD]
[TD]9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Color[/TD]
[TD="colspan: 3"]Sandstone[/TD]
[TD]Height[/TD]
[TD]7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Windows (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Window Inserts (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Struts (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD]$15.30[/TD]
[TD]$15.30[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 3"]Locks (Per Door)[/TD]
[TD="colspan: 2"]inside[/TD]
[TD]$7.89[/TD]
[TD]$7.89[/TD]
[TD]$15.78[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Low HR Kit (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD]$66.50[/TD]
[TD]$66.50[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="colspan: 3"]Weather Strips (Per Door)[/TD]
[TD="colspan: 2"]23[/TD]
[TD]$1.70[/TD]
[TD]$1.70[/TD]
[TD]$39.10[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Perfed Angles (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD]$16.50[/TD]
[TD]$16.50[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Misc (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Installations (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD]$126.00[/TD]
[TD]$126.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Mileage Total (Per Job)[/TD]
[TD="colspan: 2"]One Way[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Old Door Removal (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD]$50.00[/TD]
[TD]$50.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 6"]Sub Total[/TD]
[TD] [/TD]
[TD]$423.57[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 6"]Sales Tax[/TD]
[TD][/TD]
[TD]$22.45[/TD]
[/TR]
[TR]
[TD]0%[/TD]
[TD="colspan: 6"]Discount[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 6"]Total[/TD]
[TD] [/TD]
[TD]$446.02[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Opener (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Opener Misc (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Opener Installation (Per Door)[/TD]
[TD="colspan: 2"] [/TD]
[TD]$75.00[/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="colspan: 3"]Opener Remote (Per Job)[/TD]
[TD="colspan: 2"]973LM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 6"]Sub total[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 6"]tax[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]0%[/TD]
[TD="colspan: 6"]Discount[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 6"]total[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 6"]Grand total[/TD]
[TD] [/TD]
[TD]$446.02

[/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much for your help. I'm even open to paid software if you know of something that won't break the bank.

Richard

 

[Trevor_S]

I don't know enough about Access to get the data directly into there, but I can combine it into a single Excel workbook. Presumably Access has the option to import a data source from Excel.

I've uploaded an excel workbook with macro code to do the conversion here https://drive.google.com/file/d/0B_dlXxlE0XVBemNSTTRteUlRdWc/view?usp=drivesdk . Or if you would prefer to create it yourself rather than download from a link, I've put the macro at the end of this post. The workbook contains a single sheet. Row 1, columns A onwards, should contain the text that you want to appear as the header for each column. Row 2, columns A onwards, should contain the cell reference that the data appears in your individual quote workbooks. For example if the name is always in cell B1 and the phone number is always in cell B3, the workbook should appear as:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Phone[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B1[/TD]
[TD]B3[/TD]
[/TR]
</tbody>[/TABLE]


The important bit is that you must not leave any blank columns in this table. On the uploaded version, I've completed the first 10 columns with the cell references that I think that they may need, based on the extract that you pasted, but you should still check these plus complete the rest. When you've done this, you're ready to start.


Your quote workbooks should be in a separate folder to this workbook, and there should be no other files in that folder, as the macro will try to gather everything that it can find!


Start the macro (you can click the button in the top left corner if your using the uploaded version). You will be prompted to select the folder containing the quote files. Navigate to that folder and click Ok. Then you will be asked for a filename/location to save the output file (the file that will have all the quotes combined). You can call it anything you like, but don't put it in the folder containing the individual quote files!


The macro will then copy the column headings, then open each quote in turn, copies across the data into the relevant columns, then closes it again. It copies values (to avoid potential issues of creating formula links between files) - this may mean that you'll need to format date and amount columns in the combined workbook once its completed. Progress is reported in the status bar (bottom left corner), although you won't see the list build up on screen. Once complete, the combined output file will be saved and displayed on screen.


The macro (in case you want to produce the workbook yourself) is:

Sub ConvertFiles()


Dim TempWb As Workbook
Dim SummWb As Workbook
Dim SceWb As Workbook


'Get folder containing files
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    On Error Resume Next
    myFolderName = .SelectedItems(1)
    Err.Clear
    On Error GoTo 0
End With
If Right(myFolderName, 1) <> "\" Then myFolderName = myFolderName & "\"


'Settings
Application.ScreenUpdating = False
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Set TempWb = ActiveWorkbook
Set SummWb = Workbooks.Add


'Get name to save output file
myFileName = Application.GetSaveAsFilename("Combined Data.xlsx", "Excel Workbook (*.xlsx), *.xlsx", , "Save combined file as")
SummWb.SaveAs (myFileName)


'Count number of fields and copy across headers
DataCols = TempWb.ActiveSheet.Range("A1").End(xlToRight).Column
For n = 1 To DataCols
    SummWb.ActiveSheet.Cells(1, n).Value = TempWb.ActiveSheet.Cells(1, n).Value
Next


'Get source files and append to output file
myFileNum = 1
mySceFileName = Dir(myFolderName & "*.*")
Do While mySceFileName <> "" 'Stop once all files found
    Application.StatusBar = "Processing: " & mySceFileName
    Set SceWb = Workbooks.Open(myFolderName & mySceFileName) 'Open file found
    For n = 1 To DataCols
        SummWb.ActiveSheet.Cells(myFileNum + 1, n).Value = SceWb.ActiveSheet.Range(TempWb.ActiveSheet.Cells(2, n).Value).Value
    Next
    SceWb.Close (False) 'Close Workbook
    myFileNum = myFileNum + 1
    mySceFileName = Dir
Loop


'Settings and save output file
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
SummWb.Activate
SummWb.Save
Application.ScreenUpdating = True


End Sub

Hope this helps!

 

[RichKat]

Wow, Trevor!
That looks absolutely awesome! I haven't tried it yet because I ran into a potential snag. I'm worried about this because I've heard it can really cause problems, and probably especially with what we are trying to do. When I set this sheet up it was just a quick way to do quotes to eliminate a paper form and automate the calculations. So to get it to LOOK like the paper form, which is what the staff was used to, I merged cells in many places. Will Excel look at the first cell of the merged cells or will it crash the code when it hits one?
Thanks so much for what you did. It's far more than I expected!

 

[Trevor_S]

Thought you might have some merged ones from the layout! It will work, but you need to use the top left cell of the merged range. So if Name is merged over B1 to C2 on your forms, put B1 on the second row of my spreadsheet.
My macro leaves the original workbooks untouched (it opens, takes the values and closes them without saving them), so you could always run it and then check a few rows against the workbooks to make sure you're happy with the output. If you need to make any tweaks to the cell references, you can do it, and just re-run.

 

[Trevor_S]

I suppose another question is do you actually want them on Access? If its just to reduce the number of sheets, you could have a two-sheet workbook. Sheet 1 would be the table as produced by my macro. Sheet 2 would be a blank version of your template. You could then have macro buttons that would allow you to:

• complete a new quote in the template (getting the next quote number in sequence) and then automatically add it to the table, or
• retrieve an existing quote from the table into the template, to either edit or print.

Going one step further ... if your prices are fixed from job to job, you could build a price list in too, so when you produce a quote, you can pick items from drop downs, and the prices auto-complete...?

 

[Stiuart_W]

I'm not going to add to what Trevor_S has said except to say:

You might like to edit some of the numbers and put garbage in your first post as your competitors may stumble across it!
Also, If you can I would urge you strongly to get the data transferred to Access. This will afford you much better analysis, report writing, storage for future.
Do you have a local college with an IT department? Would be a great "Little" project for an IT student - All you need to do is consider a good NDA and set them off

 

[RichKat]

Ahh! I should have known you would have thought of that. Ok, I'll try it!

 

[RichKat]

Yes, and a good question. Problem is, it's all shared with 7 computers on the network and I'm not that proficient at creating the Excel interface. But, thanks for putting that suggestion in the box.

 

[RichKat]

Thanks Stiuart! Good ideas!

I'm not going to add to what Trevor_S has said except to say:

You might like to edit some of the numbers and put garbage in your first post as your competitors may stumble across it!
Also, If you can I would urge you strongly to get the data transferred to Access. This will afford you much better analysis, report writing, storage for future.
Do you have a local college with an IT department? Would be a great "Little" project for an IT student - All you need to do is consider a good NDA and set them off

 

[Trevor_S]

No problem, hope it helps. I'd agree with Stuart, it is really more what Access is designed for, I've only always favoured Excel because its what I've used most so know more about!