Automating Import of Excel Table: Missing Some Options, How to Code?

[roxannep]

I'm trying to automate the import of an Excel table into my database.<br><br>I have it set up in a macro, with the<br>TransferSpreadsheet instruction, however, the piece that is missing and keeps hanging it up when testing is that when you run the Get External Data manually, one of the options that comes up is to allow Access to set the Primary Key Field. This is indeed how I want it done. When the remainder of the Wizard is run, the whole thing comes it beautifully allowing me to run several macros and action queries to disburse this data as I need to into a number of tables in the database.<br><br>In the TransferSpreadsheet macro instruction, however, there is no option to determine how the primary key is set, or to set one. This needs to happen or the import runs into an error and I cannot pull the data out using all the other instructions I've set up. <br><br>Any ideas on how to handle it?

 

[Lightning]

Try using the SendKeys action in your macro to send the {Enter} key to accept Access creating your primary key.&nbsp;&nbsp;This should get through that step and let your macro run straight through.<br><br>Lightning

 

[roxannep]

Thought of that already, no go. There is no parameter in the TransferSpreadsheet that even brings up the option to select from three choices regarding the Primary Key instructions as it does in the import Wizard, so there is nothing to SendKey {Enter} to, at least not that I can see when programming this macro. If I'm missing where or how to set it up, I would really appreciate further details, but in the TransferSpreadsheet action, there is no option available, no parameter open to add that to.<br><br>But hey, I'm still pretty new at this and if you can point me to the place I put the SendKey instruction to indicate Access should add the Primary Key field and autonumber it during import, I'm all ears!

 

[Lightning]

The Sendkeys statement doesn't go in the TransferSpreadsheet command - it is a separate command line in the macro.&nbsp;&nbsp;Also, it needs to be the line BEFORE the TransferSpreadsheet command line.&nbsp;&nbsp;Access will store the keystrokes sent until it needs them, and then run them automatically.