access data from a closed wb protected with password without openi

[Guest]

Hi
i am new to Excel programming and not sure if i am doing somthing stupid
i wanted to average the data from several closed wb into a single cell of
open wb. i am using the following formula in the cell and it woks fine if the
wb are not protected.

=AVERAGE('C:\Haresh\[trial.xls]Sheet1!B10,'C:\Haresh\[Courses.xls]Sheet1!E10)

if the wb are protected then it asks for password for each of the closed wb.
the problem is the no. of closed wb from which i may need to copy data may be
10 15 20 or more which means the user will have to enter password for every
wb before the result appears in that cell.

is ther a way to put password directly in the formula or

i know that i can open the closed wb using macro and also provide password
automatically and access the data required and then close the wb but this
flashes the wbwhich i dont really want. is there a way to access the closed
wb through a macro and providing the password automatically without having to
open the closed workbook


Thanks for help in advance!

 

[Dave Peterson]

Sometimes, you can stop the flashing of the workbooks opening with:

application.screenupdating = false
'do lots of work
application.screenupdating = true

You may have to disable events when you open the other workbooks, too. They
could do something that toggles that setting.

Hi
i am new to Excel programming and not sure if i am doing somthing stupid
i wanted to average the data from several closed wb into a single cell of
open wb. i am using the following formula in the cell and it woks fine if the
wb are not protected.

=AVERAGE('C:\Haresh\[trial.xls]Sheet1!B10,'C:\Haresh\[Courses.xls]Sheet1!E10)

if the wb are protected then it asks for password for each of the closed wb.
the problem is the no. of closed wb from which i may need to copy data may be
10 15 20 or more which means the user will have to enter password for every
wb before the result appears in that cell.

is ther a way to put password directly in the formula or

i know that i can open the closed wb using macro and also provide password
automatically and access the data required and then close the wb but this
flashes the wbwhich i dont really want. is there a way to access the closed
wb through a macro and providing the password automatically without having to
open the closed workbook

Thanks for help in advance!

 

[Guest]

Hi dave just returned from sickness and Thanks very much for reply. i have
tried your code and its worked fine but there another samll prob. the wb that
opens does not flash on the screen but it shows in the tool bar at the time
of opening. is there a way get rid of this as well. actually i am trying to
import some data from several closed wb and each wb is named after differnt
users name and i dont want the user to get the impression that i am opening
the wb with their name as it flashes in the toolbar.

any help will be much appreciated!
Thanks again

Haresh

Sometimes, you can stop the flashing of the workbooks opening with:

application.screenupdating = false
'do lots of work
application.screenupdating = true

You may have to disable events when you open the other workbooks, too. They
could do something that toggles that setting.

Hi
i am new to Excel programming and not sure if i am doing somthing stupid
i wanted to average the data from several closed wb into a single cell of
open wb. i am using the following formula in the cell and it woks fine if the
wb are not protected.

=AVERAGE('C:\Haresh\[trial.xls]Sheet1!B10,'C:\Haresh\[Courses.xls]Sheet1!E10)

if the wb are protected then it asks for password for each of the closed wb.
the problem is the no. of closed wb from which i may need to copy data may be
10 15 20 or more which means the user will have to enter password for every
wb before the result appears in that cell.

is ther a way to put password directly in the formula or

i know that i can open the closed wb using macro and also provide password
automatically and access the data required and then close the wb but this
flashes the wbwhich i dont really want. is there a way to access the closed
wb through a macro and providing the password automatically without having to
open the closed workbook

Thanks for help in advance!

 

[Dave Peterson]

Maybe you could hide that workbook's window.

dim wkbk as workbook
dim myWin as window

set wkbk = workbooks.open(...)
for each mywin in wkbk.windows
mywin.visible = false
next mywin

don't save that workbook with the windows hidden. It may be confusing the next
time you open the workbook.

Hi dave just returned from sickness and Thanks very much for reply. i have
tried your code and its worked fine but there another samll prob. the wb that
opens does not flash on the screen but it shows in the tool bar at the time
of opening. is there a way get rid of this as well. actually i am trying to
import some data from several closed wb and each wb is named after differnt
users name and i dont want the user to get the impression that i am opening
the wb with their name as it flashes in the toolbar.

any help will be much appreciated!
Thanks again

Haresh

Sometimes, you can stop the flashing of the workbooks opening with:

application.screenupdating = false
'do lots of work
application.screenupdating = true

