Sign in to follow this  
Followers 0
ThatNewGuy

Access import wizard (snag at ending)

4 posts in this topic

Hello everyone!

Naturally, I'm one of those new guys that doesn't know much about coding so please be gentle. :) I just recently found autoit and it seems like an amazing tool that we can use here at work to help automate some day to day tasks.

My objective is to import an Excel file into an Access database daily via the "Get External Data" command or more widely known as the "Import Spreadsheet Wizard." I no there are better ways of doing this compared to my approach but honestly I just don't want to start dealing with COM's and macros, etc. I'm new to this and sometimes it's better to stick with the "KISS" rule. After working through some tutorial's and postings from Bryce Whitty this is what I've come up with so far:

CODE:

ShellExecute("C:\Documents and Settings\filename1.mdb", @SW_MAXIMIZE)

WinWait("Microsoft Access")

Send("!fgi")

WinWait("Import")

Send("C:\Documents and Settings\filename2.xls")

Send("{ENTER}")

WinWait("Import Spreadsheet Wizard")

Send("!n")

Send("{ENTER}")

WinWait("Import Spreadsheet Wizard")

Send("!n")

WinWait("Import Spreadsheet Wizard", "In an Existing Table:")

Send("x") <----------------------the code breaks down right here, it pauses out

Send("{TAB}")

Everything works until I get to the 2nd to last screen where the wizard asks for the name of the existing database. I need to select this option and add the name of the old database in order to update our reports accordingly. See Below:

I've searched the forum, the FAQ, the AutoIt Help folders and I still can't seem to resolve this one screen. Very frustrating as it seems like I've tried everything from tabs, to down arrows, to shortcut keys like the "x" used in the Existing Table line and nothing seems to work. When viewing the screen it's very easy to navigate on the keyboard, but I can't seem to automate the commands.

Someone might mention that my "Import Spreadsheet" lines need to be more clearly defined, but they work. Please note I've run a test code that advances to the next screen, and that works as well, so I've pinpointed the problem of being able to select/deselect the two radio buttons and enter the database name.

Any help or direction would be greatly appreciated.

post-43814-1228937848_thumb.jpg

Share this post


Link to post
Share on other sites



The only explanation I see from your code: if it "breaks" at that point (freeze) it is because it is waiting for the window you specified.

WinWait("Import Spreadsheet Wizard", "In an Existing Table:")

From the attaced image I can see that you have got "quite exact" title and text for the window ... but ... did you check that against AutoItWindowInfo?

Have a play with Opt("WinTitleMatch",x) and try different title lenghts, try also different text contents.

Put a messagebox after WinWait and experiment as I told you above - if the MsgBox prompts then you are sure your code got after that point.

Also Opt("TrayIconDebug", 1) will show you the line currently executed.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Try adding a sleep(500) line just before the line where you mark it "freezing"...my guess is the window exists before the controls that your shortcut keypresses activate are created/usable. So waiting for the window is good, just wait half a second longer to make sure the controls exist before sending your final keystrokes.

...worth a shot at least :)

Edited by james3mg

"There are 10 types of people in this world - those who can read binary, and those who can't.""We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true." ~Robert Wilensky0101101 1001010 1100001 1101101 1100101 1110011 0110011 1001101 10001110000101 0000111 0001000 0001110 0001101 0010010 1010110 0100001 1101110

Share this post


Link to post
Share on other sites

Hey guys! Thanks a ton!!!

I tried both suggestions and resolved the problem...works like a charm! The biggest problem appeared to be the length of the text I was previously using ie "Import Spreadsheet Wizard"...this matched exactly what AutoIt Window Info was kicking out, but after shortening each line to "Import Spreadsheet" the code fell right in line. I had no idea the "length" of something would cause such a problem. I was under the impression that autoit was looking for an exact match.

James, in the second half of my code ,which I just finished, the sleep( ) line came in handy after importing all records. The code was running so fast it was giving me double enteries...yuk! I used the sleep function....problem solved. Good stuff!

Once again thanks guys!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0