Should anyone else require their impromptu reports to be in the most upto date Excel format (perhaps to allow iPaq PDAs to open them with Windows CE version of Excel), here's how to use OLE commands rather than clunky SendKeys to open and resave the report. Took me ages to find the relevant file format, so hats off to Redmond logic!
'put in with your declarations
Dim strfileloc As String ' use for location of files
Dim strfilename As String ' use for filename
Dim objExcel as Object
'
'Open Excel and convert files to Excel 2000 format for iPaqs
Set objExcel = CreateObject("Excel.Application"
Set objExcelOpen = objExcel
objExcel.Visible = 1
strfilename = strfileloc + "report.xls"
objExcel.Application.Workbooks.Open strfilename
objExcel.Application.DisplayAlerts = False
objExcel.Application.Workbooks(1).SaveAs strfilename, Fileformat:=-4143
objExcel.Application.Workbooks(1).Close
objExcel.Application.Quit
Hope it saves one of you the day it took me to get around the problem (we work slowly here in the West). The DisplayAlerts = Flase is to turn off the overwrite warning, so could be left out if one is saving the file as a different name or to a different location.
lex
'put in with your declarations
Dim strfileloc As String ' use for location of files
Dim strfilename As String ' use for filename
Dim objExcel as Object
'
'Open Excel and convert files to Excel 2000 format for iPaqs
Set objExcel = CreateObject("Excel.Application"
Set objExcelOpen = objExcel
objExcel.Visible = 1
strfilename = strfileloc + "report.xls"
objExcel.Application.Workbooks.Open strfilename
objExcel.Application.DisplayAlerts = False
objExcel.Application.Workbooks(1).SaveAs strfilename, Fileformat:=-4143
objExcel.Application.Workbooks(1).Close
objExcel.Application.Quit
Hope it saves one of you the day it took me to get around the problem (we work slowly here in the West). The DisplayAlerts = Flase is to turn off the overwrite warning, so could be left out if one is saving the file as a different name or to a different location.
lex