Pulling Excel Table into Access via VBScripting

[Breezer23]

I'm trying to pull in a table from Excel into Access but am running into a ton of data type errors. The dates, which are formatted as dates in Excel, just transfer over as text, etc. Is there a way to simplify this proccess and ensure data is transferred accurately?

The code I'm using as as simple as the following:

Sub test()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TABLE NAME", "FILE LOCATION\FILENAME.xls", True
End Sub

Better yet, if it would only import whatever rows whose first column entry was contained the word 'Final' would be even more ideal.

Any help would be great.
Regards,


Travis

[orange]

Use google youtube import excel into access

[June7]

Are you importing into an existing table?

[Breezer23]

Are you importing into an existing table?

Ideally, yes, however, if it's easier not to, it can be within its' own table where I can run an append query from it to the main table.

[June7]

Test that and see what happens. I have seen cases where Excel and Access didn't communicate nicely about importing date values. If you still have issues, post your Excel file for testing.

[Breezer23]

Test that and see what happens. I have seen cases where Excel and Access didn't communicate nicely about importing date values. If you still have issues, post your Excel file for testing.

Unfortunately, the information included in the database is confidential so I can't share it - unfortunately. It's mildly annoying how Access assumes Data Types based. For example, I have a cell that includes summaries however, not all the historical data has a summary for it so the first 500 entires are blank. Because of this, Access just assumes it's a number field so the other 500 entries that do have a summary are imported incorrectly. This was resolved by sorting the Excel table differently but that is just a work around. This forum has been an incredible resource to help build my understanding of Access so I can't think you all enough.

A bit of information - The first column in the Excel table is a status. It can be either In Progress or Final. If that first column is Final, I would want the Access script to pull a set of 38 other fields/columns out for that row/line item. There are approximately 75 fields in total (yes I know - insane..) so I would assume the code would be rather long.

I will play around with what you mentioned, though I'm not entirely sure where to start, I will give it a try!
Regards,
Travis

[June7]

What's confidential - name, address, phone, SSN, email - delete or replace with dummy values.