将Access查询导出到受密码保护的Excel工作簿 [英] Export Access queries to password-protected Excel workbook

问题描述

我正在尝试将2个查询从Access数据库导出到Excel工作簿中的2个标签中。我希望该工作簿受密码保护。我很难在VBA中设置密码保护。到目前为止，这是我的VBA：

I'm attempting to export 2 queries from an Access database to 2 tabs in an Excel workbook. I want that workbook to be password-protected. I'm having difficulty making it password protected in VBA. Here's my VBA so far:

 Public Function ExportToExcel()



Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:="q1_Get_Load_Data", _
                                   FileName:="C:\Users\...\POPs_Reports.xlsx")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:="q2_Number_by_Alpha", _
                                   FileName:="C:\Users\...\POPs_Reports.xlsx")


End Function

是否可以添加

推荐答案

这是我用来保护6种不同工作簿中40张纸的代码：

This is the code I use to protect 40 sheets in 6 different workbooks:

Sub protectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
    ActiveSheet.Protect "the_password", True, True
    If i = myCount Then
        End
    End If
    ActiveSheet.Next.Select
Next i

End Sub
Sub UnprotectAll（）
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets（1）。选择
For i = 1到myCount
ActiveSheet。取消保护 the_password
如果i = myCount然后
然后
End If
ActiveSheet.Next.Select
Next i
End Sub

End Sub Sub UnprotectAll() Dim myCount Dim i myCount = Application.Sheets.Count Sheets(1).Select For i = 1 To myCount ActiveSheet.Unprotect "the_password" If i = myCount Then End End If ActiveSheet.Next.Select Next i End Sub

因此，对于要访问的单元格即未受保护，请选择单元格（或多个单元格），然后格式化单元格并取消勾选锁定复选框，（在备用纸上对其进行测试！）请参见：

So, for the cells you want access to i.e. not protected then select the cell (or cells) and go to format cells and un-tick the "locked" checkbox, (test it on a spare sheet!) see :

不要保护一张纸-将它放在位置1并从2开始循环...

To not protect one sheet - have it at position 1 and start the loop at 2...

这篇关于将Access查询导出到受密码保护的Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！