change page title with macro when saving as mht

[bkdwll]

I want to use a macro to save an .xls file as an .mht file. I have:

ActiveWorkbook.SaveAs Filename:="C:\Reports\Book2.mht", FileFormat:= _
xlWebArchive, CreateBackup:=False

which works for saving, but how can I Change the resulting web Page Title using VBA? I tried recording the macro as I changed the title and saved but nothing recorded for that. I know I can always just go in after the macro runs and manually change it but it seems like there must be another way.

Thanks in advance.

 

[sijpie]

I've playd around a bit, but it isn't going to be easy. I was thinking of renaming the mht to txt, importing it into excel find the <title>...</title> line, changing the title and saving it back as text file, then renaming it t mht.

However when saving it like that, excel surrounds each line with "", and then it doesn't work anymore.

I have also tried with a method for exporting to text by Pearson, but that truncated the file, probably because it was too large, or so. Don't understand.
There is a way you can control other programs in a macro, so you could open it in Notepad and do the deed there (that will certainly work) but I don't know how to code for other applications.

 

[bkdwll]

I found it after A LOT of searching. BuiltInDocumentProperties. I still have to save as first (there's probably a way to do it all in one command, but I couldn't figure it out), rename it and then save again, but it works.

ActiveWorkbook.SaveAs Filename:="C:\Reports\Book1.mht", FileFormat:= xlWebArchive, CreateBackup:=False
ActiveWorkbook.BuiltinDocumentProperties("Title") = "Book 1"
ActiveWorkbook.Save

Thanks for responding and the help.