Why doesnt this work
 

Dear All,

Can you have a look at this and please tell why it doesnt work

File Path is correct



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




Many Thanks in advance



Steve

Why doesnt this work
 

Hi
Try
Dim myobject As PublishObject
Set myobject = ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"I:\" & fName & ".htm", wks.Name, "DataRange", _
xlHtmlStatic, wks.Range("J4").Value)

myobject.Publish

You only apply brackets round the variables for the Add method when it
is combined with a Set ... = as above. Without the Set you leave out
the brackets

ActiveWorkbook.PublishObjects.Add xlSourceSheet, _
"I:\" & fName & ".htm", wks.Name, "DataRange", _
xlHtmlStatic, wks.Range("J4").Value

This syntax does not allow you to do .Publish though.
regards
Paul

On Mar 3, 11:29*am, steve wrote:
Dear All,

Can you have a look at this and please tell why it doesnt work

File Path is correct

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

Many Thanks in advance

Steve