Sign in to follow this  
Followers 0
is8591

Help with Excel

11 posts in this topic

Please HELP

I checked the forums and everyone is using Beta version to work with Excel.

Does anyone knows how to do it with standard 3.1.1 that doesn't do object commands?

Share this post


Link to post
Share on other sites



What is your question? I'm using spreadsheet data in Excel files in my AU 3.1.1. scripts. I'm not using AU to control Excel.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

if you know how to navigate & edit in Excel then you will need to look up "Send" in the help file, this should do what you want.

post back with some code if you need anymore help

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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.

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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

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