• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

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.

Walt


0
walterecook
Asked:
walterecook
1 Solution
 
rockiroadsCommented:
not sure, how about this

open excel as an object,
clear password
transferspreadsheet
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
0
 
walterecookAuthor Commented:
Thanks for the effort, but I've already got my arms around that.  I was kind of looking for some specifics.

Walt
0
 
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

myWkb.Close
Set myWkb = Nothing
Set myXL = Nothing
Kill Me.txtImportLocation_EOD & "Unlocked.xls"
End Sub
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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


Greetings

Andy
0
 
walterecookAuthor Commented:
IT,
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.

Walt


0
 
IT-SchubertzCommented:
Walt,

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

Greetings

Andy
0
 
moduloCommented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now