Access import wizard (snag at ending)

[ThatNewGuy]

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.

[enaiman]

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.

[james3mg]

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 December 10, 2008 by james3mg

[ThatNewGuy]

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!