Help with "SendKeys" in macro

[Charlie]

I have an unusual macro that I'm trying to get running. I put it in
Word VBA since I've used that a bit. What I'm trying to do is to
automate the process of opening a Works spreadsheet and from Works
saving it as an Excel file. This procedure works fine when the
commands are typed.... and according to the manual I have, they
"should" work when executed using the SendKeys commands from a VBA
macro. Here's the macro:

Sub Works_to_Excel()
Dim appWorks As Variant
Dim filename As String
filename = "caddo.wks"
'
' Open Works Spreadsheet & activate
'
appWorks = Shell("c:\program files\microsoft works\Wksss.exe",
vbNormalFocus)
AppActivate "Microsoft Works Spreadsheet"
'
' Send Works command to load file into Works
'
SendKeys "%FO" & filename & "{Enter}", True
'
' Change filename and send command to save as an Excel file
'
filename = "caddo.xls"
AppActivate "Microsoft Works Spreadsheet"
SendKeys "%FA" & filename & "{Tab}" & "E" & "{Enter}", True
'
End Sub

Although the long lines wrap when seen above, they are OK in the VBA
editor....

The maro runs and opens Works, but fails to load the spreadsheet. If
I split the macro into several small macros, and execute them
sequentially, it will open the spreadsheet and save it as an Excel
file (sometimes). Is there some timing factor involved, where I need a
long delay after sending the Works command, before sending another?

Any help or suggestions would really be appreciated.

 

[Cindy Meister -WordMVP-]

Hi Charlie,

SendKeys is simply not reliable - for the very reasons you're
experiencing. It depends on having everything in just the right
state when the commands are sent, and there's simply no way to do
that. A C++ programmer could probably manage it, since through that
programming environment one can get exact information about what's
on the screen and what has the focus. But through VBA I'd say it's
pretty much hopeless.

What I'm trying to do is to
automate the process of opening a Works spreadsheet and from Works
saving it as an Excel file. This procedure works fine when the
commands are typed.... and according to the manual I have, they
"should" work when executed using the SendKeys commands from a VBA
macro.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24
2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail