Solved

export HTML table to excel

Posted on 2002-05-01
6
477 Views
Last Modified: 2007-11-27
Hi there,
 I need to export a dynamically created table in HTML to a MS Excel file. This is a clientside application,
and I can only use VBscript or javascript. It does NOT have a server! so I cannot use asp and jsp technologies.
I need a way to export whatever you see on the screen into a excel or CSV file. Any ideas??
0
Comment
Question by:sandloper
6 Comments
 
LVL 3

Expert Comment

by:n_narayanan
ID: 6985043
0
 
LVL 3

Expert Comment

by:n_narayanan
ID: 6985071
Sorry the above is for EXCEL TO HTML

The below is the Japanese Site which does HTML2EXCELL

http://hp.vector.co.jp/authors/VA014833/Excel/HTML2XL.html

Narayanan
0
 
LVL 3

Accepted Solution

by:
n_narayanan earned 200 total points
ID: 6985073
I am appending the code taken from the above site,

Option Explicit

'*********************************************************************
' HTML2Excel/Directory2Excel Ver 2.0
'
' HTML L TABLE p Excel L[NV[gIG]]\7
' HTML2Excel M HTML6I
\\ji TABLE L`epG]]\7
' Directory2Excel MfB Ng
LS HTML6pN[I5\7
'
'Designed for HPCF http://www.hpcf.com/
'by poetlabo http://www.bekkoame.ne.jp/%7Epoetlabo/
'also available at http://www.vector.co.jp/authors/VA014833/
'Copyleft:1999, p 6 poetlabo@cap.bekkoame.ne.jp
'lRL       BH-      |OEpEzz7i1Fp
= }5\7
'*********************************************************************

Sub HTML2Excel2()
    Dim file_name As String
   
    file_name = Application.GetOpenFilename("HTML6 (*.htm),*.htm")
    If file_name = "False" Then Exit Sub
   
    Call GetTABLE(file_name)
   
    Application.StatusBar = "HTML^Opm5D"\7B"
    Call ReplaceTags("<*>", "")  '// HTML^Om
    Call ReplaceTags("&nbsp;", "")   '// s6m
    Call ReplaceTags("&amp;", "&")     '// & L
    Call ReplaceTags("&lt;", "<")     '// ,Hh
    Call ReplaceTags("&gt;", ">")     '// eHh
    Call ReplaceTags(Chr(9), "")   '// ^u6m
    Application.StatusBar = False
    Beep
    MsgBox file_name & "L`epG]]\5=B", 0, "HTML2Excel"
End Sub

Sub GetTABLE(file_name As String)
    Const TABLEstag As String = "<TABLE"
    Const TABLEetag As String = "</TABLE>"
    Const tagc As String = ">"
    Dim FileNum As Integer
    Dim TRn As String, TextLine As String
    Dim TABLEstart As Integer, TABLEend As Integer

    Application.StatusBar = "HTMLt@C p
J"D"\7B"

    FileNum = FreeFile()
    Open file_name For Input Access Read As #FileNum
   
    On Error GoTo CloseFile

    Do Until EOF(FileNum)
        Line Input #FileNum, TextLine
        TRn = TRn & TextLine
    Loop
   
    TABLEstart = 1
    TABLEstart = InStr(TABLEstart, TRn, TABLEstag, 1)
    If TABLEstart = 0 Then
        Application.StatusBar = False
        MsgBox Title:="E+\9qE5=B", _
            prompt:=file_name & " :" & Chr(13) & Chr(10) & _
            "1Lt@C IMA\L`e* )=h\9qB"
        Exit Sub
    End If
    Application.StatusBar = "TABLEL`epG]qE"\7B*Imi\E(R?->3"B"
    Do
        TABLEstart = InStr(TABLEstart, TRn, tagc, 1) + 1
        TABLEend = InStr(TABLEstart, TRn, TABLEetag, 1)
        TextLine = Mid(TRn, TABLEstart, TABLEend - TABLEstart)
        Call GetTRs(TextLine)
        TABLEstart = TABLEend + 8
        TABLEstart = InStr(TABLEstart, TRn, TABLEstag, 1)
    Loop Until TABLEstart = 0

CloseFile:
    Application.StatusBar = False
    Close #FileNum
End Sub

Sub GetTRs(TRn As String)
    Const TRstag As String = "<TR", tagc As String = ">"
    Const Msg1 As String = "f ", Msg2 As String = " spG]qE"\7B"
    Dim TextLine As String
    Dim TRstart As Integer, TRend As Integer, TABLEend As Integer
    Dim rn As Integer
   
    TRstart = InStr(1, TRn, TRstag, 1)
    Do Until TRstart = 0
        TRstart = InStr(TRstart, TRn, tagc, 1) + 1
        TRend = InStr(TRstart, TRn, TRstag, 1)
        TextLine = Mid(TRn, TRstart, TRend - TRstart)
        rn = rn + 1
        Application.StatusBar = Msg1 & rn & Msg2
        Call GetTHsTDs(TextLine, rn)
        TRstart = TRend
    Loop
    TextLine = Mid(TRn, TRstart, Len(TRn) - TRstart)
    rn = rn + 1
    Application.StatusBar = Msg1 & rn & Msg2
    Call GetTHsTDs(TextLine, rn)
End Sub

Sub GetTHsTDs(TextLine As String, rn As Integer)
    Const tagc As String = ">"
    Dim Cstart As Integer, Cend As Integer
    Dim StrArray() As String, cn As Integer
   
    Cstart = GetTHTDstart(1, TextLine)
    Cstart = InStr(Cstart, TextLine, tagc, 1) + 1
    Cend = GetTHTDstart(Cstart, TextLine)
    Do While Cend > 0
        cn = cn + 1
        ReDim Preserve StrArray(1 To cn)
        StrArray(cn) = Mid(TextLine, Cstart, Cend - Cstart)
        Cstart = InStr(Cend, TextLine, tagc, 1) + 1
        Cend = GetTHTDstart(Cstart, TextLine)
    Loop
    cn = cn + 1
    ReDim Preserve StrArray(1 To cn)
    StrArray(cn) = Mid(TextLine, Cstart, Len(TextLine) - Cstart + 1)
    Range(Cells(rn, 1), Cells(rn, cn)).Value = StrArray()
End Sub

Function GetTHTDstart(Cstart As Integer, TextLine As String) As Integer
    Const THstag As String = "<TH", TDstag As String = "<TD", tagc As String = ">"
    Dim THstart As Integer, TDstart As Integer

    THstart = InStr(Cstart, TextLine, THstag, 1)
    TDstart = InStr(Cstart, TextLine, TDstag, 1)
    GetTHTDstart = Application.Min(THstart, TDstart)
    If GetTHTDstart = 0 Then GetTHTDstart = Application.Max(THstart, TDstart)
End Function


Cheers

Narayanan
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6986815
optionally, you could take a look at:
www.angelfire.com/realm/vb-shared/index.html under specific topic
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7698700
Hi sandloper,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: n_narayanan and Richie_Simonetti

sandloper, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 

Expert Comment

by:SpideyMod
ID: 7755546
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange

Richie_Simonetti points added to:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20463716.html
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now