Publishing a selection to the web (static)

[Scott Zierler]

Hello all!

I have recently upgraded to Office 2003 from Office XP.
Here is my dilemma.

Before the upgrade I had written a macro which would publish a selected
range of cells to a .HTM file using a specified cell as the filename. This
worked wonderfully. Now when I run my macro in office 2003, the resulting
file is a .mhtml file which from what I understand is a MIME encapsulated
HTML file which is a self runinng single web page. However, when I FTP these
to my website for my clients to view they get text, no graphics, colors,
etc...

Please look at the following code which worked correctly in Office XP now
doesnt work in Office 2003.

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

Another thought.....If I record a new macro selecting the cells and save to
the web, the resulting file has a HTM extension, not a MHTML extension as my
previous macro now mysteriously generates.

THANKS FOR YOUR HELP!

Scott

 

[Andibevan]

Hi Scott,

There is microsoft.public.excel.programming that is a forum specifically for
VBA within Excel - I am sure that people will find your post here though.

Please tell me what the value in Cell C4 on Sheet Schedule is?

Make sure that this filename has the htm extension you are looking for.

Regards

Andy

 

[Scott Zierler]

Hi Andy,

The value in the C4 cell is an address (i.e. 1234 Main St)

The main issue here though is the extension that the Save to Web is
assigning to my output. The Office XP was assigning .htm and the Office 2003
is using .mhtml. These Mhtml files are not working...text only, no other
formatting.

Thanks for the recommendation on changing the post to other forum.

Scott

 

[Jim Cone]

Scott,

I see in xl2002 that the PublishObjects help file is somewhat botched up.
MS lists the same constants for the source type and the html type.

Have you checked the constants in xl2003 to see if MS has "improved" them?
I don't have xl2003, so I can't check.

Other than that, you might want to replace the constant with the numeric value.
In xl2002, the xlhtmlstatic constant value is 0.

Regards,
Jim Cone
San Francisco, USA



Hello all!

I have recently upgraded to Office 2003 from Office XP.
Here is my dilemma.

Before the upgrade I had written a macro which would publish a selected
range of cells to a .HTM file using a specified cell as the filename. This
worked wonderfully. Now when I run my macro in office 2003, the resulting
file is a .mhtml file which from what I understand is a MIME encapsulated
HTML file which is a self runinng single web page. However, when I FTP these
to my website for my clients to view they get text, no graphics, colors,
etc...

Please look at the following code which worked correctly in Office XP now
doesnt work in Office 2003.

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

Another thought.....If I record a new macro selecting the cells and save to
the web, the resulting file has a HTM extension, not a MHTML extension as my
previous macro now mysteriously generates.

THANKS FOR YOUR HELP!

Scott

 

[Scott Zierler]

Hi Jim,

Thanks for the suggestion. I dove into the VBA help and dug around. Where
would you add the constant of 0 in the following code?

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=Worksheets("Schedule").Range("C4").Value, _
Sheet:="Schedule", _
Source:="B1:F107", _
HtmlType:=xlHtmlStatic).Publish
ChDir "C:\Documents and Settings\ScottZierler\My Documents"
End Sub

I want to force Excel to generate an HTM file not a MHTML file...

Thanks!

Scott

 

[Jim Cone]

Scott,

You have to go to the "Add" method for the PublishObjects collection (in the help file)
to see the display of the source type constants and html type constants.

'----------
Try Replacing:
HtmlType:=xlHtmlStatic).Publish

with:
HtmlType:=0).Publish
'-----------

If that doesn't do it, then you might want to experiment with the rest of
the numeric values to see if they do what you want.
The values allowed, according to the object browser, are 0, 1, 2, 3.

Hope this helps.

Jim Cone
San Francisco, USA



message Hi Jim,
Thanks for the suggestion. I dove into the VBA help and dug around. Where
would you add the constant of 0 in the following code?
ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=Worksheets("Schedule").Range("C4").Value, _
Sheet:="Schedule", _
Source:="B1:F107", _
HtmlType:=xlHtmlStatic).Publish
ChDir "C:\Documents and Settings\ScottZierler\My Documents"
End Sub
I want to force Excel to generate an HTM file not a MHTML file...
Thanks!
Scott

 

[Tom Huna]

Scott, did you ever find the solution to your problem???? I am now running
into the same issue. Let me know. Thanks...