TransferSpreadsheet with password protected workbook

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.


LVL 17
Who is Participating?
moduloConnect With a Mentor Commented:
Closed, 500 points refunded.

Community Support Moderator
Experts Exchange
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
walterecookAuthor Commented:
Thanks for the effort, but I've already got my arms around that.  I was kind of looking for some specifics.

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

walterecookAuthor Commented:
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
walterecookAuthor Commented:
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 ?


walterecookAuthor Commented:
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.



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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.