Quick question...

[Scott Zierler]

Hi All,

Could anyone please help me figure out how to make a specified field in a
worksheet be the
file name in the code below. Currently the name has to be manually typed
in, such as below Cambell.htm. I can't think of the variable or fieldname.
Thanks.

Scott

Code Below

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="Campbell.htm", _
Sheet:="Schedule", _
Source:="B1:F107", _
HtmlType:=xlHtmlStatic).Publish
ChDir "G:\My Documents"

 

[Bob Phillips]

Scott,

Use Activecell.Value or say Worksheets(1).Range("A1").Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Scott Zierler]

Hi Bob,

Thanks for the info, I tried what you mentioned in the post, but I get a
compilation error, any further thoughts...

Code Below

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="Worksheets(1).Range("C5").Value", _
Sheet:="Schedule", _
Source:="B1:F107", _
HtmlType:=xlHtmlStatic).Publish
ChDir "G:\My Documents"

 

[Bob Phillips]

Scott,

No quotes, it's not a string

Filename:=Worksheets(1).Range("C5").Value, _

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Scott Zierler]

Hi Bob,

Boy I sure to appreciate your help with this. I made that change you
mentioned and when I attempt to run it, VBA reports a Run Time Error 9,
Subscript out of range. Help?

Code Below

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=Worksheets(Schedule).Range("C5").Value, _
Sheet:="Schedule", _
Source:="B1:F107", _
HtmlType:=xlHtmlStatic).Publish
ChDir "G:\My Documents"

 

[Bob Phillips]

Scot,

You keep changing the brief. You had Worksheets(1) originally and now you
have Worksheets(Schedule)<vbg>. And guess what? That should be in quotes

Filename:=Worksheets("Schedule").Range("C5").Value, _

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Phobos]

Your worksheet name IS a string though. <g>

Filename:=Worksheets("Schedule").Range("C5").Value, _

P