35105

Export Access queries to password-protected Excel workbook

Question:

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

Is there a way to add code to make the workbook password protected?

Answer1:

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).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 :

<a href="https://i.stack.imgur.com/5jrgB.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/5jrgB.png" data-original="https://i.stack.imgur.com/5jrgB.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

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

Recommend

  • wso2: Can't login to correct publisher tenant
  • Homework: Sublist? checking if an item is a sublist of the first one
  • process is failing in ptrgl_64._ptrgl functionon aix
  • Preserve link to Excel when name of worksheet changes
  • MySQL JOIN by SELECT earliest DATETIME
  • How can I find the id of a gang in OpenACC?
  • Can to do one query from two queries?
  • Is there any better way to write this query
  • Scraping data from the tag names in python
  • 1:1 Alignment of C# Structs with Streamed Delphi Records Possible?
  • How do I scroll HTML textbox to the end?
  • docmd.transferspreadsheet incorrectly assigning Data Type
  • Iteration over the dictionary and extracting values
  • how to get href link from onclick function in python
  • Making cut/replace methods with JTextArea
  • Foreign key constraint error when adding through migrating
  • Excluding some combinations from left join in MySQL
  • Space between buttons and window
  • PHP MYSQL event listener
  • Converting Uint8Array crashing browser for large files
  • vim tabular plugin question
  • Ng-Repeat: Filter By Uniqueness
  • Workflow - Database Design
  • msbuild create itemgroup from property group
  • List images(01.png) and descriptions(01.txt) from directory
  • cygwin cannot exec 'git-add--interactive' permission denied
  • how to upload multiple files in c# windows application
  • MS Access - How to change the linked table path by amend the table
  • Ajax calls do not work in IE unless you fiddle with security settings
  • Alternative to overridePendingTransition() - Android
  • Rails Find when some params will be blank
  • MongoError: Incorrect arguments
  • C++ Partial template specialization - design simplification
  • How to install a .deb file on a jailbroken iphone programmatically?
  • Handling un-mapped Rest path
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • Cassandra Data Model
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • reshape alternating columns in less time and using less memory