Converting *.xls files to *.mht files

[BajanPOET]

How do I use VBA code to save an Excel file as a single file webpage(*.MHT file) so that my users have a copy of the data without being able to chage it?

I would put that into a command button on the side of the spreadsheet.

Any ideas? (Or is there another way of protecting the data after the file has been saved?)



What? Who? ME????

 

[SkipVought]

Hi,

Have you tried actually saveas .mht with the macro recorder on?

Skip,

_[red][/red]

 

[BajanPOET]

I haven't tried that because I need the user to be able to enter their own file name. I'm working on a template for purchase orders. My Access application that I've been working on populates the template in Excel, but it will be working on a different purchase order each time. So I was wondering if there was a way that I could get the user to enter the name of the file and have it saved as a *.mht file. A macro will just save the name of the template as template.mht.

What? Who? ME????

 

[SkipVought]

Well, it's the place to start, from which you can add all the bells and whistles your little heart desires.

Skip,

_[red][/red]

 

[BajanPOET]

Ok... I didn't think it would work. I'll try it.



What? Who? ME????

 

[SkipVought]

Has been an essental part of my toolbox!

Skip,

_[red][/red]

 

[BajanPOET]

I tried saving as a *.mht file with the macro recorder on, but then when I went into the macro there was no code there!


What? Who? ME????

 

[SkipVought]

when you started the macro recorder, WHERE did you save it? This workbook, PERSONAL, or other?

In the VB Editor of THAT workbook, did you see a MODULE in the Project Explorer?

Skip,

_[red][/red]

 

[BajanPOET]

I started in my template file, but when I saved the module it was saved in the purchase order file - the *.mht file.

What? Who? ME????

 

[SkipVought]

Okayyyyyyyyyyyy...........???????????

Skip,

_[red][/red]

 

[BajanPOET]

yeah that's what I think... I'll try it again.

What? Who? ME????

 

[BajanPOET]

I did it again... it saved in the *.mht file again... but at least I saw the code this time. So now I can take it out and put it in my command button and delete the *.mht file.


What? Who? ME????

 

[SkipVought]

Open a new workbook.

activate the VB editlr

DRAG the module from the .mth project to the new project

open the module.

copy the code and post back.

Skip,

_[red][/red]

 

[BajanPOET]

Well I just needed to know how it was done (the macro idea definitely worked!) I cut and pasted the code that was generated:

 ActiveWorkbook.SaveAs Filename:="O:\Requisitions\PurchaseOrders.mht", _
        FileFormat:=xlWebArchive, CreateBackup:=False

into the code behind a button so that I can use an input box to get the user to put the file name in to replace the "Purchase Orders" part

What? Who? ME????

 

[SkipVought]

sub button_click()
    ActiveWorkbook.SaveAs _
        Filename:="O:\Requisitions\" & YourTextBox.text & ".mht", _
        FileFormat:=xlWebArchive, _
        CreateBackup:=False

end sub
[code]


Skip,
[sub]
[glasses] [b][red][/red][/b]
[b][/b] [tongue][/sub]

 

[BajanPOET]

Yeah, that's what I had

What? Who? ME????