You may have to disable events when you open the other workbooks, too. They
could do something that toggles that setting.

Hi
i am new to Excel programming and not sure if i am doing somthing stupid
i wanted to average the data from several closed wb into a single cell of
open wb. i am using the following formula in the cell and it woks fine if the
wb are not protected.

=AVERAGE('C:\Haresh\[trial.xls]Sheet1!B10,'C:\Haresh\[Courses.xls]Sheet1!E10)

if the wb are protected then it asks for password for each of the closed wb.
the problem is the no. of closed wb from which i may need to copy data may be
10 15 20 or more which means the user will have to enter password for every
wb before the result appears in that cell.

is ther a way to put password directly in the formula or

i know that i can open the closed wb using macro and also provide password
automatically and access the data required and then close the wb but this
flashes the wbwhich i dont really want. is there a way to access the closed
wb through a macro and providing the password automatically without having to
open the closed workbook

Thanks for help in advance!

 

[Guest]

Hi Dave
i tried using this code and its still flashing in the taskbar when the wb
opens(sorry i mentioned toolbar last time) . is there anyting else i can do
to resolve this

regards
Haresh

Maybe you could hide that workbook's window.

dim wkbk as workbook
dim myWin as window

set wkbk = workbooks.open(...)
for each mywin in wkbk.windows
mywin.visible = false
next mywin

don't save that workbook with the windows hidden. It may be confusing the next
time you open the workbook.

Hi dave just returned from sickness and Thanks very much for reply. i have
tried your code and its worked fine but there another samll prob. the wb that
opens does not flash on the screen but it shows in the tool bar at the time
of opening. is there a way get rid of this as well. actually i am trying to
import some data from several closed wb and each wb is named after differnt
users name and i dont want the user to get the impression that i am opening
the wb with their name as it flashes in the toolbar.

any help will be much appreciated!
Thanks again

Haresh

Sometimes, you can stop the flashing of the workbooks opening with:

application.screenupdating = false
'do lots of work
application.screenupdating = true

You may have to disable events when you open the other workbooks, too. They
could do something that toggles that setting.



Haresh wrote:

Hi
i am new to Excel programming and not sure if i am doing somthing stupid
i wanted to average the data from several closed wb into a single cell of
open wb. i am using the following formula in the cell and it woks fine if the
wb are not protected.

=AVERAGE('C:\Haresh\[trial.xls]Sheet1!B10,'C:\Haresh\[Courses.xls]Sheet1!E10)

if the wb are protected then it asks for password for each of the closed wb.
the problem is the no. of closed wb from which i may need to copy data may be
10 15 20 or more which means the user will have to enter password for every
wb before the result appears in that cell.

is ther a way to put password directly in the formula or

i know that i can open the closed wb using macro and also provide password
automatically and access the data required and then close the wb but this
flashes the wbwhich i dont really want. is there a way to access the closed
wb through a macro and providing the password automatically without having to
open the closed workbook

Thanks for help in advance!

 

[Dave Peterson]

Maybe you can turn off that option (temporarily).

(Untested, uncompiled).

Dim WinInTB As Boolean
WinInTB = Application.ShowWindowsInTaskbar
Application.ShowWindowsInTaskbar = False
'open the other workbook and hide all the windows
'turn it back on
Application.ShowWindowsInTaskbar = WinInTB

But that could cause different flashing.

There are API calls that can freeze your screen so nothing moves. But if
something goes wrong, it's reboot time. I wouldn't use them. I'd live with
minor flashing.

At the top of the module:

Declare Function LockWindowUpdate Lib _
"user32" (ByVal hwndLock As Long) As Long
Declare Function FindWindowA Lib _
"user32" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

In your code:

Sub whatever()
'do stuff

'freeze the screen
hWnd = FindWindowA("XLMAIN", Application.Caption)
LockWindowUpdate hWnd

'do more stuff

'unfreeze the screen
LockWindowUpdate 0

'do more stuff

End sub

Remember to save your work often (in all open applications!) if you use this.
You may be rebooting more than you want. (I wouldn't use this--but I've said
this before.)

Hi Dave
i tried using this code and its still flashing in the taskbar when the wb
opens(sorry i mentioned toolbar last time) . is there anyting else i can do
to resolve this

regards
Haresh

Maybe you could hide that workbook's window.

dim wkbk as workbook
dim myWin as window

set wkbk = workbooks.open(...)
for each mywin in wkbk.windows
mywin.visible = false
next mywin

