how to use dynamic name when creating save as macro in excel ?

reader6886

New Member
Joined
Sep 10, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
I created a simple macros to save excel file as webpage.I would like to make that macros possible to use for file with different names too without manually having to change the name for every different workbook/files . What and where do I change below so that macros saves the excel file as html with whatever the workbook file name was? I tried using ActiveWorkBook.name in place of mov2.htm but it didnot work.

VBA Code:
Sub tohtml()

' tohtml Macro

With ActiveWorkbook.PublishObjects.Add(xlSourceWorkbook, _

"C:\path\path\path\mov2.htm", , , xlHtmlStatic, "mov2_16832", "")

.Publish (True)

.AutoRepublish = True

End With

End Sub
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You could try this

Change "C:\path\path\path\mov2.htm" to

VBA Code:
"C:\path\path\path\" & ActiveWorkbook.name & ".htm"
 

reader6886

New Member
Joined
Sep 10, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
You could try this

Change "C:\path\path\path\mov2.htm" to

VBA Code:
"C:\path\path\path\" & ActiveWorkbook.name & ".htm"
FANTASTIC !!!! thank you so much . that works for me. one more thing just to make it bit more neater. right now it saves as x.y.htm , b.y.htm etc etc where y is the original file extension how can it save just as x.htm , b.htm.

thank you so for you time and expertise.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
there should be a
FANTASTIC !!!! thank you so much . that works for me. one more thing just to make it bit more neater. right now it saves as x.y.htm , b.y.htm etc etc where y is the original file extension how can it save just as x.htm , b.htm.

thank you so for you time and expertise.
There should be a couple of ways to do this.

in place of Activeworkbook.Name, so we extract just the name portion, you can use

VBA Code:
Split(ActiveWorkbook.Name, ".")(0)
or
VBA Code:
?Left(ActiveWorkbook.Name,instr(1,Activeworkbook.Name,".")-1)
or
VBA Code:
Replace(Activeworkbook.Name, ".xls", "")
if I am sure it is a .xls (maybe not my preferred approach), but just throwing a couple of ideas out there
 

reader6886

New Member
Joined
Sep 10, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Your solution works good .I used the last code .That was a great solution/macro for saving any workbook to htm with the workbook name .

I am trying to use/adapt the same code to save the active sheet only to htm not the whole workbook with the same effect of sheet/workbook file name. The following code which works perfectly for saving the whole workbook doest not work for sheet only. I only have one sheet per workbook so no complicated workbooks with lots of sheets with different name to bother about. The main reason I am trying to have macros for sheet saving not the whole workbook is not to deal with extra files that get produced with the folder.For my need I dont need those extra files/folder.

Could you please have a look and see where/what I am doing wrong in this adaption/code

VBA Code:
Sub workbookbutsheet()

    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
        "C:\path\" & Replace(ActiveWorkbook.name, ".csv", "") & ".htm", xlHtmlStatic, _
        "Replace(ActiveWorkbook.name, ".csv", "")", "")
        .Publish (True)
        .AutoRepublish = True
    End With
    
    
End Sub

Thank you so much.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Your solution works good .I used the last code .That was a great solution/macro for saving any workbook to htm with the workbook name .

I am trying to use/adapt the same code to save the active sheet only to htm not the whole workbook with the same effect of sheet/workbook file name. The following code which works perfectly for saving the whole workbook doest not work for sheet only. I only have one sheet per workbook so no complicated workbooks with lots of sheets with different name to bother about. The main reason I am trying to have macros for sheet saving not the whole workbook is not to deal with extra files that get produced with the folder.For my need I dont need those extra files/folder.

Could you please have a look and see where/what I am doing wrong in this adaption/code

Try this
VBA Code:
Sub workbookbutsheet()

    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
        "C:\path\" & Replace(ActiveWorkbook.name, ".csv", "") & ".htm", xlHtmlStatic, _
        "Replace(ActiveWorkbook.name, ".csv", "")", "")
        .Publish (True)
        .AutoRepublish = True
    End With
  
  
End Sub

Thank you so much.
It may have to do with the Replace being in Quotes
VBA Code:
Sub workbookbutsheet()

    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
        "C:\path\" & Replace(ActiveWorkbook.Name, ".csv", "") & ".htm", xlHtmlStatic, _
        Replace(ActiveWorkbook.Name, ".csv", ""), "")
        .Publish (True)
        .AutoRepublish = True
    End With  
End Sub
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

@reader6886 : Does the modification work?
 

reader6886

New Member
Joined
Sep 10, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Sub workbookbutsheet() With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _ "C:\path\" & Replace(ActiveWorkbook.Name, ".csv", "") & ".htm", xlHtmlStatic, _ Replace(ActiveWorkbook.Name, ".csv", ""), "") .Publish (True) .AutoRepublish = True End With End Sub

Unfortunately It did not work. I think It was getting bit difficult to keep track of the ' and "" so I have recorded a fresh new macro that saves active sheet to htm. When I tried to use this macros on new files it does not run .How would you change the following code so that it can be used on any file to save the active sheet to htm with dynamic name option ( sheet name to be the saved htm file name )?

below in theh code output is/was the name of the activesheet/workbook

VBA Code:
Sub Macro1()

    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
        "C:\path\output.htm", "output", "", xlHtmlStatic, "output_31163" _
        , "")
        .Publish (True)
        .AutoRepublish = True
    End With
    ChDir "C:\path"
End Sub

thank you very much.
 

reader6886

New Member
Joined
Sep 10, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
I have been suggested this edit in another site which works perfectly


VBA Code:
Sub Macro1()

Dim s As String
s = ActiveSheet.name

    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
        "C:\path\" & s & ".htm", s, "", xlHtmlStatic, "output_31163" _
        , "")
        .Publish (True)
        .AutoRepublish = True
    End With
    ChDir "C:\path"
End Sub


thank you for the help with publishing the workbook code that was brilliant.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
i was not even looki
I have been suggested this edit in another site which works perfectly


VBA Code:
Sub Macro1()

Dim s As String
s = ActiveSheet.name

    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
        "C:\path\" & s & ".htm", s, "", xlHtmlStatic, "output_31163" _
        , "")
        .Publish (True)
        .AutoRepublish = True
    End With
    ChDir "C:\path"
End Sub


thank you for the help with publishing the workbook code that was brilliant.
I was not even looking at the sheet name portion, that 's an easy fix, i was just concerned with getting rid of the error. Glad it works
 

Watch MrExcel Video

Forum statistics

Threads
1,127,210
Messages
5,623,401
Members
415,972
Latest member
SY1234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top