Import Excel Data

[vcarrill]

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]

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

 

[Deleted member 147267]

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]

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

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

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]

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.