don't save that workbook with the windows hidden. It may be confusing the next
time you open the workbook.

Hi dave just returned from sickness and Thanks very much for reply. i have
tried your code and its worked fine but there another samll prob. the wb that
opens does not flash on the screen but it shows in the tool bar at the time
of opening. is there a way get rid of this as well. actually i am trying to
import some data from several closed wb and each wb is named after differnt
users name and i dont want the user to get the impression that i am opening
the wb with their name as it flashes in the toolbar.

any help will be much appreciated!
Thanks again

Haresh

:

Sometimes, you can stop the flashing of the workbooks opening with:

application.screenupdating = false
'do lots of work
application.screenupdating = true

You may have to disable events when you open the other workbooks, too. They
could do something that toggles that setting.



Haresh wrote:

Hi
i am new to Excel programming and not sure if i am doing somthing stupid
i wanted to average the data from several closed wb into a single cell of
open wb. i am using the following formula in the cell and it woks fine if the
wb are not protected.

=AVERAGE('C:\Haresh\[trial.xls]Sheet1!B10,'C:\Haresh\[Courses.xls]Sheet1!E10)

if the wb are protected then it asks for password for each of the closed wb.
the problem is the no. of closed wb from which i may need to copy data may be
10 15 20 or more which means the user will have to enter password for every
wb before the result appears in that cell.

is ther a way to put password directly in the formula or

i know that i can open the closed wb using macro and also provide password
automatically and access the data required and then close the wb but this
flashes the wbwhich i dont really want. is there a way to access the closed
wb through a macro and providing the password automatically without having to
open the closed workbook

Thanks for help in advance!

 

[Dave Peterson]

I pasted that code that uses the API call from the wrong saved message.

It looks like that previous posted message locks just excel--not the desktop
(including the taskbar).

Here's one that locks the entire desktop:

Option Explicit
Private Declare Function LockWindowUpdate Lib "USER32" _
(ByVal hwndLock As Long) As Long
Private Declare Function GetDesktopWindow Lib "USER32" () As Long
Sub WindowUpdating(Enabled As Boolean)

'Completely Locks the Whole Application Screen Area,
'including dialogs and the mouse.

Dim Res As Long

If Enabled Then
LockWindowUpdate 0
'Unlock screen area
Else
Res = LockWindowUpdate(GetDesktopWindow)
'Lock at desktop level
End If

End Sub

Sub testme01()
Call WindowUpdating(False)
'do the work
Call WindowUpdating(True)
End Sub

Hi Dave
i tried using this code and its still flashing in the taskbar when the wb
opens(sorry i mentioned toolbar last time) . is there anyting else i can do
to resolve this

regards
Haresh

Maybe you could hide that workbook's window.

dim wkbk as workbook
dim myWin as window

set wkbk = workbooks.open(...)
for each mywin in wkbk.windows
mywin.visible = false
next mywin

don't save that workbook with the windows hidden. It may be confusing the next
time you open the workbook.

Hi dave just returned from sickness and Thanks very much for reply. i have
tried your code and its worked fine but there another samll prob. the wb that
opens does not flash on the screen but it shows in the tool bar at the time
of opening. is there a way get rid of this as well. actually i am trying to
import some data from several closed wb and each wb is named after differnt
users name and i dont want the user to get the impression that i am opening
the wb with their name as it flashes in the toolbar.

any help will be much appreciated!
Thanks again

Haresh

:

Sometimes, you can stop the flashing of the workbooks opening with:

application.screenupdating = false
'do lots of work
application.screenupdating = true

You may have to disable events when you open the other workbooks, too. They
could do something that toggles that setting.



Haresh wrote:

Hi
i am new to Excel programming and not sure if i am doing somthing stupid
i wanted to average the data from several closed wb into a single cell of
open wb. i am using the following formula in the cell and it woks fine if the
wb are not protected.

=AVERAGE('C:\Haresh\[trial.xls]Sheet1!B10,'C:\Haresh\[Courses.xls]Sheet1!E10)

if the wb are protected then it asks for password for each of the closed wb.
the problem is the no. of closed wb from which i may need to copy data may be
10 15 20 or more which means the user will have to enter password for every
wb before the result appears in that cell.

is ther a way to put password directly in the formula or

i know that i can open the closed wb using macro and also provide password
automatically and access the data required and then close the wb but this
flashes the wbwhich i dont really want. is there a way to access the closed
wb through a macro and providing the password automatically without having to
open the closed workbook

Thanks for help in advance!