Prev: Remote script with alternate credentials?
Next: Determining the last business date
From: SAC on 11 Jan 2010 12:42
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.

From: mr_unreliable on 11 Jan 2010 14:41
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)

From: ekrengel on 11 Jan 2010 19:16
On Jan 11, 12:42 pm, "SAC" <s...(a)somewhere.com> 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.
>
> Thanks for your help.  I really appreciate it.

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
From: SAC on 11 Jan 2010 20:31
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" <erickrengel5(a)gmail.com> wrote in message
news:d0917eda-f500-4e57-aff9-2a177b4c4204(a)p24g2000yqm.googlegroups.com...
On Jan 11, 12:42 pm, "SAC" <s...(a)somewhere.com> 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.
>
> Thanks for your help. I really appreciate it.

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


Thank you for your help.

From: ekrengel on 12 Jan 2010 08:06
On Jan 11, 8:31 pm, "SAC" <s...(a)somewhere.com> wrote:
> 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
>


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
 |  Next  |  Last
Pages: 1 2
Prev: Remote script with alternate credentials?
Next: Determining the last business date