Simple walk through
(1) User runs session calls my device (PCEXCEL) running new ttstpconv called tuddcconv1
(2) tuddcconv1 looks up in table tuddc905 report name like rtisfc960001000, finds download location with filename(C:\Program Files\Baan\tmp\perfdetails.txt), macro location(m:\data\excel\baseline\) and macro name(tisfc9600m000d.xls).
(3) tuddcconv1 runs ttstpconv with ascii to temp file.
(4) tuddcconv1 runs server2client to download file. (Creates C:\Program Files\Baan\tmp\perfdetails.txt)
(5) tuddcconv1 runs excel with macro file name.(m:\data\excel\baseline\tisfc9600m000d.xls
(6) Each macro has something like this in it - we keep macros in a common directory on a file server.
' Baseline Macro
' Macro recorded 3/2/2001
'Step 1 Convert text to columns
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.fileexists("C:\Program Files\Baan\tmp\perfdetails.txt") Then
Workbooks.OpenText FileName:="C:\Program Files\Baan\tmp\perfdetails.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 2), Array(9, 1), Array(10, 1))
Sheets(1).Name = "Details"
' Save as for the sub-totals
ChDir ("C:\Program Files\Baan\tmp")
FileSaveName = Application.GetSaveAsFilename(initialfilename:="perfdetails.xls", _
filefilter:="Microsoft Excel Workbook (*.xls),*.xls")
' Step 7 Do save as to allow user to save worksheet
ActiveWorkbook.SaveAs FileName:=FileSaveName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
At no point do I write anything to the macro spreadsheet. Make sure your macro actually works on the imported text file rather than in the macro spreadsheet. As a matter of fact our macros are usually saved as RO so the users can not hose them up. Also note I left out all the crap between opening and saving the spreadsheet. It sounds like your macro is actually the piece that is not working. Also forgot that we actually have another table that lets us run excel from various locations for each site and it also allows us to have mutliple macros for the same report - different formats for different users at different sites.