Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

HTML in Excel

Status
Not open for further replies.
JimmyFo

JimmyFo

Programmer
Feb 14, 2005
102
US
Hi, I am trying to display an HTML table in an excel cell. I am using VBscript to write the table to the Excel file - it looks something like this:


Code: 
Set objWorksheet = objWorkbook.Sheets.Add
objExcel.activeworkbook.ActiveSheet.name = "Chart for Project 1"
objWorksheet.Columns.AutoFit
objWorksheet.Cells(2,1) = "<html><head></head><body><table><tr><td>1</td></tr></table></body></html>"
However, it does not work - it simply shows the HTML - isn't there a way to have the table generated by Excel?

Thanks,
James
 
Sort by date Sort by votes
Just populate your data in the cells and use scripting to set the borders.

It took me forever to come up with a good example so I will share.

Code: 
'==========================================================================
'
' NAME: FormatExcelBorders.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.thespidersparlor.com[/URL]
' DATE  : 06/04/2007
' COPYRIGHT (c) 2007 All Rights Reserved
'
' COMMENT: 
'
'    THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
'    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED To
'    THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'    PARTICULAR PURPOSE.
'
'    IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS 
'    BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
'    DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
'    WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
'    ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
'    OF THIS CODE OR INFORMATION.
'
'==========================================================================
' Declare Excel Constants
Const xlAscending = 1
Const xlTopToBottom = 1
Const xlThin = 3
Const xlAutomatic = -4105
Const xlContinuous = 1
Const xlCenter = -4108
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeLeft = 7
Const xlEdgeTop = 8
Const xlEdgeBottom = 9
Const xlEdgeRight = 10
Const xlInsideVertical = 11
Const xlInsideHorizontal = 12
Const xlThemeColorTeal = 9
Const xlThemeColorLight1 = 3

'Excel Colors
'White = 1
'Black = 2
'Gray = 3
'Dark Blue = 4
'Light Blue = 5
'Maroon = 6
'Olive = 7
'Light Purple = 8
'Teal = 9
'Orange = 10
'Navy Blue = 11
'Dark Purple = 12

    
    Set xl = CreateObject("Excel.Application")
    Set objWorkbook = xl.Workbooks.Add
    xl.Visible = true
    xl.Cells(1,1).Value = "Header"
    xl.Cells(1,2).Value = "Header 2"
    xl.Cells(1,3).Value = "Header3"
    xl.Cells(2,1).Value = "Data 1"
    xl.Cells(2,2).Value = "Data 2"
    xl.Cells(2,3).Value = "Data 3"
    xl.ActiveSheet.Range("A1:C1").Select
    xl.Selection.Font.Bold = True
    With xl.Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorTeal
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    With xl.Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    
    xl.ActiveSheet.Range("A1:C2").Select

	With xl.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With xl.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With xl.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With xl.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    With xl.Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With xl.Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
Thanks for the reply, Mark - I had found some things like this online, but because I am outputing a formatted GANTT chart (done with tables), I would need to run the logic all over again in VBScript to do so. I know if you have an HTML file named as an XLS file it reads it and displays it, same with Response.Write types if you say it is XLS - but for some reason, assigning it to a cell just shows the HTML.

Thanks for your help,
James
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Brianfree
  • Locked
  • Question
Excel Rows to Column 1
Replies
2
Views
341
Brianfree
Brianfree
Katto
  • Locked
  • Question
Excel VBA functions in VBScript
Replies
4
Views
330
dhookom
dhookom
bdwilcox
  • Locked
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
1K
SoftwareRT
SoftwareRT
asel
  • Locked
  • Question
Copy cell data from excel and paste into portable software application
Replies
0
Views
174
asel
asel
Katto
  • Locked
  • Question
Get the last filled line in excel
Replies
7
Views
483
Katto
Katto

Part and Inventory Search

Sponsor

Back
Top