Results 1 to 6 of 6
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Export to HTML

    What is the correct format to export to HTML? I am trying to do the right click Export to HTML of a query in VBA.



    DoCmd.OutputTo 1, "qryDaySumm", acOutpotHTML, "C:\Temp" & NIE & " Daily Summary - " & Tod & ".html"

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    try
    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12Xml ,query,filename,true,sheetname

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ranman,
    I already have this: DoCmd.TransferSpreadsheet 1, 10, "qryDaySumm", "C:\Temp" & NIE & " Daily Summary - " & Tod & ".xlsx"

    It outputs a plain text format. Without the breaks/formatting. What I am trying to do is:

    1. Export a HTML file to a C:\Temp with a file name
    2. Open the HTML with Excel
    3. Delete the first row
    4. Save the file
    5. Attach to an email.
    6. Kill the file.

    I already export the file to a plain xlsx file, #4,#5, and #6.

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    What I am doing is a workaround to avoid a huge complicated process in VBA.

    What I would like to do is automate the right click sequence. You select a query, right click, select export to HTML. What is the VBA coding for that?

    Thanks

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Solved
    DoCmd.OutputTo acOutputQuery, "qryWeeklySumm", "HTML(*.html)", "c:\Temp\test.html", False, "", 1200, acExportQualityPrint

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    For me this was the shortest possible way to get Rich Type Formatting output to an Excel spreadsheet and attached to an email. Below is an example for FYSA

    Code:
    Public Sub Send_HBRoll_Click()
    Dim xlApp As Excel.Application
    Dim WkBkA As Excel.Workbook
      
     On Error GoTo ErrorMsgs
      
    DoCmd.OpenQuery "qryDailyUpdate"
    DoCmd.OpenQuery "qryRollupUpdate"
     
     NIE = DLookup("[NIE]", "[tblChangeRequest]")
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Set xlApp = New Excel.Application
          xlApp.Visible = False
      
    
    With objOutlookMsg
       .Subject = NIE & " HB Weekly Rollup - " & Tod
       .Body = SigBlock
        DoCmd.OutputTo 3, "rptHBRollup", acFormatPDF, "C:\Temp\" & NIE & " HB Weekly Rollup - " & Tod & ".pdf", , 0
        DoCmd.OutputTo 3, "rptAnnexB", acFormatPDF, "C:\Temp\" & NIE & " Annex B - " & Tod & ".pdf", , 0
        
    DoCmd.OutputTo acOutputQuery, "qryWeeklySumm", "HTML(*.html)", "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".html", False, "", 1200, acExportQualityPrint
        Set WkBkA = Workbooks.Open("C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".html")
        WkBkA.Sheets(1).Rows(1).Delete
        WkBkA.SaveAs ("C:\Temp\" & NIE & " Weekly Summation - " & Tod), 51
       
       .Attachments.Add ("C:\Temp\" & NIE & " HB Weekly Rollup - " & Tod & ".pdf")
       .Attachments.Add ("C:\Temp\" & NIE & " Annex B - " & Tod & ".pdf")
       .Attachments.Add "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".xlsx"
       .To = "HB Rollup"
       .Display
        Kill "C:\Temp\" & NIE & " HB Weekly Rollup - " & Tod & ".pdf"
        Kill "C:\Temp\" & NIE & " Annex B - " & Tod & ".pdf"
        Kill "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".html"
        Excel.Application.Quit
        Kill "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".xlsx"
     
     End With
      
     Set objOutlookMsg = Nothing
     Set objOutlook = Nothing
     Set objOutlookAttach = Nothing
     Set WkBkA = Nothing
      
      Exit Sub
    
    ErrorMsgs:
     If Err.Number = "287" Then
      MsgBox "You selected No to the Outlook security warning. Rerun the procedure and click Yes to access e-mail addresses to send your message."
     Else
     MsgBox Err.Number & " " & Err.Description
    End If
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export html to body of email issue
    By mmart33 in forum Reports
    Replies: 3
    Last Post: 02-28-2013, 03:16 PM
  2. Export to html AccessTemplate tags
    By starhawk85 in forum Import/Export Data
    Replies: 1
    Last Post: 04-30-2012, 10:30 AM
  3. Export to HTML Button on Form
    By iProRyan in forum Forms
    Replies: 2
    Last Post: 04-26-2012, 11:41 AM
  4. Access 2003 - Export Report to HTML
    By darth.pathos in forum Import/Export Data
    Replies: 0
    Last Post: 03-20-2010, 01:13 PM
  5. Best approach to export to HTML in this scenario
    By techguy817 in forum Import/Export Data
    Replies: 0
    Last Post: 04-17-2009, 10:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums