Import Excel Data (1 Viewer)

vcarrill · Dec 5, 2019

I am in need of advice in regard to importing Excel sheets.

The Excel sheet has data that is copy/pasted directly from our ERP. This data contains columns that I do not need. I then select all the columns I do not need and delete them all at once. I save this Excel sheet, then import it to Access and append the table.

I do it this way versus selecting the "do not import this column" in Access because it is too many columns to select.

Is there an easier way to do this? Please advice and thank you!

theDBguy · Dec 5, 2019

It depends. It might be easier, if you use Excel Automation.

Micron · Dec 5, 2019

Also consider that if it be the same columns every time and the spreadsheet and file name are the same every time: link to the sheet and run a query to either update or append to a table that has the fields you need.

Or use transferspreadsheet function, transfer the whole sheet into a table that takes all the columns and run same type of query or queries into the new table.

vcarrill · Dec 6, 2019

I am trying to use the transferspreadsheet function and I keep getting error 3709. In the spreadsheet I am trying to import, not all columns have a heading. Is this a problem?

Thanks

Micron · Dec 6, 2019

There are over 1000 error numbers so when you know the text please don't make us look it up. That happens all too often.

I'd say no. It's saying that in the FROM clause you've referenced a field which exists in more than one table. Even when you fix this error I'm afraid I don't know if it will fail because some columns have headers and some don't.

theDBguy · Dec 6, 2019

vcarrill said:
I am trying to use the transferspreadsheet function and I keep getting error 3709. In the spreadsheet I am trying to import, not all columns have a heading. Is this a problem?

Thanks

Hi. Just in case there's some confusion from what I said earlier, TransferSpreadsheet is not the same as Excel Automation.

Micron · Dec 6, 2019

theDBguy said:
Hi. Just in case there's some confusion from what I said earlier, TransferSpreadsheet is not the same as Excel Automation.

That would be me, post 3 I think. But your link is for VB, not VBA? I would recommend Ken Snell's site for automation between Excel and Access.

theDBguy · Dec 6, 2019

Micron said:
That would be me, post 3 I think. But your link is for VB, not VBA? I would recommend Ken Snell's site for automation between Excel and Access.

I see what you mean. Although VB code should also work in VBA, I guess showing VB code in a VBA topic could be a little confusing.

Import Excel Data (1 Viewer)

vcarrill

Registered User.

theDBguy

I’m here to help

Micron

AWF VIP

vcarrill

Registered User.

Micron

AWF VIP

theDBguy

I’m here to help

Micron

AWF VIP

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread