Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

objExcel.ActiveWorkbook.SaveAs

1,131 views
Skip to first unread message

SAC

unread,
Jan 11, 2010, 12:42:19 PM1/11/10
to
I have a file called HR.xls, but it is a html file type (eventhough it has
an xls extention). I want to save it as an xls file type.

How do I do this and where can I find the properties for this method? I've
looked for it in Technet but haven't found it.

Thanks for your help. I really appreciate it.

mr_unreliable

unread,
Jan 11, 2010, 2:41:07 PM1/11/10
to
SAC wrote:
> I have a file called HR.xls, but it is a html file type (eventhough it
> has an xls extention). I want to save it as an xls file type.
>
> How do I do this and where can I find the properties for this method?
> I've looked for it in Technet but haven't found it.
>

hi SAC,

I suggest this:

- change the extension of your existing (html) file
to "HR.htm".

- open excel and then open the HR.htm file. If all goes
well, you should then see your spreadsheet displayed
in excel.

- then just save as: an xls file.

I don't have any code for you, but you can "roll-yer-own"
by using the XL macro recorder. The part about changing
the file extension you can do with the vbs fso object.
As for the rest, turn on your macro recorder, run through
the steps spelled out above, then close the macro recorder.
Look into your "Macros" area, to see what you recorded.
That will be "VBA" (visual basic for applications) code,
but you should be able to easily translate that into vbs
code. (There are a couple of caveats, but you can find
them in the archives of this ng).

cheers, jw
____________________________________________________________

You got questions? WE GOT ANSWERS!!! ..(but, no guarantee
the answers will be applicable to the questions)

ekrengel

unread,
Jan 11, 2010, 7:16:22 PM1/11/10
to

If you want to do it with vbscript...how about this? You will have to
edit the path to your HR.xls file:

Const Excel2007 = 12

sXLS = "C:\HR.xls"

On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
On Error GoTo 0
WScript.Echo "Excel Application not found!"
WScript.Quit
End If
On Error GoTo 0

With objExcel
.Application.DisplayAlerts = False
.Visible = True
.Workbooks.Open(sXLS)
appVerInt = split(.Version, ".")(0)
If appVerInt-Excel2007 >=0 Then
.ActiveWorkbook.SaveAs(sXLS), 56 'office 2007
Else
.ActiveWorkbook.SaveAs(sXLS), 43 'office 2003
End If
End With

objExcel.Quit
Set objExcel = Nothing

msgbox "Done!"
WScript.Quit

SAC

unread,
Jan 11, 2010, 8:31:40 PM1/11/10
to
I need to run this unattended within a stored procedure on a sql server so I
can't have the messages, etc.

Here's what I have now, but it didn't change the file...

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Delete Backup File first
objFSO.DeleteFile("\\Vadenmclp01\groups\Vangent
Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop1.xls")

'Copy to a Backup File
'objFSO.MoveFile "\\Vadenmclp01\groups\Vangent
Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls" ,
"\\Vadenmclp01\groups\Vangent Facilities\FMDesktop\Peoplesoft HR
Data\PS_FMDesktop1.xls"

objFSO.CopyFile "\\Vadenmclp01\groups\Vangent
Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls" ,
"\\Vadenmclp01\groups\Vangent Facilities\FMDesktop\Peoplesoft HR
Data\PS_FMDesktop1.xls", OverwriteExisting

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("\\Vadenmclp01\groups\Vangent
Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls")

objExcel.Application.Visible = False

objExcel.Application.DisplayAlerts = False

'objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls", Fileformat=43
'It's a 2003 file .......... Fileformat=xlNormal
objExcel.ActiveWorkbook.Close

objExcel.Quit
Set objExcel = Nothing


Any more ideas?

Also, where can I find the

"ekrengel" <erickr...@gmail.com> wrote in message
news:d0917eda-f500-4e57...@p24g2000yqm.googlegroups.com...

Const Excel2007 = 12

sXLS = "C:\HR.xls"

msgbox "Done!"
WScript.Quit


Thank you for your help.

ekrengel

unread,
Jan 12, 2010, 8:06:36 AM1/12/10
to


Your not actually saving anything, you have the SaveAS line commented
out. If your using excel 2003, the SaveAS code is 43. If it's 2007,
then it's 56. Just change this line:

'objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls",
Fileformat=43
'It's a 2003 file .......... Fileformat=xlNormal

WITH THIS:

objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls", 43

SAC

unread,
Jan 12, 2010, 8:53:00 AM1/12/10
to
Perfect! Guess it would help if I didn't have it commented out!! :-)

Also, I searched Tecnet for the 43 number as the file format. Where can I
find different options for different commands in vbscript?

Thanks so much for your help!!!


"ekrengel" <erickr...@gmail.com> wrote in message

news:75239645-08a6-494d...@34g2000yqp.googlegroups.com...

SAC

unread,
Jan 12, 2010, 9:05:43 AM1/12/10
to
There is another file type that I think I need.

I'm using MS Excel 2003 and when I do a file save as with it, file type 43
shows it as:

"Microsoft Excel 97 - Excel 2003....."

I need the one that just reads "Microsoft Office Excel Workbook"

Which number would that one be?

Thanks again!

"ekrengel" <erickr...@gmail.com> wrote in message

news:75239645-08a6-494d...@34g2000yqp.googlegroups.com...

ekrengel

unread,
Jan 12, 2010, 9:36:22 AM1/12/10
to
On Jan 12, 9:05 am, "SAC" <s...@somewhere.com> wrote:
> There is another file type that I think I need.
>
> I'm using MS Excel 2003 and when I do a file save as with it, file type 43
> shows it as:
>
> "Microsoft Excel 97 - Excel 2003....."
>
> I need the one that just reads "Microsoft Office Excel Workbook"
>
> Which number would that one be?
>
> Thanks again!
>
> "ekrengel" <erickreng...@gmail.com> wrote in message

A couple good references...

For Excel 2003:
http://blogs.technet.com/heyscriptingguy/archive/2006/03/07/how-can-i-save-an-excel-spreadsheet-and-then-save-a-copy-as-html.aspx
For Excel 2007-2010: http://www.rondebruin.nl/saveas.htm

Hope this helps...

SAC

unread,
Jan 12, 2010, 10:28:09 AM1/12/10
to
Do you know what is the number for saving it as a Microsoft Office Excel
Workbook? Or do you know where I can find the number for the parameter?
I'm using Excel 2003.

I don't know where to find this number. xlNormal does not work. Leaving no
parameter does not work.

Thanks.

"ekrengel" <erickr...@gmail.com> wrote in message

news:7bfc7d1b-9a02-4877...@w12g2000vbj.googlegroups.com...

SAC

unread,
Jan 12, 2010, 10:30:17 AM1/12/10
to
Oh, I see the list...thanks!!! I'll test some of these.

"SAC" <s...@somewhere.com> wrote in message
news:eFe7av5k...@TK2MSFTNGP02.phx.gbl...

zhen qin

unread,
Jul 19, 2011, 9:53:18 AM7/19/11
to
I can't create a xlsx format excel with VBS and keep getting error message 101590.

One more thing what is the 3D in your post

thanks

> On Monday, January 11, 2010 12:42 PM SAC wrote:

> I have a file called HR.xls, but it is a html file type (eventhough it has
> an xls extention). I want to save it as an xls file type.
>

> How do I do this and where can I find the properties for this method? I have
> looked for it in Technet but have not found it.


>
> Thanks for your help. I really appreciate it.


>> On Monday, January 11, 2010 2:41 PM mr_unreliable wrote:

>> SAC wrote:
>>
>> hi SAC,
>>
>> I suggest this:
>>
>> - change the extension of your existing (html) file
>> to "HR.htm".
>>
>> - open excel and then open the HR.htm file. If all goes
>> well, you should then see your spreadsheet displayed
>> in excel.
>>
>> - then just save as: an xls file.
>>

>> I do not have any code for you, but you can "roll-yer-own"


>> by using the XL macro recorder. The part about changing
>> the file extension you can do with the vbs fso object.
>> As for the rest, turn on your macro recorder, run through
>> the steps spelled out above, then close the macro recorder.
>> Look into your "Macros" area, to see what you recorded.
>> That will be "VBA" (visual basic for applications) code,
>> but you should be able to easily translate that into vbs
>> code. (There are a couple of caveats, but you can find
>> them in the archives of this ng).
>>
>> cheers, jw
>> ____________________________________________________________
>>
>> You got questions? WE GOT ANSWERS!!! ..(but, no guarantee
>> the answers will be applicable to the questions)


>>> On Monday, January 11, 2010 8:31 PM SAC wrote:

>>> I need to run this unattended within a stored procedure on a sql server so I

>>> cannot have the messages, etc.
>>>
>>> Here is what I have now, but it did not change the file...


>>>
>>> Set objFSO = CreateObject("Scripting.FileSystemObject")
>>>
>>> 'Delete Backup File first
>>> objFSO.DeleteFile("\\Vadenmclp01\groups\Vangent
>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop1.xls")
>>>
>>> 'Copy to a Backup File
>>> 'objFSO.MoveFile "\\Vadenmclp01\groups\Vangent
>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls" ,
>>> "\\Vadenmclp01\groups\Vangent Facilities\FMDesktop\Peoplesoft HR
>>> Data\PS_FMDesktop1.xls"
>>>
>>> objFSO.CopyFile "\\Vadenmclp01\groups\Vangent
>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls" ,
>>> "\\Vadenmclp01\groups\Vangent Facilities\FMDesktop\Peoplesoft HR
>>> Data\PS_FMDesktop1.xls", OverwriteExisting
>>>

>>> Set objExcel = CreateObject("Excel.Application")

>>> Set objWorkbook = objExcel.Workbooks.Open("\\Vadenmclp01\groups\Vangent
>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls")
>>>
>>> objExcel.Application.Visible = False
>>>
>>> objExcel.Application.DisplayAlerts = False
>>>
>>> 'objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls", Fileformat=43

>>> 'it is a 2003 file .......... Fileformat=xlNormal
>>> objExcel.ActiveWorkbook.Close
>>>

>>> objExcel.Quit
>>> Set objExcel = Nothing
>>>
>>>

>>> Any more ideas?
>>>
>>> Also, where can I find the

>>> Thank you for your help.


>>>> On Tuesday, January 12, 2010 8:53 AM SAC wrote:

>>>> Perfect! Guess it would help if I did not have it commented out!! :-)


>>>>
>>>> Also, I searched Tecnet for the 43 number as the file format. Where can I
>>>> find different options for different commands in vbscript?
>>>>
>>>> Thanks so much for your help!!!
>>>>
>>>>
>>>>
>>>>

>>>> Your not actually saving anything, you have the SaveAS line commented

>>>> out. If your using excel 2003, the SaveAS code is 43. If it is 2007,
>>>> then it is 56. Just change this line:


>>>>
>>>> 'objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
>>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls",
>>>> Fileformat=43

>>>> 'it is a 2003 file .......... Fileformat=xlNormal


>>>>
>>>> WITH THIS:
>>>>
>>>> objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
>>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls", 43


>>>>> On Tuesday, January 12, 2010 9:05 AM SAC wrote:

>>>>> There is another file type that I think I need.
>>>>>

>>>>> I am using MS Excel 2003 and when I do a file save as with it, file type 43


>>>>> shows it as:
>>>>>
>>>>> "Microsoft Excel 97 - Excel 2003....."
>>>>>
>>>>> I need the one that just reads "Microsoft Office Excel Workbook"
>>>>>
>>>>> Which number would that one be?
>>>>>
>>>>> Thanks again!
>>>>>
>>>>>
>>>>>

>>>>> Your not actually saving anything, you have the SaveAS line commented

>>>>> out. If your using excel 2003, the SaveAS code is 43. If it is 2007,
>>>>> then it is 56. Just change this line:


>>>>>
>>>>> 'objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
>>>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls",
>>>>> Fileformat=43

>>>>> 'it is a 2003 file .......... Fileformat=xlNormal


>>>>>
>>>>> WITH THIS:
>>>>>
>>>>> objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
>>>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls", 43


>>>>>> On Tuesday, January 12, 2010 10:28 AM SAC wrote:

>>>>>> Do you know what is the number for saving it as a Microsoft Office Excel
>>>>>> Workbook? Or do you know where I can find the number for the parameter?

>>>>>> I am using Excel 2003.
>>>>>>
>>>>>> I do not know where to find this number. xlNormal does not work. Leaving no


>>>>>> parameter does not work.
>>>>>>
>>>>>> Thanks.
>>>>>>
>>>>>>

>>>>>> A couple good references...
>>>>>>
>>>>>> For Excel 2003:
>>>>>> http://blogs.technet.com/heyscriptingguy/archive/2006/03/07/how-can-i-save-an-excel-spreadsheet-and-then-save-a-copy-as-html.aspx
>>>>>> For Excel 2007-2010: http://www.rondebruin.nl/saveas.htm
>>>>>>
>>>>>> Hope this helps...


>>>>>>> On Tuesday, January 12, 2010 10:30 AM SAC wrote:

>>>>>>> Oh, I see the list...thanks!!! I will test some of these.


>>>>>>>> On Tuesday, January 12, 2010 3:41 PM ekrengel wrote:

>>>>>>>> s
>>>>>>>> I have


>>>>>>>>
>>>>>>>> If you want to do it with vbscript...how about this? You will have to
>>>>>>>> edit the path to your HR.xls file:
>>>>>>>>

>>>>>>>> Const Excel2007 =3D 12
>>>>>>>>
>>>>>>>> sXLS =3D "C:\HR.xls"
>>>>>>>>
>>>>>>>> On Error Resume Next
>>>>>>>> Set objExcel =3D CreateObject("Excel.Application")


>>>>>>>> If (Err.Number <> 0) Then
>>>>>>>> On Error GoTo 0
>>>>>>>> WScript.Echo "Excel Application not found!"
>>>>>>>> WScript.Quit
>>>>>>>> End If
>>>>>>>> On Error GoTo 0
>>>>>>>>
>>>>>>>> With objExcel

>>>>>>>> .Application.DisplayAlerts =3D False
>>>>>>>> .Visible =3D True
>>>>>>>> .Workbooks.Open(sXLS)
>>>>>>>> appVerInt =3D split(.Version, ".")(0)
>>>>>>>> If appVerInt-Excel2007 >=3D0 Then


>>>>>>>> .ActiveWorkbook.SaveAs(sXLS), 56 'office 2007
>>>>>>>> Else
>>>>>>>> .ActiveWorkbook.SaveAs(sXLS), 43 'office 2003
>>>>>>>> End If
>>>>>>>> End With
>>>>>>>>
>>>>>>>> objExcel.Quit

>>>>>>>> Set objExcel =3D Nothing
>>>>>>>>
>>>>>>>> msgbox "Done!"
>>>>>>>> WScript.Quit


>>>>>>>>> On Tuesday, January 12, 2010 3:41 PM ekrengel wrote:

>>>>>>>>> o I
>>>>>>>>> 3


>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Your not actually saving anything, you have the SaveAS line commented

>>>>>>>>> out. If your using excel 2003, the SaveAS code is 43. If it is 2007,
>>>>>>>>> then it is 56. Just change this line:


>>>>>>>>>
>>>>>>>>> 'objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
>>>>>>>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls",

>>>>>>>>> Fileformat=3D43
>>>>>>>>> 'it is a 2003 file .......... Fileformat=3DxlNormal


>>>>>>>>>
>>>>>>>>> WITH THIS:
>>>>>>>>>
>>>>>>>>> objExcel.ActiveWorkbook.SaveAS "\\Vadenmclp01\groups\Vangent
>>>>>>>>> Facilities\FMDesktop\Peoplesoft HR Data\PS_FMDesktop.xls", 43


>>>>>>>>>> On Tuesday, January 12, 2010 3:41 PM ekrengel wrote:

>>>>>>>>>> 3
>>>>>>>>>> so
>>>>>>>>>> nt
>>>>>>>>>> =3D43


>>>>>>>>>>
>>>>>>>>>> A couple good references...
>>>>>>>>>>
>>>>>>>>>> For Excel 2003:

>>>>>>>>>> http://blogs.technet.com/heyscriptingguy/archive/2006/03/07/how-can-i-save-=

Bob Barrows

unread,
Jul 20, 2011, 11:23:21 AM7/20/11
to
zhen qin wrote:
> I can't create a xlsx format excel with VBS and keep getting error
> message 101590.
>
We're not looking over your shoulder at your computer monitor. You need to
show us the relevant section of your code, indicate which line is throwing
the error, and tell us what the error message is instead of just giving us a
number to look up somewhere.

>
<snip>


Reventlov

unread,
Jul 20, 2011, 4:09:15 PM7/20/11
to
Il giorno Tue, 19 Jul 2011 13:53:18 GMT, zhen qin <zhen...@yahoo.com> ha scritto:

>I can't create a xlsx format excel with VBS and keep getting error message 101590.
>One more thing what is the 3D in your post
>thanks

Some characters are not correctly shown in some newsreaders.
=3D should be an equal sign: =

--
Giovanni Cenati (Bergamo, Italy)
Write to "Reventlov" at katamail com
http://digilander.libero.it/Cenati (Esempi e programmi in VbScript)
--

0 new messages