Thread: Export to HTML filename and filepath

Hello,
I need a macro to export a range of cells to .html. The Save as Web Page works fine for me except a few thing that I need to automate. The macro I use now looks like this (recorded):

 Sub Button5_Click()
'
' Button5_Click Macro
' Macro recorded 7/24/2009 by Magic
'

'
    Range("A1:G25").Select
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Documents and Settings\Magic\Desktop\Page.htm", "1F", "$A$1:$G$25", _
        xlHtmlStatic, "v3_177", "")
        .Publish (True)
        .AutoRepublish = False
    End With
    ChDir "C:\Documents and Settings\Magic\Desktop"
End Sub

I know nothing about VBA but from all my research I found that it is posible to define a lot of options from the code. I just do not know how to implement the code for my needs. Here is what I want to achieve:
- the filename must be the text from a cell (+the html extension)
- the path for the saved file must be in the same location as the workbook
- if the file exists it would be automatically overwritten

Please help guys! I know it is easy for you, but for me it is a nightmare

I also found this code:

Sub CopySheetAsHTML() 
    Dim fName As String 
    Dim wks As  Worksheet 
    Set wks = ActiveSheet 
    fName = wks. Range("J4").Value 
    With  ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _ 
        "I:\" & fName & ".htm", wks.Name, "DataRange", _ 
        xlHtmlStatic, wks.Range("J4").Value) 
        .Publish (True) 
    End With 
End Sub

I guess it is close to what I need (except the file path) but it does not work.

Hi Magic

Try this:

 Sub Button5_Click()
'
' Button5_Click Macro
' Macro recorded 7/24/2009 by Magic
'
On Error Resume Next
Kill ThisWorkbook.Path & "\" & Range("A1").Value & ".htm"
On Error Goto 0
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        ThisWorkbook.Path & "\" & Range("A1").Value & ".htm", "1F", Selection.Address, _
        xlHtmlStatic, "v3_177", "")
        .Publish (True)
        .AutoRepublish = False
    End With

End Sub

Change the address in red to be whatever cell holds the workbook name.

Richard