Export Help

**Lafor** · Oct 14th, 2003, 11:30 AM

Hi!

I Created a report with Data environment... It is displayed on the screen. How can I dynamically export the results into an html file from within VB...

Thanks in advance for any help Or ideas...

**andreys** · Oct 14th, 2003, 12:59 PM

Here is an example how it could be done.

VB Code:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
 
Private Sub Command1_Click()
    CreateHTMLReport "SELECT * FROM Users", "D:\Databases\db.mdb", "C:\Output.html", "USERS LISTING", True
End Sub
 
Private Sub CreateHTMLReport(SQL As String, _
                            DBPath As String, _
                            ReportFile As String, _
                            ReportTitle As String, _
                            Optional OpenReport As Boolean = True)
                            
Dim rs, connect, fso, fso_file, fso_ts, line_color
 
    SQL = "SELECT * FROM Users" 'your SQL statement
    connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
    
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open SQL, connect
    
    If Not rs.EOF Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        fso.CreateTextFile (ReportFile)
        Set fso_file = fso.GetFile(ReportFile)
        Set fso_ts = fso_file.OpenAsTextStream(2)
        
        With fso_ts
            .write ("<HTML>")
            .write ("<HEAD>")
            .write ("<TITLE>" & ReportTitle & "</TITLE>")
            .write ("</HEAD>")
            .write ("<BODY>")
            .write ("<DIV ALIGN='center'>")
            .write ("<H2>" & ReportTitle & "</H2>")
            .write ("<SPAN STYLE='font-family:Tahoma;font-size:11px'>|&nbsp;<A HREF='javascript:window.print()'>PRINT REPORT</A>&nbsp;|&nbsp;<A HREF='javascript:window.close()'>CLOSE WINDOW</A>&nbsp;|</SPAN><BR><BR>")
            .write ("<TABLE BORDERCOLOR='#C0C0C0' WIDTH='95%' STYLE='border-collapse:collapse;font-family:Arial;font-size:12px' BORDER='1'>")
            .write ("<TR BGCOLOR='#000000'>")
            
            For i = 0 To rs.fields.Count - 1
                .write ("<TH NOWRAP STYLE='color:#FFFFFF' ALIGN='center'><B>&nbsp;" & rs(i).Name & "&nbsp;</B></TH>")
            Next i
            
            .write ("</TR>")
            
            Do Until rs.EOF
                If line_color = "#E8E8E8" Then line_color = "#F4F4F4" Else: line_color = "#E8E8E8"
                .write ("<TR BGCOLOR='" & line_color & "'>")
                For i = 0 To rs.fields.Count - 1
                    .write ("<TD ALIGN='left'>&nbsp;" & rs(i) & "</TD>")
                Next i
                .write ("</TR>")
                rs.movenext
            Loop
            
            .write ("<TR>")
            .write ("<TD ALIGN='center' STYLE='font-family:Arial;font-size:10px' COLSPAN='" & rs.fields.Count & "'>Printed: " & Format(Now(), "mmmm dd, yyyy hh:mm ampm") & "</TD>")
            .write ("</TABLE>")
            .write ("</DIV>")
            .write ("</BODY>")
            .write ("</HTML>")
        End With
        
    End If
    rs.Close
    Set rs = Nothing
    fso_ts.Close
    Set fso_ts = Nothing
    Set fso_file = Nothing
    Set fso = Nothing
    
    If OpenReport Then ShellExecute 0, "Open", ReportFile, 0, 0, 3
End Sub

**Lafor** · Oct 14th, 2003, 02:38 PM

This is not exactly what I need... but I can certainly use the ideas...

Thanks a lot for responding..

Thread: Export Help

Thread Tools

Display

Export Help

Thanks

Posting Permissions