Question Import data from Password Protected Spreadsheet (1 Viewer)

rkrause

Registered User.
Local time
Today, 06:51
Joined
Sep 7, 2007
Messages
343
I posted this in the Macro Board and havent had any luck. heres my issue:

I have a spreadsheet located on our network called dps2006.xls. This sheet is password protected. I want to know how i can import data from a tab called cream summary. i havent had any luck doing it. i can get non password protected to work fine.
 

DJkarl

Registered User.
Local time
Today, 08:51
Joined
Mar 16, 2007
Messages
1,028
If you are trying to use the TransferSpreadsheet then you cannot do that on a password protected workbook. You need to use Excel automation to import the data, or at least to remove the password then use TransferSpreadsheet.

There are tons of examples on this forum, MSDN, and google on how to do Excel Automation from Access. Good luck!
 

rkrause

Registered User.
Local time
Today, 06:51
Joined
Sep 7, 2007
Messages
343
Ok, geting a little farther. where do i put this code in access, and how do i run it?
 

DJkarl

Registered User.
Local time
Today, 08:51
Joined
Mar 16, 2007
Messages
1,028
You could put it many places (there's a joke there somewhere). The most common place is a public module in the database, for more advanced users that do a lot of Excel Automation putting your code into a class module is what I would recommend.

To run it you could call it from a macro, run it from the immediate window, create a form and call it from a button click, etc...
 

rkrause

Registered User.
Local time
Today, 06:51
Joined
Sep 7, 2007
Messages
343
do you have any sample code for what im trying to do? i cant find anything similar for what im trying to do, specially to get data from the password protected file.
 

DJkarl

Registered User.
Local time
Today, 08:51
Joined
Mar 16, 2007
Messages
1,028
do you have any sample code for what im trying to do? i cant find anything similar for what im trying to do, specially to get data from the password protected file.

There are examples out there, the password is part of the Workbooks.Open method.

Code:
Sub OpenPwdProtFile()
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("FileName", , , , "PassWord")
wb.Close
xlApp.Quit
Set xlApp = Nothing
End Sub
 

rkrause

Registered User.
Local time
Today, 06:51
Joined
Sep 7, 2007
Messages
343
when i run this code i get an error on the 2nd line on the DIM xlApp as Excel.Application

Sub OpenPwdProtFile()Dim xlApp As Excel.ApplicationDim wb As Excel.WorkbookSet xlApp = New Excel.ApplicationSet wb = xlApp.Workbooks.Open("FileName", , , , "PassWord")wb.ClosexlApp.QuitSet xlApp = NothingEnd Sub
 

DJkarl

Registered User.
Local time
Today, 08:51
Joined
Mar 16, 2007
Messages
1,028
You most likely need to add a reference to Excel. If you need to know how to do that, Tools->References, find Microsoft Excel and select it, then click OK.
 

rkrause

Registered User.
Local time
Today, 06:51
Joined
Sep 7, 2007
Messages
343
im using access 07, im not seeing a tools menu?
 

rkrause

Registered User.
Local time
Today, 06:51
Joined
Sep 7, 2007
Messages
343
nevermind i found it, but its grayed out. this is in the VBA screen correct
 

rkrause

Registered User.
Local time
Today, 06:51
Joined
Sep 7, 2007
Messages
343
Ok i am able to get this to work heres my code. my issue is, i cant get the file to stay open, it goes right into a close, and which i see in the code, what can i use to get it to stay open? if i ake out the wb.close, and xlapp.quit it wont do anything.




Sub OpenPwdProtFile()
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("Y:\Accounting\DPS\DPS2006.xls", , , , "dps06")
wb.Close
xlApp.Quit
Set xlApp = Nothing
End Sub
 

DJkarl

Registered User.
Local time
Today, 08:51
Joined
Mar 16, 2007
Messages
1,028
I was trying to give you a sample of how to open a password protected file, not to do your project for you. The code was not intended to be complete. Please try to put forth some of your own effort, the internet is FULL of Excel Automation examples using VBA. I don't mind helping, but giving out fully developed solutions is a little above the scope of helping.
 

Users who are viewing this thread

Top Bottom