NickHK wrote:
What do you mean by "i need to add to files" ?
Are you trying to insert a worksheet into the open workbook ?
Or just open another WB ?

Is this an add-in ? As it has a name of psXLAFName.

Also, you do not need the () in
.Run ("FormatReportScript")

Also, it may depend on how/what "FormatReportScript" does.

But as Charles says, it is easier to give yourself some object variables to
work with, otherwise statements like ".ActiveWorkbook.Save" are not obvious
as to which WB is being affected.

NickHK

wrote in message
ups.com...

Charles Chickering wrote:
I don't have vb6 so I guessing a little bit here. I would guess that
because
the excel object is not visible there is no "ActiveWorkbook" try dimming
a
workbook object then set that to your open statement:

Dim ExcelWorkbook As Excel.Workbook 'Not sure of exact syntax
Set ExcelWorkbook = ExcelObj.Workbooks.Open 'fill in rest from your code
'Do formatting
ExcelWorkbook.Save

Let me know if that works.
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

Didn't work...i need to add to files..the original excel file
(psExcelFName) and the vba script(psXLAFName) and you can't add 2 files
to a single workbook only to a workbookS object...so i tried below and
still didn't work. Other ideas?

With ExcelObj
.DisplayAlerts = False
.Visible = False
.Workbooks.Open FileName:=psExcelFName, ReadOnly:=False,
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
.WorkBooks(1).Save
.WorkBooks(1).Close
.Quit
End With


I meant I need to add two files(the initial open of the spreadsheet and the xla file)..sorry. I suspect part of my problem is that I don't fully understand the difference in a macro and an addin. I've seen 'addin' examples of code( and tried them) but with no luck. Again, what I'm trying to do is add some vba script(see below) to an existing excel spredsheet and save the results. The vba script simply changes font settings, column sizes etc. When the code hits the 'run' statement, the excel spreadsheet flashes on the screen(formatted correctely) and then disappears, but when I open it up with excel by double-clicking...the formatting is gone and the vba script is not saved with the spreadsheet.

Public Function CSSReportFormat()
Dim oSheet As Object
Dim oRange As Range
Dim i As Long
Dim StartRow As Long
Dim nReports As Long


On Error Resume Next

'make the worksheet invisible
Excel.Application.Visible = False

'get a reference to the activeworksheet
Set oSheet = Excel.ActiveWorkbook.Sheets(1)

'resize all the fonts
oSheet.Cells.Select
Excel.Selection.Font.Name = "Courier New"
Excel.Selection.Font.Size = 8

'resize the columns
oSheet.Columns(1).ColumnWidth = 13.86
oSheet.Columns(2).ColumnWidth = 11.71

'apply special stuff to the header row
oSheet.Range(oSheet.Cells(2, 5), oSheet.Cells(6, 5)).Select
Excel.Selection.Font.Bold = True
oSheet.Rows(8).Select
Excel.Selection.Font.Bold = True
oSheet.Cells(8, 3).Select
Excel.Selection.WrapText = True

'oSheet.Rows(1).Select
oSheet.Range("A8:L8").Borders(xlEdgeTop).Weight = xlThick
oSheet.Range("A8:L8").Borders(xlEdgeBottom).Weight = xlThick

oSheet.Cells(1, 1).Select

Excel.Application.Visible = True

End Function