activeworkbook.saveas - saving format changes
 

I have a WSH script that opens a csv file and loads it
into an excel spreadsheet. I then perform several
formatting functions on various columns and rows (autofit,
bold).

after the script has completed when I reopen the file in
excel I have lost all the formatting changes. If I step
through the code with the worksheet visible I see the
changes taking effect but not after I save it and then
open the newly created spreadsheet with Excel.

When I try the vb syntax of saveas FileName:= ...
FileFormat:=xlNormal WSH returns an error - I have
searched high and low for the correct syntax for WSH and
as a last chance hope the experts can help. Hereis
the .VBS script. Thanks, Tom


'******* main *************
set myExcel = CreateObject("EXCEL.Application")
Call createMpetReport
call formatMpetReport
call saveMpetReport

'***create and open the mpet report
Sub createMpetReport()
myExcel.Workbooks.Opentext "C:\Development\Automated
Reports\MPET_DATA.DAT",,,,,,,,,,true,"|"
end sub

'***Do some formatting
sub formatMpetReport
with myExcel
.Columns("B:B").Select
.Selection.Columns.AutoFit
.Columns("G:G").Select
.Selection.NumberFormat = "m/d/yy h:mm;@"
.Rows("1:2").Select
.Selection.Font.Bold = True
end with
end sub

'***save the mpet report
Sub saveMpetReport()
myExcel.visible = true
Set objWorkbook = myExcel.ActiveWorkbook
objWorkbook.SaveAs "C:\Development\MPET_DATA.xls"

activeworkbook.saveas - saving format changes
 

you need to use the value of xlWorkbookNormal

? xlWorkbookNormal
-4143

objWorkbook.SaveAs "C:\Development\MPET_DATA.xls", -4143

--
Regards,
Tom Ogilvy



"tk3" wrote in message
...
I have a WSH script that opens a csv file and loads it
into an excel spreadsheet. I then perform several
formatting functions on various columns and rows (autofit,
bold).

after the script has completed when I reopen the file in
excel I have lost all the formatting changes. If I step
through the code with the worksheet visible I see the
changes taking effect but not after I save it and then
open the newly created spreadsheet with Excel.

When I try the vb syntax of saveas FileName:= ...
FileFormat:=xlNormal WSH returns an error - I have
searched high and low for the correct syntax for WSH and
as a last chance hope the experts can help. Hereis
the .VBS script. Thanks, Tom


'******* main *************
set myExcel = CreateObject("EXCEL.Application")
Call createMpetReport
call formatMpetReport
call saveMpetReport

'***create and open the mpet report
Sub createMpetReport()
myExcel.Workbooks.Opentext "C:\Development\Automated
Reports\MPET_DATA.DAT",,,,,,,,,,true,"|"
end sub

'***Do some formatting
sub formatMpetReport
with myExcel
.Columns("B:B").Select
.Selection.Columns.AutoFit
.Columns("G:G").Select
.Selection.NumberFormat = "m/d/yy h:mm;@"
.Rows("1:2").Select
.Selection.Font.Bold = True
end with
end sub

'***save the mpet report
Sub saveMpetReport()
myExcel.visible = true
Set objWorkbook = myExcel.ActiveWorkbook
objWorkbook.SaveAs "C:\Development\MPET_DATA.xls"

activeworkbook.saveas - saving format changes
 

It Works - where did you find the syntax for this? -
Thanks for your help, Tom Kramer III
-----Original Message-----
you need to use the value of xlWorkbookNormal

? xlWorkbookNormal
-4143

objWorkbook.SaveAs "C:\Development\MPET_DATA.xls", -4143

--
Regards,
Tom Ogilvy



"tk3" wrote in
message
...
I have a WSH script that opens a csv file and loads it
into an excel spreadsheet. I then perform several
formatting functions on various columns and rows
(autofit,
bold).

after the script has completed when I reopen the file in
excel I have lost all the formatting changes. If I step
through the code with the worksheet visible I see the
changes taking effect but not after I save it and then
open the newly created spreadsheet with Excel.

When I try the vb syntax of saveas FileName:= ...
FileFormat:=xlNormal WSH returns an error - I have
searched high and low for the correct syntax for WSH and
as a last chance hope the experts can help. Hereis
the .VBS script. Thanks, Tom


'******* main *************
set myExcel = CreateObject("EXCEL.Application")
Call createMpetReport
call formatMpetReport
call saveMpetReport

'***create and open the mpet report
Sub createMpetReport()
myExcel.Workbooks.Opentext "C:\Development\Automated
Reports\MPET_DATA.DAT",,,,,,,,,,true,"|"
end sub

'***Do some formatting
sub formatMpetReport
with myExcel
.Columns("B:B").Select
.Selection.Columns.AutoFit
.Columns("G:G").Select
.Selection.NumberFormat = "m/d/yy h:mm;@"
.Rows("1:2").Select
.Selection.Font.Bold = True
end with
end sub

'***save the mpet report
Sub saveMpetReport()
myExcel.visible = true
Set objWorkbook = myExcel.ActiveWorkbook
objWorkbook.SaveAs "C:\Development\MPET_DATA.xls"





.

activeworkbook.saveas - saving format changes
 

You had the syntax. My only change was to use positional arguments rather
than named argument - that an using the value of the constant rather than
the constant name itself. I found the value for the constant in the
immediate window of Excel.

--
Regards,
Tom Ogilvy



"TK3" wrote in message
...
It Works - where did you find the syntax for this? -
Thanks for your help, Tom Kramer III
-----Original Message-----
you need to use the value of xlWorkbookNormal

? xlWorkbookNormal
-4143

objWorkbook.SaveAs "C:\Development\MPET_DATA.xls", -4143

--
Regards,
Tom Ogilvy



"tk3" wrote in
message
...
I have a WSH script that opens a csv file and loads it
into an excel spreadsheet. I then perform several
formatting functions on various columns and rows
(autofit,
bold).

after the script has completed when I reopen the file in
excel I have lost all the formatting changes. If I step
through the code with the worksheet visible I see the
changes taking effect but not after I save it and then
open the newly created spreadsheet with Excel.

When I try the vb syntax of saveas FileName:= ...
FileFormat:=xlNormal WSH returns an error - I have
searched high and low for the correct syntax for WSH and
as a last chance hope the experts can help. Hereis
the .VBS script. Thanks, Tom


'******* main *************
set myExcel = CreateObject("EXCEL.Application")
Call createMpetReport
call formatMpetReport
call saveMpetReport

'***create and open the mpet report
Sub createMpetReport()
myExcel.Workbooks.Opentext "C:\Development\Automated
Reports\MPET_DATA.DAT",,,,,,,,,,true,"|"
end sub

'***Do some formatting
sub formatMpetReport
with myExcel
.Columns("B:B").Select
.Selection.Columns.AutoFit
.Columns("G:G").Select
.Selection.NumberFormat = "m/d/yy h:mm;@"
.Rows("1:2").Select
.Selection.Font.Bold = True
end with
end sub

'***save the mpet report
Sub saveMpetReport()
myExcel.visible = true
Set objWorkbook = myExcel.ActiveWorkbook
objWorkbook.SaveAs "C:\Development\MPET_DATA.xls"





.