Programatically Copy Data from Excel and Paste to Access Table

[EhWot]

Hi all,

I've had to resort to the above as I have some quite 'Dirty' data in Excel which doesn't respond well to the Access 97 import process, and I have to 'Import' fresh excel data daily. I'm guessing this is a really monkey way of doing things so does anybody know of any possible pitfalls/dangers?

The 'Copy' procedure in Excel will overwrite any data already on the clipboard, right? Anybody?

Any head's up would be greatfully appreciated.

Thanks all..

 

[xlbo]

You are correct in assuming that the copy command will clear any data on the clipboard.
If you are programming (seems likely given the forum) application.cutcopymode = false will clear the clipboard just in case.
I have to ask tho, why can you not clean the data up in excel and then use the normal import process......or just link the file......or use ADO recordsets to populate the table. If you're gonna be programmatically copying the data in, your gonna have to already have set a reference to the app that your not coding in so why not use recordsets ???

Hope this gives you some ideas
Geoff

 

[EhWot]

Hey Geoff,

Thanks for replying, I'll include the cutcopymode code to be on the safe side. In answer to your question the import process has to be automated as users need to be able to import on a daily basis at a click of a button; I tried linking the Excel file but the .xls file is overwritten daily and Access doesn't like it if the file it linked to disappears (Even if it is replaced by one of the same name.)

If I try and import the file using the normal import process I run into problems as I have some fields with a mixture of Text and Number entries. If the first 8 entries are Text then no problem but if there's and numbers in first 8, Access treats the entire Field as numbers and will blank out any Text entries (I.e. the data will be lost).

I tried formatting the Excel sheet before import but to no avail.

After this I tried using code, i.e.

 rs.Fields(i).Value = objXL.Application.ActiveCell.Offset(j, i).Value

using Loops to increment the Field and Row, however this effectively imports each cell at a time and took about 10 mins as the Excel file is around 4000 rows with 20 Fields. My users won't wait that long so I had to resort to copy/paste.

Is there any way of carrying out a similar Recordset operation but by Row or Field instead of cell by cell or Am I barking up the wrong tree?

Thanks again for replying.

 

[xlbo]

Hmmm - not sure about copying the whole field in one recordet - here's another suggestion tho:
Can you save the excel file as .csv ?
If so, you can set up an import spec and tell Access which format each field is.

Once the import spec is saved, you can just call it from code and it will import with the same field format each time. Use either the transfer text macro option or the transfertext method of the Docmd function.

HTH
Geoff

 

[EhWot]

Sounds like a plan, I guess you could save a .csv version, import it and then delete the .csv file so everything is nice and tidy. Will give this a go.

Thanks for the advice Geoff, much appeciated!

 

[xlbo]

That's the method I'd use - shouldn't take too long either. Only prob is that you're gonna have to open the excel workbook to save as csv but I guess you'd have to do that to c&p anyway. As I recall, setting up the import spec and referencing it from Access is dead easy and it should take very little time to save as csv and you can use the :
Kill "TestFile" command to delete the file afterwards
Hth
Geoff

 

[Guest_imported]

Erm the application will be importing tables of which the field names I won't know in advance.I've searched the net...found a couple of examples but they didnt work in my case.I need to find out the exact field names.

Something I tried was:

adorecordset.Open "xxx", adoConnection
Do Until adorecordset.EOF
List1.Additem adoRecordset!Name
adoRecordset.MoveNext
Loop

pfff no good though

 

[xlbo]

You should post your message as a new thread - very few people will pick this up
Rgds
geoff

 

[Guest_imported]

How do you import a text file with no headers. It's always asking for "F1" instead of the actual field name. Any suggestions?

 

[xlbo]

Post this under a new thread - did you not see my message to
HelloDP Help