Solved

TransferSpreadsheet with password protected workbook

Posted on 2004-04-26
9
865 Views
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.

Walt


0
Comment
Question by:walterecook
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10919482
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
 
LVL 17

Author Comment

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

Walt
0
 
LVL 17

Author Comment

by:walterecook
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

myWkb.Close
Set myWkb = Nothing
Set myXL = Nothing
Kill Me.txtImportLocation_EOD & "Unlocked.xls"
End Sub
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 17

Author Comment

by:walterecook
ID: 10920239
0
 
LVL 7

Expert Comment

by:IT-Schubertz
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 ?


Greetings

Andy
0
 
LVL 17

Author Comment

by:walterecook
ID: 10920708
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
 
LVL 7

Expert Comment

by:IT-Schubertz
ID: 10921329
Walt,

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

Greetings

Andy
0
 

Accepted Solution

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

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

820 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