Microsoft Access VBA Coding to Excel Password Protect Fail

[Knicks]

The Protect Worksheet was working when I had created the Excel spreadsheet by manually clicking the export button on the ribbon. I needed it more automated so I use the transfer spreadsheet 12xml to get the file in XLSX format. That works for the correct file format and I am still able to Protect Workbook. But the Worksheet Protect is failing. Run Time error 1004 Method "Protect of Object '_Worksheet" failed. I also tried just using WS.Protect to keep it simple and that failed the same way. I tried removing the WB.Worksheets("qryFCST_Template").Cells.Select line and that didn't help. I also tried moving the select line just above the Password Protect line and it still failed. Any suggestions would be appreciated

Here is the code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryFCST_Template", "C:\Database\Excel_Output\WK41_FCST_COUNTRY.xlsx", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qrySheetSummary", "C:\Database\Excel_Output\WK41_FCST_COUNTRY.xlsx", True


Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Worksheet
Dim XLrange As Range
Set xlApp = New Excel.Application
Dim Z As Long



Set WB = xlApp.Workbooks.Open("C:\Database\Excel_Output\WK41_FCST_COUNTRY.xlsx") 'file reference location

WB.Worksheets("qryFCST_Template").Cells.Select


Set WS = WB.Worksheets("qryFCST_TEMPLATE")


Z = WS.UsedRange.rows(WS.UsedRange.rows.Count).row


For i = 2 To Z


WS.Range("N" & i).Formula = "=L" & i & "+M" & i



Next i


WS.Cells.EntireColumn.AutoFit
WS.Cells.EntireRow.AutoFit




Set XLrange = WS.Range("A1:k1")
XLrange.Interior.Color = RGB(192, 192, 192) 'set the data columns from CASA range to gray

Set XLrange = WS.Range("L1:N1")
XLrange.Interior.Color = RGB(0, 255, 255) 'set the columns involved in INPUT to BLUE

Set XLrange = WS.Range("L2:M" & Z)
XLrange.Locked = False

WS.Protect Password:="BASA", allowformattingcells:=False, AllowFormattingColumns:=False, AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, AllowUsingPivotTables:=False[/color]









WS.Name = "CASA WK41 TOPS DOWN FCST"
WB.Protect Password:="BASA"



WB.Save
WB.Close False
xlApp.Quit

Set xlApp = Nothing
Set WB = Nothing
Set WS = Nothing
Set XLrange = Nothing

 

[Knicks]

I just ran a test. It has something to do with the ADDED worksheet from the 2nd query making it fail. They wanted additional data on the 2nd worksheet and I was going to add in the various formatting and protections using the first part as a template to do the 2nd worksheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qrySheetSummary", "C:\Database\Excel_Output\WK41_FCST_COUNTRY.xlsx", True

So this additional worksheet is making the Password Protect of Worksheet fail. I also needed to change the name of both worksheets. I assume it is best change them before Password protecting worksheet/workbook

 

[Knicks]

I tried to super simplify it to see if one could loop through the Worksheets to apply Protect. This still generates an ERROR 1004. I also tried adding the qualifier WB.ActiveWorkbook.Worksheets to see if that would help but it generated a different ERROR 438. This seems fairly straightforward, anyone have a technique for Worksheet Protect when there is more than 1 worksheet

Dim WB As Excel.Workbook
Dim WS As Worksheet
Dim XLrange As Range
Set xlApp = New Excel.Application
Dim Z As Long


Set WB = xlApp.Workbooks.Open("C:\Files\Test\WK41_FCST_COUNTRY.xlsx")

For Each WS In ActiveWorkbook.Worksheets 'Generates ERROR 1004
'For Each WS In WB.ActiveWorkbook.Worksheets 'Alternate. Generates ERROR 438 Object Doesn't Support Property or Method
WS.Protect Password:="BASA"
Next WS





WB.Protect Password:="BASA"
WB.Save
WB.Close False
xlApp.Quit

Set xlApp = Nothing
Set WB = Nothing
Set WS = Nothing
Set XLrange = Nothing

 

[combo]

Try with excel visible, check if you have proper variables, work directly with WB:

[tt]Set xlApp = New Excel.Application
xlApp.Visible = True ' make excel instance visible
Dim Z As Long
Set WB = xlApp.Workbooks.Open("C:\Files\Test\WK41_FCST_COUNTRY.xlsx")

For Each WS In WB.Worksheets 'replace ActiveWorkbook by WB, workbook hasn't 'Worksheets' property
MsgBox WS.Name ' check WB by displaying its name
WS.Protect ' protect without password first.
Next WS[/tt]

If this works, succesively add password and remove visibility.

[tt]WB.Protect Password:="BASA"[/tt] does not add any protection. For workbook, you have to set protection parameter to true (

https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbook.Protect),

otherwise both are False.

If you want to add file protection password use SaveAs with password (

https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas)

combo

 

[Knicks]

I was able to see the 2 sheet names as it looped thourgh. It fails at

WS.Protect

And generates a Run Time Error '91' Object Variable or Block Variable not set. This is a different error than I have gotten in the past

 

[combo]

Does your code compile without errors?

Is there any code between the two test lines, assuming no changes in WB?:
[pre]MsgBox WS.Name
WS.Protect[/pre]

Does this peace of code works:
[pre]For Each WS In WB.Worksheets
WSname = WS.Name
MsgBox WS.Name
WB.Worksheets(WSname).Protect
Next WS[/pre]

It is not a reason, but you could declare excel variables with library name:
[tt]Dim WS As Excel.Worksheet
Dim XLrange As Excel.Range[/tt]

All remarks refer to fresh excel file without password, generated by two DoCmd.TransferSpreadsheet actions.


combo