vba excel to html

hi dears

i have excel with 10 sheets and i need vba for convert to 1-html format and 2-csv comma utf8 and 3.txt format

1-do it for entire workbook automatically and with no question

2-for each sheet make seperate file name and same as sheet name in my excel file path

3- printarea by default selected for each sheets , for convert

thx dear

hi royUK

excel for example,i shared on google drive . thx for your try . about if will be done is anf for txt i can use kutoolsplus and no problem . please try about html and csv utf8 . thx

royUK

i fix your description.in this file,print area is set for all sheets

thx

there is 2 issue

1-for csv vba :

a) i need make file in same as directory of file.xlsm

2-for html

a)i need exact .html export but this code prepare mhtml and must change to html . thx

b)i need for each sheet make seperate file name and same as sheet name but this vba make book1.html and etc ......

c)i need make file in same as directory of file.xlsm

Quote from royUK

1. add the filepath into the code.

2. You don't answer my questions, as far as I know this is the only way to save a web page.

about html ,

1-in first step , i donot need show more specification , only need simple table show with cell character content

2-only need html ,please refer to https://docs.microsoft.com/en-…el.publishobject.htmltype

Quote from royUK

Well if you've found the code then try it.

Also, why do you need html?

i have online store and from excel change the price and then convert to html and pdf and then show on my site . i want to show with html because it is very lower size and more mobile friendly and responsive . i have a plugin for show table on my site that only work with csv and html format

Quote from royUK

I think this is on the right track.

I've amended the csv code and found & adapted some code to publish to html.

They will be created in the same folder as the workbook, but you need to add a subfolder and name it HTML

Code

Option Explicit


Sub SaveWorkSheetAsCSV()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
oWs.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & oWs.Name & ".csv", xlCSVUTF8
ActiveWorkbook.Close True
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub


Sub PublishOnWeb(strSheetName As String, strFileName As String)
Dim objPub As Excel.PublishObject
Set objPub = ThisWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:=strFileName, Sheet:=strSheetName, HtmlType:=xlHtmlStatic, Title:="Calls Analysis")
objPub.Publish True
End Sub


Sub CreateHTMLFile()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
Call PublishOnWeb(oWs.Name, ThisWorkbook.Path & .PathSeparator & "HTML" & .PathSeparator & oWs.Name & ".htm")
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Display More

Display More

for csv is okey

for html , have error

in line : Call PublishOnWeb

compile error

sub or function not defined

Quote from royUK

I think this is on the right track.

I've amended the csv code and found & adapted some code to publish to html.

They will be created in the same folder as the workbook, but you need to add a subfolder and name it HTML

Code

Option Explicit


Sub SaveWorkSheetAsCSV()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
oWs.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & oWs.Name & ".csv", xlCSVUTF8
ActiveWorkbook.Close True
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub


Sub PublishOnWeb(strSheetName As String, strFileName As String)
Dim objPub As Excel.PublishObject
Set objPub = ThisWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:=strFileName, Sheet:=strSheetName, HtmlType:=xlHtmlStatic, Title:="Calls Analysis")
objPub.Publish True
End Sub


Sub CreateHTMLFile()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
Call PublishOnWeb(oWs.Name, ThisWorkbook.Path & .PathSeparator & "HTML" & .PathSeparator & oWs.Name & ".htm")
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Display More

Display More

this is code for produce html and only need to 1-repeat for entire workbook and 2-printarea by default selected for each sheets for convert and 3-html name is same as sheet name

Sub Export()
 Dim rng As Range

 file1 = ThisWorkbook.Path & "\" & "test.html"

 Set rng = Sheets("Tabelle1").Range("A1:C10")

 ActiveWorkbook.PublishObjects.Add( _
 SourceType:=xlSourceRange, _
 Filename:=file1, _
 Sheet:=rng.Worksheet.Name, _
 Source:=rng.Address, _
 HtmlType:=xlHtmlStatic).Publish

End Sub

i use , office 2019 and you ?