Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Hide Rows In HTML Export (2003 SP2)

    I use the macro below to export part of a grade spreadsheet to HTML for posting to the internet.

    Now ... the suits have said that I have to allow students to opt out of this. I would prefer to modify the existing macro.

    Sub exportToHTML()
    Dim strMyDocs As String
    strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desk top")
    With ActiveWorkbook.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=strMyDocs & "page1.htm", _
    Source:="grades_1", _
    HtmlType:=xlHtmlStatic)
    .Publish (False)
    .AutoRepublish = False
    End With
    End Sub

    The named range grade_1 has a row for each student (say A11.C25) followed by several rows of summary statistics (say A26.C30). There is nothing below row 30. Column B is a 0/1 index where 1 means show their grade, and 0 means to not show their grade.

    I envision something that will loop over rows 11 to 25 (I don't mind editing those values for each class), check the value in column B, and either include or exclude that row from the HTML output without changing the underlying data.

    Suggestions?

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

    Re: Hide Rows In HTML Export (2003 SP2)

    Set an AutoFilter on the grades_1 range and select 1 in the dropdown for column B. The code will only export the filtered rows. You can do this in your code by inserting

    Range("grades_1").AutoFilter Field:=2, Criteria1:="1"

    near the beginning, and

    ActiveSheet.ShowAllData

    near the end.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Hide Rows In HTML Export (2003 SP2)

    I had a lot of problems with this, but I did get it working.

    The "ActiveSheet.ShowAllData" was always flagged as a debug problem if I ran the macro more than once.

    A statement like this:

    range("grades_1_.autofilter field:=2, criteria:="<>-1"

    did the same job, and seems to work without problems (of course, my index only contains 0 and 1, so the -1 never gets filtered).

    Thanks HansV

    EOM

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hide Rows In HTML Export (2003 SP2)

    ActiveSheet.ShowAllData

    will give an error if nothing is filtered (ie all the data is being shown). I would use instead:

    If ActiveSheet.FilterMode then ActiveSheet.ShowAllData

    to prevent the error

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Hide Rows In HTML Export (2003 SP2)

    That didn't seem to catch the error. Here is the macro. I commented out my range statement (that worked) and replaced it with your suggestion. The de###### pointed to your first line.

    Very long code fragment moved to attachment by HansV

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

    Re: Hide Rows In HTML Export (2003 SP2)

    Your code has

    ActiveSheet.FilterMode
    ActiveSheet.ShowAllData

    Steve suggested

    If ActiveSheet.FilterMode then ActiveSheet.ShowAllData

Posting Permissions

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