Updating Impromptu Excel Format from 3.0/5.0

[drlex]

Having spent most of the day sorting out why PDA users couldn't open Impromptu reports saved in excel with format, I've been resigned to having to put the files back into Excel 2000 using SendKeys. Was disappointed to find that Impromptu V7 MR2 was still on this old format.
If anyone else has this problem, here's an extract from the macro I cobbled together:

Sub Main()
Dim objExcel as Object
Dim objReport as Object
Dim strFile as String
strFile = "k:\xl\salesdata for 2003-02-26.xls"
'change this to your file location!
Set objExcel = CreateObject("Excel.Application&quot
objExcel.Visible = 1
Set objReport = GetObject(strfile,)
' Now comes script to save with format
AppActivate "Microsoft Excel"
SendKeys "%W",1 'Unhide document
SendKeys "U",1
SendKeys "{ENTER}",1
SendKeys "%F",1 'Start save routine
SendKeys "A",1 'chooses save as
SendKeys "{TAB}",1
'Confirms document name as before
SendKeys "{TAB}",1 'move to save as type prompt
SendKeys "{UP 7}",1 'change to latest format
SendKeys "{ENTER}",1 'and select
SendKeys "{TAB}",1 'move to save button
SendKeys "{ENTER}",1 'and select
SendKeys "{TAB}",1 'switch to yes button
SendKeys "{ENTER}",1 'and confirm overwrite
SendKeys "%F",1 'close document
SendKeys "C",1 'by selecting close
SendKeys "%F",1 'Close Excel
SendKeys "X",1 'by exiting
Set objExcel = Nothing
Set objReport = Nothing
End Sub

Note that this converts 5.0 to 2000. If it's a 3.0, then add 4 to the UP value. It also overwrites the original filename, so if you're changing filenames, change the first {TAB} to the filename required and remove the last {TAB} and {ENTER} pair.