Open excel doc and save with password 2

[hondaman2003]

I know how to open an excel document and change data and formatting. How can I save the file with a password so it can not be openned at all without the password? Separately how can I save with a password so it can only be openned read only without the password?

Here is my code to open and save the file

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim mySheet As Excel.Worksheet
Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("R:\Specialty\Factor\Assay reports\FACTOR OVERVIEW " & Format([End date], "mmm yyyy") & ".xls")
Set mySheet = myWorkbook.Sheets("Prescribed_Dispensed_Difference")

<commands to edit data and change formatting>

mySheet.Columns("A:I").EntireColumn.AutoFit
mySheet.Columns("I").NumberFormat = "0.00%"
myWorkbook.Save
myWorkbook.Close

Set appExcel = Nothing
Set myWorkbook = Nothing
Set mySheet = Nothing

 

[bubba100]

In Excel help lookup Activesheet.protect

 

[RoyVidar]

Try something like this

myWorkbook.SaveAs "<path and name>", , "ThePassword"

Roy-Vidar

 

[PHV]

Anyway, I'd quit properly the excel app:

...
myWorkbook.Close
[!]appExcel.Quit[/!]
Set appExcel = Nothing
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hondaman2003]

Thank you RoyVidar that worked

Thank you PHV, I'm going to start closing excel properly

One more question would be, how to you add the password, but allow the document to be opened read only without the password?

 

[hondaman2003]

And one more thing, I'm saving the file directly over itself, is there a way to have it not ask to overwrite the file?

 

[RoyVidar]

Oh, forgot that, it's the fourth arguement

appExcel.DisplayAlerts = False
myWorkbook.SaveAs "<path and name>", , , "PasswordToChange"
appExcel.DisplayAlerts = True

Roy-Vidar

 

[PHV]

appExcel.DisplayAlerts = False
myWorkbook.SaveAs "<path and name>", , (!], [/!]"ThePassword"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hondaman2003]

I'm affraid I don't understand the command myWorkbook.SaveAs "<path and name>", , (!], [/!]"ThePassword"

I used it exactly as it was typed and it doesn't work. Since I don't know how to use the command I cannot catch the simple error in it, but can you clarify it for me?

 

[RoyVidar]

<path and name>" means the actual path and name of the file you wish to save, like "c:\test.xlsx", "F:\wombat.docm" etc, that you must provide.

"ThePassword" means the literal password, such as "guzunder", "Foo", "Beer", "Avec" or whatever is used as password, which is also something you must provide.

The (!], [/!] part, means PHV wanted to make the third comma red, as in

myWorkbook.SaveAs "<path and name>", , [!], [/!]"ThePassword"

which is the result of the following code

myWorkbook.SaveAs "<path and name>", , [ignore][!], [/!][/ignore]"ThePassword"

where the [ignore][!][/!][/ignore] tags are meant to give this horrendous red colour to whatever is between the tags, but switched a starting bracket with starting parenthesis.

More can be found out about the SaveAs command by placing the cursor within the keyword in the VBE, and hit F1.

For more info on the tags, hit the "Process TGML" link at the bottom of the reply pane.

Roy-Vidar

 

[PHV]

Sorry for the typo in the TGML tags:
myWorkbook.SaveAs "<path and name>", , [!], [/!]"ThePassword"

Anyway, roy posted the correction at same time.

 

[hondaman2003]

Thank you very much RoyVidar and PHV. I completely understand now!

The last thing is, if the saveas function is going to overwrite something, is there a way to have access NOT prompt for the overwrite and just do it?

 

[PHV]

access prompt for the overwrite
Even with the below code ?
appExcel.DisplayAlerts = False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hondaman2003]

Perfect thank you!