[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


TransferSpreadsheet with password protected workbook

Posted on 2004-04-26
Medium Priority
Last Modified: 2008-02-01
Dear experts
I need to use the transferspreadsheet method to import information from excel but the hitch is that the file is password protected.
I do not wish to clear the file's password, rather simply be able to access the file to grab the data leaving the password in tact.

I may have worked with VBA once or twice, so that's alright.  I'm not caffeinated yet today and am having a little trouble fiddling around with the Excel objects.

Thank you.


Question by:walterecook
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 65

Expert Comment

ID: 10919482
not sure, how about this

open excel as an object,
clear password
reset password
close object

not sure how easy or if possible to do but its just a thought

problem is then having to keep track of passwords in VBA code
LVL 17

Author Comment

ID: 10919937
Thanks for the effort, but I've already got my arms around that.  I was kind of looking for some specifics.

LVL 17

Author Comment

ID: 10920068
I ended up opening an object, saving it as something else without the password, transferring off the new workbook, then killing it.

Private Sub txtImportEOD_Click()

' requires reference to MS Excel Object Library
Dim password1, password2
Dim myXL As New Excel.Application

password1 = DLookup("FilePassword", "tblPassword", "[passwordID] = 1")
password2 = DLookup("FilePassword", "tblPassword", "[passwordID] = 2")

Dim myWkb As Workbook
myXL.Visible = True
Set myWkb = myXL.Workbooks.Open(Me.txtImportLocation_EOD, , , , password1)

'if saveas file exists, delete it first
If Dir(Me.txtImportLocation_EOD & "Unlocked.xls") <> "" Then _
    Kill Me.txtImportLocation_EOD & "Unlocked.xls"
myWkb.SaveAs Me.txtImportLocation_EOD & "Unlocked.xls", , ""

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "tblEOD_ImportTemp", Me.txtImportLocation_EOD & "Unlocked.xls", True

Set myWkb = Nothing
Set myXL = Nothing
Kill Me.txtImportLocation_EOD & "Unlocked.xls"
End Sub
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 17

Author Comment

ID: 10920239

Expert Comment

ID: 10920293
A more easy approach would be opening the excel workbook via
ADO. There you can state the password  for reading the data.
And then only some code is required to copy the required fields and records, cause you have already two recordsets: your table in Access, and the spreadsheet also as a recordset.
I don't know how sensible your data is, but it sure isn't password protected without reason. And now you save it unsecured and delete it later in a manner where it easily can be restored ?


LVL 17

Author Comment

ID: 10920708
Interesting points.  ADO IS a consideration.  I was kind of looking to avoid recordset transfers mostly because a) I'm lazy b) don't really have time when transferSpreadsheet is so readily available.  :)  I may look in to that anyway.
Understood on the data security aspect.  Actually Kill doesn't even leave a copy in the recycle bin.  Sure, you could always recover from the hard drive itself, but the data in this case, well quite frankly, I don't know why it was protected in the first place.



Expert Comment

ID: 10921329

your'e welcome. If you do have questions about the ado thing - never mind asking!



Accepted Solution

modulo earned 0 total points
ID: 10962372
Closed, 500 points refunded.

Community Support Moderator
Experts Exchange

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question