I have a vb6 program where I merge vba script(that handles formatting)
with an existing excel file. If i turn 'visible' on and manually save
the excel file and re-open it, the formatting is correct. If I merge
the vba script with the excel file and save through code(visible =
false), the vba script is not saved as a part of the excel file thus my
formatting is incorrect. Any ideas?

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

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:

I have a vb6 program where I merge vba script(that handles formatting)
with an existing excel file. If i turn 'visible' on and manually save
the excel file and re-open it, the formatting is correct. If I merge
the vba script with the excel file and save through code(visible =
false), the vba script is not saved as a part of the excel file thus my
formatting is incorrect. Any ideas?

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

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

Ok, I've tried to simulate your problem by accessing Excel from Outlook.
Here's what I came up with for code:
Dim ExcelObj As Object
Dim ExcelWB As Object
Set ExcelObj = CreateObject("Excel.Application")

With ExcelObj
.DisplayAlerts = False
.Visible = False
Set ExcelWB = .Workbooks.Open _
FileName:=psExcelFName, ReadOnly:=False, _
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
ExcelWB.Save
ExcelWB.Close
.quit
End With


Does that work in VB?
--
Charles Chickering

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


" wrote:


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

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

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

I tried Charles method but with no luck...seems to work with no errors
but my results are still not saved.