You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » COM Scripting » Excel Scripting
Register User       Forum List       Calendar       FAQ
Page 1 of 2 12>
Topic Options
#154555 - 2006-01-04 02:51 PM Excel Scripting
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1895
Loc: Hilversum, The Netherlands
Hi, I searched all trough the forum and google'd it but I can't seem to find the code that let's you save an excel sheet as HTML.

Could anyone help me out on this one ?
I mean I'm only looking for the simple command to save as html, for instance:

Code:

Dim $ExcelSheet
$ExcelSheet = CreateObject("Excel.Sheet")
$ExcelSheet.Application.Visible = 0
$ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
$ExcelSheet.SaveAs("D:\TEST.XLS")

But it would be something like:
Code:

$ExcelSheet.SaveAsHTML("D:\TEST.HTML")
Top
#154556 - 2006-01-04 03:20 PM Re: Excel Scripting
Allen Administrator Online shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4549
Loc: USA
You must be close... I found this document on the Scripting Guy site, and it references Saving as a CSV. It appears its just a number that need to follow the path. I've dug around but have yet to find any reference to those constants.
Top
#154557 - 2006-01-04 03:39 PM Re: Excel Scripting
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
In this forum there is a post call "COM Resources" and in there is a link to the MS Excel constants ... heres a dump of those format values - dont see anything obvious to do with HTML ...

#define xlAddIn 18
#define xlCSV 6
#define xlCSVMac 22
#define xlCSVMSDOS 24
#define xlCSVWindows 23
#define xlDBF2 7
#define xlDBF3 8
#define xlDBF4 11
#define xlDIF 9
#define xlExcel2 16
#define xlExcel2FarEast 27
#define xlExcel3 29
#define xlExcel4 33
#define xlExcel5 39
#define xlExcel7 39
#define xlExcel9795 43
#define xlExcel4Workbook 35
#define xlIntlAddIn 26
#define xlIntlMacro 25
#define xlWorkbookNormal 0xffffefd1
#define xlSYLK 2
#define xlTemplate 17
#define xlCurrentPlatformText 0xffffefc2
#define xlTextMac 19
#define xlTextMSDOS 21
#define xlTextPrinter 36
#define xlTextWindows 20
#define xlWJ2WD1 14
#define xlWK1 5
#define xlWK1ALL 31
#define xlWK1FMT 30
#define xlWK3 15
#define xlWK4 38
#define xlWK3FM3 32
#define xlWKS 4
#define xlWorks2FarEast 28
#define xlWQ1 34
#define xlWJ3 40
#define xlWJ3FJ3 41

-Shawn
Top
#154558 - 2006-01-04 04:45 PM Re: Excel Scripting
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
you should not forget to .quit this thing somehow,
as after running this script there is an excel process left running...
_________________________



Top
#154559 - 2006-01-04 04:46 PM Re: Excel Scripting
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1895
Loc: Hilversum, The Netherlands
Indeed, I've read trough that dump and found nothing regarding HTML. Basically the option has nothing to do with excel itself. I belief it calls for a function that rewrites the excel layout into html layout and therefor cannot be used in vbs/kix. I was hoping someone would prove me wrong I'm still looking tho, if I find anything I will let you know.
Top
#154560 - 2006-01-04 04:48 PM Re: Excel Scripting
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1895
Loc: Hilversum, The Netherlands
Yeah I know it should be quitted, I was merely showing a quick example to express the meaning of the SaveAs command.
Top
#154561 - 2006-01-04 05:05 PM Re: Excel Scripting
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
wacky idea alert - create your spreadsheet with Excel COM, then open a WORD COM object, transfer (or maybe open) your sheet data in WORD, then use Word's SaveAs HTML ?
Top
#154562 - 2006-01-04 05:09 PM Re: Excel Scripting
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1895
Loc: Hilversum, The Netherlands
Word's saveas HTML is exactly the same as Excel's version.
Also the dump misses "xlHtml" It does exist, microsoft mentions it here: http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

Only thing now is to find out the value of xlHtml and we're done
Top
#154563 - 2006-01-04 05:11 PM Re: Excel Scripting
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
so it is:

Const xlHtml = 44 (&H2C)

-Shawn
Top
#154564 - 2006-01-04 05:22 PM Re: Excel Scripting
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1895
Loc: Hilversum, The Netherlands
Perfect

To give a quick & nasty example that works:
Code:

Dim $ExcelSheet
$ExcelSheet = CreateObject("Excel.Sheet")
$ExcelSheet.Application.Visible = 0
$ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
$ExcelSheet.SaveAs("D:\TEST.html", 44)


Thx alot for your help Shawn and everyone who contributed
I love this forum, every time I needed something there is always someone around that actually helps
Thx again
Top
#154565 - 2006-01-04 05:50 PM Re: Excel Scripting
Allen Administrator Online shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4549
Loc: USA
So Shawn... oh wise one... how'd you figure it out?
Top
#154566 - 2006-01-04 06:11 PM Re: Excel Scripting
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
I smoked some devine cactus and it came to me in a vision.
Top
#154567 - 2006-01-04 06:17 PM Re: Excel Scripting
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
someone figured out something?
_________________________
!

download KiXnet

Top
#154568 - 2006-01-04 06:42 PM Re: Excel Scripting
Allen Administrator Online shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4549
Loc: USA
ah... the elusive Canadian Cactus... no wonder we couldn't figure it out.
Top
#154569 - 2006-01-04 06:48 PM Re: Excel Scripting
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
rofl, could have said I smoked some Canadian beaver but that aint a pretty picture. Seriously, I just fired-up Excel and smoked into Tools->Macro->Visual Basic Editor. Then flipped open the View->Object Browser and browsed the <globals> and found xlHtml = 44.
Top
#154570 - 2006-01-04 06:57 PM Re: Excel Scripting
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
hmm...
so, you admit you use excel?
woot.

I just browsed to msdn and vóila:
http://msdn.microsoft.com/library/defaul..._HV01049962.asp (check under XlFileFormat)
_________________________
!

download KiXnet

Top
#154571 - 2006-01-04 06:58 PM Re: Excel Scripting
Allen Administrator Online shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4549
Loc: USA
Pretty Slick. I didn't realize that was possible. Thanks.
Top
#154572 - 2006-01-04 07:02 PM Re: Excel Scripting
Allen Administrator Online shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4549
Loc: USA
Jooel... but if you notice... xlhtml is missing from that list... (at least I don't think its there)
Top
#154573 - 2006-01-04 07:13 PM Re: Excel Scripting
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
check again.
_________________________
!

download KiXnet

Top
#154574 - 2006-01-04 07:17 PM Re: Excel Scripting
Allen Administrator Online shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4549
Loc: USA
$#!^ ...

Quote:

(check under XlFileFormat)


got me on the edit...;)
Top
Page 1 of 2 12>


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 896 anonymous users online.
details
Newest Members
Timothy, Jojo67, MaikSimon, kvn317, kixtarts2025
17874 Registered Users
My Cookies · Mark all read Powered by UBB.threads™ Contact Us · KiXtart.org website · Top

Generated in 0.136 seconds in which 0.088 seconds were spent on a total of 12 queries. Zlib compression enabled.
Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org