Save Excel book as Html Page Using Macro

[Excel]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/02/2006 by rulebr
'

'
With ActiveWorkbook.PublishObjects("Brady Standard Quote 7.2.06_23158")
.HtmlType = xlHtmlStatic
.Publish (False)
End With
End Sub




Ive recorded the following macro to save my worksheet as a html page.
Ive created a button and that works fine.

Is there a way I can get tricky with the name of the file it creates.

Basically I would like the file name to contain a number that increments
every time the macro is pressed.
Id also like it to contain the date, and the company name which is from cell
in the spreadsheet.


eg.

QuoteNo_1000_Company Name(Cell D10)_09022006.xls

QuoteNo_1001_Company Name(Cell D10)_09022006.xls

I guess i need to store the incrementing number somewhere in a worksheet of
its own so it knows the number to call.

Any help with the macro appreciated.

 

[Nick Hodge]

Not in a position to test but you would build it like so and then use the
filename variable instead of the filename. Change the sheet names and cell
references as necessary

Dim TodaysDate As String
Dim CoName As String
Dim NextNumber As Integer
Dim Filename As String

TodaysDate = Format(Now(), "dd.mm.yyyy")
NextNumber = Worksheets("OrderNo").Range("A1").Value
CoName = Worksheets("CoNames").Range("D10").Value
Filename = "QuoteNo_" & NextNumber & "_" & CoName _
& "_" & TodaysDate & ".xls"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

 

[David McRitchie]

I'm just dumping what I started to work on from your other post, and you
can work through differences that you want to include. Please post to
ONE thread and use your name.

Looks like Nick has your increment method included
(like an invoice number), you will have to add 1 to the number on that
sheet for your filename and copy that number back to that other sheet.

You want to include formatting, take a look at
http://www.mvps.org/dmcritchie/excel/backup.htm


dim dname as string, quoteNo as long
dname = "c:\mybackup\QuoteNO_" & Format(quoteNo,"0000_" _ & TRIM(cells(10,4).text) _ & Format(Now(), "yyyy_mmdd"):
and I would suggest that you put the year first as shown above, so that
your directory can be sorted into alphabetical order.

 

[David McRitchie]

that long line is supposed to be on three lines with break char (" _")
between each line. Looked okay when I sent it but I had copied a
line from my webpage (HTML) so it had HTML formatting and was
converted to text (incorrectly) when it was sent.