Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OutputTo html? (XP)

    I have done a crosstab query and currently am OutputTo-ing to Excel, running some formatting code and then saving as html.

    The query lists all the hours in the day as rows, against days in columns, with a count function for each cell. It shows how many people have signed up for a certain time/day. I am using vba to go though the range in excel and apply different colours and text changes, alignment etc, and add titles and notes before the save as html.

    This is working quite well, but was wondering if anyone knows how easy is would be to OutputTo html directly. I would need to use a html template, of which I know nothing about (yet). I imagine I could have the table and titles etc all set up and export the html file into the template, but then I wonder if i would be able to change the colours of the individual cells.

    Also, I have had to put a messgae box in my code between the outputto command and the getobject command (see below) as the code throws an error otherwise, as the Excel application hasnt 'materialised' bu the time the getobject command runs! Any thoughts of 'cleaning this up'?

    <pre>Sub doExcel()
    DoCmd.OutputTo acOutputQuery, "Query1_Crosstab", acFormatXLS, _
    "Cocuments and SettingsmatarazzopDesktoptemp.xls", True

    MsgBox "Click me" 'use to allow excel object to be created

    Dim o As Object, rngSel As Object, rngCell As Object

    Set o = GetObject(, "Excel.application")
    o.Visible = True
    Set rngSel = o.worksheets(1).Range("a1:h25")

    ....

    End Sub</pre>

    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OutputTo html? (XP)

    I can't help you with the HTML.

    I would set the AutoStart argument of OutputTo to False (or omit it, False is the default), and use code to open the workbook:

    Dim strFilename As String
    Dim o As Object, w As Object, rngSel As Object, rngCell As Object

    strFilename = "Cocuments and SettingsmatarazzopDesktoptemp.xls"
    DoCmd.OutputTo acOutputQuery, "Query1_Crosstab", acFormatXLS, strFilename

    Set o = CreateObject("Excel.Application")
    Set w = o.Workbooks.Open(strFilename)
    o.Visible = True ' do you need this?
    Set rngSel = w.Worksheets(1).Range("A1:H25")
    ...

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OutputTo html? (XP)

    Yes, that makes sense. Thanks.

    No worries about the html, I dont think thats the way for me to go on this one anyhow, but it would be good to know if anyone has used this feature of Access.

    I am wondering if I can 'call' a macro from within Excel from Access. Not sure if this can be done?

    I have set the 'trust access to VBA' in the workbook to true, and I can refernce the VBComponents from within Access, but not sure if I can actually EXECUTE them. Can this be done Hans?

    something like

    <font color=blue>Dim xl As Excel.Workbook
    Set xl = GetObject("Cocuments and SettingsmatarazzopDesktopbook1.xls")
    xl.VBProject.VBComponents(5).PublicSub_1</font color=blue>
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OutputTo html? (XP)

    If you put an automatic macro in the workbook (Workbook_Open in the ThisWorkbook module) it should be run when the workbook opens.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OutputTo html? (XP)

    Fair play, <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    trying to do something so simple in such a complicated way!

    ok, last Q for today Hans, should be simple but can't seem to clinch it.

    after this:
    <font color=blue>
    With o.activeworkbook.PublishObjects.Add(4, _
    "Cocuments and SettingsmatarazzopDesktopPage.htm", "Query1_Crosstab", _
    "$A$1:$H$25", 0, "temp_24163", "")
    .Publish (True)
    .AutoRepublish = False
    End With </font color=blue>

    I want to open the html file. I can set a variable to the file with getobject("Cocuments and SettingsmatarazzopDesktopPage.htm") but then how do i open it to view? or should i use an API - i use them to print files sometimes.

    Muchas Gracias [img]/forums/images/smilies/wink.gif[/img]
    Thanks,

    pmatz

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OutputTo html? (XP)

    You can use the Access method Application.FollowHyperlink, or the FollowHyperlink method of the Excel workbook (both followed by the name of the HTML file), or use the Windows Api function ShellExecute - see for example <post#=374409>post 374409</post#>.

  7. #7
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OutputTo html? (XP)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Thanks,

    pmatz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •