Importing Excel Files

[theDBguy]

Could you possibly give an example of how to use the DoCmd.Transferspreadsheet? I am reading the docs on it now, but I am finding it a little confusing.

I see. I will give that a try!

Let us know how it goes or if you still need an example for using TransferSpreadsheet.

 

[Gasman]

I actually thought about that, but I could very easily see my users forgetting to do that. The less I have to rely on them doing every step correctly, the better lol.

Well, I was thinking more of you letting them select a file, then you copy it to the same location and name as your spec, them import.?

 

[tmyers]

Im finding the various codes for each specific operation, but not how to tie them together.
I have the code to open the filedialog window, found the code for copy, paste, rename, delete, but cant figure out how to store the selected file as a variable to pass it to the rest of the code to run those.

 

[theDBguy]

Im finding the various codes for each specific operation, but not how to tie them together.
I have the code to open the filedialog window, found the code for copy, paste, rename, delete, but cant figure out how to store the selected file as a variable to pass it to the rest of the code to run those.

Okay, here's a quick example you could try.

Public Function ImportXL() As Boolean
'thedbguy@gmail.com
'10/9/2020

Dim fd As Object
Dim strFile As String

Set fd = Application.FileDialog(3)
    
With fd
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*"
    If .Show Then
        strFile = .SelectedItems(1)
    End If
End With

If strFile = "" Then
    ImportXL = False
Else
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempTable", strFile, True
    ImportXL = True
End If

Set fd = Nothing

End Function

 

[tmyers]

Ill be honest, I never would have figured this out. Too green in VB.

But it works wonderfully! I would buy you a beer, but this will have to suffice

 

[theDBguy]

Ill be honest, I never would have figured this out. Too green in VB.

But it works wonderfully! I would buy you a beer, but this will have to suffice

Hi. Glad to hear you got it to work. Good luck with your project.

 

[Pat Hartman]

I would NOT create different tables for different vendors. The idea is to ultimately have ALL quotes "normalized" and stored in a SINGLE table. The only way you can compare quotes from multiple vendors is if you get them into a consistent format.

Start by trying to get the vendors to use YOUR format. They may all agree but even if only some agree, you're ahead of the game. You will then have to create code customized to import the file from each vendor. Depending on how awful the format is, you might be able to link to the spreadsheet and ignore "pretty printing" rows and just pull in data or you might have to write automation code to pick stuff up from hither, thither, and yon.

 

[tmyers]

Currently I have them going through a bit of a process (there are almost certainly cleaner ways, but this works and hasn't had issues so far).
Using the code DBGuy provided (again, thank you so much), I have set up temp tables for each vendor (they all use their own application, and most of them can't change its format).

With a click of a button, the file dialog window allows them to pick a file to be imported. The file is imported to the temp table then several queries run. One deletes empty rows based on nulls specific to that vendor. Then an Update query updates the imported items with the JobID tying them to the specific job they are on then an append query moves them to the products table. The final set of queries delete all info out of the temp tables (resetting them) and I have a code that runs and deletes all importerror tables created in the process. That last part actually runs just after the import, but before the queries.

 

[theDBguy]

Hi. Looks like you have a good ETL process in place now. Just make sure to watch the file size of your database and maybe do a C&R once in a while.

 

[tmyers]

Will do!
Thankfully, the stuff I am importing isn't very large. 100-150 lines absolute tops for each vendor. Average I would say is less than 50 line per vendor with an average of 3 per job.

I would imagine it would take a long time to get to the 2g mark of Access. If it happens quickly, I will have to have our IT department import it to SQL server.

 

[Isaac]

Will do!
Thankfully, the stuff I am importing isn't very large. 100-150 lines absolute tops for each vendor. Average I would say is less than 50 line per vendor with an average of 3 per job.

I would imagine it would take a long time to get to the 2g mark of Access. If it happens quickly, I will have to have our IT department import it to SQL server.

FYI, Access size bloat is often tied to records that don't exist any more, but were there at some point.
For example. If you imported 500 records, then deleted all but 50, the bloat would be more closely tied to the 500 than the 50.

 

[Pat Hartman]

You are better off linking to the spreadsheets rather than importing them. Then use an append query to select the data you want to append to the permanent tables.