Pivot table - connection to Excel file with password

[IgorM]

Hi

How can I set up a connection to data stored in another Excel file that is
protected with password. The password in known of course.
I reveive an error while trying to do that.

Kind regards
Igor

 

[DILipandey]

Hi Igor,

Here is the solution:-
Suppose you want to make pivot using the data file which is placed at
following location:-
C:\Documents and Settings\DilPrati\Desktop\data.xls
Now, when you are doing pivot table, you just need to type following in the
"range":-
C:\Documents and Settings\DilPrati\Desktop\[Data.xls]Sheet1!$A:$A.

Here, data which is there in the column A of the workbook "Data.xls" is used
to create the pivot table in another excel workbook.

You just need to watch out for the workbook name and the sheet name format.
I am having a dummy file as well, let me know if you need that as well.
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
[email protected]
[email protected]
New Delhi, India

 

[IgorM]

Hi Dilip

Thanks for response. Unfortunetely the solution given did not resolve my
problem.
I'll try to explain once again. I have one Excel file that stores some data.
It is saved with password (to open and make changes in it). So basically if
you would want to open it in excel to see the data you would have to provide
passoword. But instead I want to make a pivot table in another Excel file
using this data. I know how to do that when the source file is not protected
with password, but when it is the connection cannot be established. I tried
to enter the password in data connection properties but I think the password
is intended for connection to servers as it requires the user to provide
user name too.
I tried to edit the connection string, but did not come up with a proper
property for password.

Regards
Igor

Hi Igor,

Here is the solution:-
Suppose you want to make pivot using the data file which is placed at
following location:-
C:\Documents and Settings\DilPrati\Desktop\data.xls
Now, when you are doing pivot table, you just need to type following in
the
"range":-
C:\Documents and Settings\DilPrati\Desktop\[Data.xls]Sheet1!$A:$A.

Here, data which is there in the column A of the workbook "Data.xls" is
used
to create the pivot table in another excel workbook.

You just need to watch out for the workbook name and the sheet name
format.
I am having a dummy file as well, let me know if you need that as well.
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
[email protected]
[email protected]
New Delhi, India

Hi

How can I set up a connection to data stored in another Excel file that
is
protected with password. The password in known of course.
I reveive an error while trying to do that.

Kind regards
Igor

 

[David Heaton]

Hi Dilip

Thanks for response. Unfortunetely the solution given did not resolve my
problem.
I'll try to explain once again. I have one Excel file that stores some data.
It is saved with password (to open and make changes in it). So basically if
you would want to open it in excel to see the data you would have to provide
passoword. But instead I want to make a pivot table in another Excel file
using this data. I know how to do that when the source file is not protected
with password, but when it is the connection cannot be established. I tried
to enter the password in data connection properties but I think the password
is intended for connection to servers as it requires the user to provide
user name too.
I tried to edit the connection string, but did not come up with a proper
property for password.

Regards
Igor

U¿ytkownik "DILipandey" <[email protected]> napisa³ w wiadomo¶ci

Hi Igor,

Here is the solution:-
Suppose you want to make pivot using the data file which is placed at
following location:-
C:\Documents and Settings\DilPrati\Desktop\data.xls
Now, when you are doing pivot table, you just need to type following in
the
"range":-
C:\Documents and Settings\DilPrati\Desktop\[Data.xls]Sheet1!$A:$A.

Here, data which is there in the column A of the workbook "Data.xls" is
used
to create the pivot table in another excel workbook.

You just need to watch out for the workbook name and the sheet name
format.
I am having a dummy file as well, let me know if you need that as well.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
[email protected]
[email protected]
New Delhi, India

"IgorM" wrote:

- Show quoted text -

Try this.


Go to the Data menu,
select Pivot Table and Chart
Choose Microsoft Office Excel List of Database, click Next.
Click on the browse button and select the file you want. ( add the
data range after the ! )
Click finish.

Excel should now ask you for the password you set when saving your
data sheet.

hth

regards


David