save workbook but doesn't really save

[mlafarlett]

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

 

[Guest]

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.

 

[mlafarlett]

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."


:

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?

 

[Guest]

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?

 

[NickHK]

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

 

[mlafarlett]

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

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

 

[mlafarlett]

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