Rework script from Excel 2003 to Excel 2010

[clearlinkit]

Hello everyone, I appreciate any feedback you may have for me. I am new to AutoIT, in fact I am learning about it because of the project I'm asking about now. The scripts that I'm working to get online were put in place by a prior admin and work fine on Excel 2003. However we are in process up grading them to Excel 2010 and need to have these updated to work with the new software.

The script is basically concatenating multiple excel spreadsheets into a single sheet that is published as a web page to their site.

The uses will open three or more spreadsheets in the order she wants them published in and then run the autoit script to copy the contents of those into a single, newly created html spreadsheet.

WinActivate("Microsoft Excel", "")
$outputfile = "W:\pastsales.html"
FileDelete ($outputfile)
AutoItSetOption ( "SendKeyDelay", 20 )
If WinActive("Microsoft Excel") Then
While WinGetTitle("Microsoft Excel") <> "Microsoft Excel"
  WinActivate("Microsoft Excel", "")
  Send ("^a!fg!e")
  Send ("!n{DELETE}")
  ClipPut ($outputfile)
  Send ("^v!p")
  WinWaitActive ("Publish as Web Page")
  Send ("!p")
  Sleep(2000)
  Send ("!a")
  Sleep(2000)
  Send ("^{F4}")
  WinWaitActive ("Microsoft Excel", "Do you want to save")
  Send ("!n")
;Ctrl-S Save Document
;Ctrl-A Select All
;Alt-F G Save as Web Page
;Alt-P Publish
;Alt-E Selection...
;Alt-N Filename
;Ctrl-V Paste Outputfile
;Alt-P Publish
;Alt-A Add to File
;Ctrl-F4 Close Document
  RunWait ("S:\Internet\Script\rebol.exe -cs S:\Internet\Script\redundant.r")
;  Sleep (100)   ; Pause .1 seconds
WEnd
EndIf

The redundant.r script looks like this

REBOL []
shrink: func [filename][
text: read/lines filename
last: copy ""
new-block: copy []
print ["Removing redundant lines..."]
while [not tail? text][if text/1 <> last [append new-block last: text/1] text: next text]
;delete filename
write/lines filename new-block
text: read filename
parse/all text [some [to "<tr height=0 style='display:none'>" cs: thru "</tr>" ce: :cs (remove/part cs ce)]]
parse/all text [some [to "^/" skip cs: any newline ce: :cs (remove/part cs ce)]]
write filename text
]

shrink %/s/internet/futuresales.html
shrink %/s/internet/currentsales.html
shrink %/s/internet/pastsales.html

I have been reading about some of the newer excel functions but I'm not seeing how to do exactly what I was expecting. Can anyone help me get moving forward on this.

Thanks!

[water]

I would try to stick with the builtin Excel functions. They are just wrappers for the Excel COM.

If no function does what you need use native methods.

[clearlinkit]

That really doesn't get me going at all. Do you have any more specific type of direction you can offer? What do you mean by stick with the builtin Excel funcitons? I don't see an excel function that will copy the page for example.

Can you give me an idea how you would go about copying the content of a spreadsheet and pasting it into another one for example?

[Reg2Post]

The example script that was posted looks to automate a lot of keystrokes. You may want to look into any new shortcut keys for Excel 2010 and modify the existing script accordingly. Also, look for any changes in windows titles and change those, as well.

The majority, if not all, of your keystroke automation can be replaced with code that is translated VBA. The built-in Excel UDF is a good starting point that has many functions that you can use for many comman Excel tasks. Unless someone is generous enough with their time, you will be better off posting what you have attempted so far and asking for any corrections or pointers to any issues you may encounter.

[water]

The Excel UDF offers some (basic) functions you could use in your script (open the Workbook, save it ...).

For the other functions I suggest to record what you need to do using the Excel macro function. This can then be translated to AutoIt.

Using Excel COM makes your scripts much more reliable compared to sending keystrokes.