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