I have written code that saves multiple worksheets individually to multiple html pages (using ActiveWorkbook.Publish.Add()). It works great except that there are lots of garbage characters in the resulting html pages. I found that if I convert the tables to ranges, I can eliminate the garbage text
With the tables present, I am able to use the table name as the "Source" argument (ex: "TableName:TableName")
When I convert the tables to ranges in VBA, I can no longer refer to the range with the data that used to be in a table.
Is there a way to set a string variable to the range of the table before I remove it? so that I can refer to the same data in A1 format?
For example,
I have a table that occupies A1:G1000 named TABLE1, I want to write code that will output a string value: "A1:G1000" by referencing TABLE1 to a string variable ("TableRange"). This is so that after I convert the table to a range I can populate the ActiveWorkbook.Publish.Add() SOURCE argument with the variable "TableRange" containing "A1:G1000"
NOTE I've left my code to convert table to range commented out
Function MakeSaleTableHTM(ProdSheet As String, ProdTable As String, PageTitlePre As String, PageTitlePost As String, PathPre As String, FileName As String, PathExt As String)
Dim PageTitle As String
Dim strPath As String
Dim strSheet As String
Dim strTable As String
PageTitle = "" ' PageTitlePre & ProdSheet & PageTitlePost
strPath = PathPre & FileName & PathExt
strSheet = ProdSheet
strTable = ProdTable
Sheets(strSheet).Activate
' convert table to range
' Dim xSheet As Worksheet
' Dim xList As ListObject
' Set xSheet = ActiveWorkbook.ActiveSheet
' For Each xList In xSheet.ListObjects
' xList.Unlist
' Next
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
strPath, _
strSheet, _
strTable, _
xlHtmlStatic, "PublishToHTML", _
PageTitle)
.Publish (True)
End With
End Function
Bookmarks