TransferSpreadsheet with password protected workbook

Posted on 2004-04-26
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
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
LVL 17

Author Comment

ID: 10920239
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I name a sub macro so Shift/F10 would work. 3 23
MS Access 03, TransferText, decimal places 8 47
Access coding 2 15
access pop-up form 3 9
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

864 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now