Importing Excel Files (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 15:39
Joined
Oct 29, 2018
Messages
13,186
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

Enthusiastic Amateur
Local time
Today, 23:39
Joined
Sep 21, 2011
Messages
7,114
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

Member
Local time
Today, 18:39
Joined
Sep 8, 2020
Messages
349
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

I’m here to help
Staff member
Local time
Today, 15:39
Joined
Oct 29, 2018
Messages
13,186
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.
Code:
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

Member
Local time
Today, 18:39
Joined
Sep 8, 2020
Messages
349
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

I’m here to help
Staff member
Local time
Today, 15:39
Joined
Oct 29, 2018
Messages
13,186
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

Super Moderator
Staff member
Local time
Today, 18:39
Joined
Feb 19, 2002
Messages
30,069
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

Member
Local time
Today, 18:39
Joined
Sep 8, 2020
Messages
349
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

I’m here to help
Staff member
Local time
Today, 15:39
Joined
Oct 29, 2018
Messages
13,186
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

Member
Local time
Today, 18:39
Joined
Sep 8, 2020
Messages
349
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

Lifelong Learner
Local time
Today, 15:39
Joined
Mar 14, 2017
Messages
2,701
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

Super Moderator
Staff member
Local time
Today, 18:39
Joined
Feb 19, 2002
Messages
30,069
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.
 

Users who are viewing this thread

Top Bottom