Password on Excel

  • Thread starter Thread starter Stephen Lynch
  • Start date Start date
S

Stephen Lynch

In access, I have code that adds data from the database via a query to an
excel file. I now need to password protect the file and I am having trouble
password protecting my excel workbook via script. Any help is appreciated:

Here is my code:

Dim ExcelApp As Excel.Application
Dim workBook As Object
Dim workSheet As Object

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
ExcelApp.Workbooks.Open ("S:\test.xls")
ExcelApp.Cells.Select
ExcelApp.Selection.Columns.AutoFit
ExcelApp.Application.DisplayAlerts = False
ExcelApp.Application.Save
ExcelApp.Application.DisplayAlerts = True
ExcelApp.Application.Quit


This runs but if I add

ExcelApp.Worksheets.Protect Password = "1234" before
ExcelApp.Application.Save I get an error. Anyone know the correct code to
add?

Thanks
 
Hi Stephen,

If you just want put a password on the file, use the Password argument
of Workbook.SaveAs. If you want to protect worksheets you have to do
it one at a time, e.g.

...
Dim wksW As Excel.Worksheet
...
For Each wksW in ExcelApp.Workbooks(1).Worksheets
wksW.Protect Password:="1234"
Next wksW
ExcelApp.Workbooks(1).Save
 
Back
Top