Jump to content

Help with Excel


Recommended Posts

I have reports and forms coming from database. I have to verify and modify the forms according to rules stored in Excel. Also I have to maintain some secondary records in Excel. Since the database is priproetary I don't have anyway to hook up Excel into it and I have to use AU to do everything through windows.

So far I managed to open Excel and all files from AU but I need to know how to navigate and read/write into cells.

When I checked the forums - everyone talks about ObjCreate and ObjGet but they only supported in Beta which I can't install.

I would really appreciate any help, ideas or code.

Link to comment
Share on other sites

I would not use 'Send' unless it is a last resort. Using VBA or COM is much better. If you cannot wait for the beta to become a full release, have you looked at exporting the database as say CSV and reading it in using Excel VBA?

Send will definately work though. Note that I think that the AutoIt menu commands don't work in Excel because they are controls instead of menus.

“Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.”AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.

Link to comment
Share on other sites

I would not use 'Send' unless it is a last resort. Using VBA or COM is much better. If you cannot wait for the beta to become a full release, have you looked at exporting the database as say CSV and reading it in using Excel VBA?

Send will definately work though. Note that I think that the AutoIt menu commands don't work in Excel because they are controls instead of menus.

<{POST_SNAPBACK}>

I can't export data - database does not support it. I only get screen forms. Even reports are forms.

All I basically need is to select a sheet and cell on it or a group of cells - then read from it or write into it an AU variable/array.

I had seen people comments on COM but can't find practical code to implement it.

Link to comment
Share on other sites

Ok, if you want to use excel, look in the help file under Keyboard shortcuts:

example:

winactivate("Microsoft Excel")
;I want to select range D9 to E12
send("{f5}")
winwait("Go To"); default go to box
send("D9:E12"); range
send("{enter}") 
send("^c"); copy the range, basically sends a tab delimited array into clipboard.
; work with the data

Just from memory, but this should get you started.

I would personally try and use controlsend when possible, and actually I would use obj/com but that is me.

edit, before obj/com in beta, I used excel with send. Macros help quite a bit, but that might be disabled in excel as well. Keyboard shortcuts can really cut down on errors, and many excel tasks can be autoit scripted very reliably.

I am not sure when you say database that you are refering to excel, it sounds more like an access or other database (forms, reports, etc.) If you need help pulling information from those it gets a bit more tricky depending on access rights.

Edited by scriptkitty

AutoIt3, the MACGYVER Pocket Knife for computers.

Link to comment
Share on other sites

Ok, if you want to use excel, look in the help file under Keyboard shortcuts:

example:

winactivate("Microsoft Excel")
;I want to select range D9 to E12
send("{f5}")
winwait("Go To"); default go to box
send("D9:E12"); range
send("{enter}") 
send("^c"); copy the range, basically sends a tab delimited array into clipboard.
; work with the data

Just from memory, but this should get you started.

I would personally try and use controlsend when possible, and actually I would use obj/com but that is me.

edit, before obj/com in beta, I used excel with send. Macros help quite a bit, but that might be disabled in excel as well.  Keyboard shortcuts can really cut down on errors, and many excel tasks can be autoit scripted very reliably.

I am not sure when you say database that you are refering to excel, it sounds more like an access or other database (forms, reports, etc.)  If you need help pulling information from those it gets a bit more tricky depending on access rights.

<{POST_SNAPBACK}>

Thank you very much. Now I understand - I have to activate "Go To" in Excel and proceed from there with copy to read and with send to write

Just to clarify on Database - it's a derivative of Cache and tables are encrypted.

What advantage would I get from controlsend?

Link to comment
Share on other sites

Unlike "Send" which sends information to the active window.

"control send" sends information to a specific control/window

from help

ControlSend works in a similar way to Send but it can send key strokes directly to a window, rather than just to the active window

hope it helps

8)

NEWHeader1.png

Link to comment
Share on other sites

Unlike "Send" which sends information to the active window.

"control send" sends information to a specific control/window

from help

ControlSend works in a similar way to Send but it can send key strokes directly to a window, rather than just to the active window

hope it helps

8)

<{POST_SNAPBACK}>

Thanks a lot. I tried the script - it will do the job.

And thanks for explaining controlsend - it's definitely the way to go.

Hoping one day to see how COM works.

Link to comment
Share on other sites

You can also send a range name:

send("sales"); range

This allows you to vary the size of your input range without changing your AutoIT script. You only have to adjust the range of "sales" in Excel and AutoIT will get the right data.

...by the way, it's pronounced: "JIF"... Bob Berry --- inventor of the GIF format
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...