open Excel file again and again
 

I cannot find any hint in the VBA help files nor in expert books how to get
access to worksheet data in another Excel file without displaying window of
that file on the screen. I have just found a workaround using legacy Office95
macros or so.

I have written an application consolidating data from many Excel files and
it is frustrating for the users to see windows of the files rapidly poping up
and closing on their screen.

I tried minimizing the window just after file open command etc. but still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter

open Excel file again and again
 

Hello Peter
Try:
Application.ScreenUpdating = False
'your code
Application.ScreenUpdating = True

HTH
Cordially
Pascal

"Peter K. Livingston" a écrit
dans le message de news:
...
I cannot find any hint in the VBA help files nor in expert books how to get
access to worksheet data in another Excel file without displaying window
of
that file on the screen. I have just found a workaround using legacy
Office95
macros or so.

I have written an application consolidating data from many Excel files and
it is frustrating for the users to see windows of the files rapidly poping
up
and closing on their screen.

I tried minimizing the window just after file open command etc. but still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter

open Excel file again and again
 

If(?) your workbooks are already open them you do not need to activate
workbook and sheet to get the data

dim sBook as string
Dim sSheet as string
Dim rng as range
dim arrData As Variant

sBook = "SomeBook.xls"
sSheet = "Sheet1"

Set rng = Workbooks(sBook).Worksheets(sSheet).Range("A1:B10" )
arrData = rng.Value

Your data is now in an array (assuming the range range's area size is 2+
cells)

Regards,
Peter T




"Peter K. Livingston" wrote in
message ...
I cannot find any hint in the VBA help files nor in expert books how to
get
access to worksheet data in another Excel file without displaying window
of
that file on the screen. I have just found a workaround using legacy
Office95
macros or so.

I have written an application consolidating data from many Excel files and
it is frustrating for the users to see windows of the files rapidly poping
up
and closing on their screen.

I tried minimizing the window just after file open command etc. but still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter

open Excel file again and again
 

You should not have to open any extra files to get their data.

Say we want the data in cell A1 in file

C:\temp2\b.xls

Run the following:

Sub marine()
Range("B9").Formula = "='C:\temp2\[b.xls]b'!$A$1"
Application.CalculateFullRebuild
MsgBox (Range("B9").Value)
End Sub

Basically we are using cell B9 as a "helper" cell. We:

1. insert a formula to retrieve the value from the closed workbook
2. calculate the formula
3. display the result

--
Gary''s Student - gsnu200777


"Peter K. Livingston" wrote:

I cannot find any hint in the VBA help files nor in expert books how to get
access to worksheet data in another Excel file without displaying window of
that file on the screen. I have just found a workaround using legacy Office95
macros or so.

I have written an application consolidating data from many Excel files and
it is frustrating for the users to see windows of the files rapidly poping up
and closing on their screen.

I tried minimizing the window just after file open command etc. but still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter

open Excel file again and again
 

Peter T, Thnx for your advice, but the files are not open!

Actualy this is the point:
there is no switch in open method to say "open minimized", or "open hidden".
When I open the file, Excel creates a new window for it and displays it.

Thanks a lot,

Peter

"Peter T" wrote:

If(?) your workbooks are already open them you do not need to activate
workbook and sheet to get the data

dim sBook as string
Dim sSheet as string
Dim rng as range
dim arrData As Variant

sBook = "SomeBook.xls"
sSheet = "Sheet1"

Set rng = Workbooks(sBook).Worksheets(sSheet).Range("A1:B10" )
arrData = rng.Value

Your data is now in an array (assuming the range range's area size is 2+
cells)

Regards,
Peter T




"Peter K. Livingston" wrote in
message ...
I cannot find any hint in the VBA help files nor in expert books how to
get
access to worksheet data in another Excel file without displaying window
of
that file on the screen. I have just found a workaround using legacy
Office95
macros or so.

I have written an application consolidating data from many Excel files and
it is frustrating for the users to see windows of the files rapidly poping
up
and closing on their screen.

I tried minimizing the window just after file open command etc. but still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter

open Excel file again and again
 

It looks extremely innovative and interesting. I will keep it for the future,
as it does not fully solve my current task.
I fact it is very simple: I need to work with the other workbooks in full
without having it displayed on the screen! I guess a kind of option like
"open minimized" or "open hidden" will help, if it exists with the Open
method.

Thanks a much anyhow,

Peter

"Gary''s Student" wrote:

You should not have to open any extra files to get their data.

Say we want the data in cell A1 in file

C:\temp2\b.xls

Run the following:

Sub marine()
Range("B9").Formula = "='C:\temp2\[b.xls]b'!$A$1"
Application.CalculateFullRebuild
MsgBox (Range("B9").Value)
End Sub

Basically we are using cell B9 as a "helper" cell. We:

1. insert a formula to retrieve the value from the closed workbook
2. calculate the formula
3. display the result

--
Gary''s Student - gsnu200777


"Peter K. Livingston" wrote:

I cannot find any hint in the VBA help files nor in expert books how to get
access to worksheet data in another Excel file without displaying window of
that file on the screen. I have just found a workaround using legacy Office95
macros or so.

I have written an application consolidating data from many Excel files and
it is frustrating for the users to see windows of the files rapidly poping up
and closing on their screen.

I tried minimizing the window just after file open command etc. but still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter

open Excel file again and again
 

Thanx a lot, I will definitely test this workaround asap.

all the best,

Peter

"papou" wrote:

Hello Peter
Try:
Application.ScreenUpdating = False
'your code
Application.ScreenUpdating = True

HTH
Cordially
Pascal

"Peter K. Livingston" a écrit
dans le message de news:
...
I cannot find any hint in the VBA help files nor in expert books how to get
access to worksheet data in another Excel file without displaying window
of
that file on the screen. I have just found a workaround using legacy
Office95
macros or so.

I have written an application consolidating data from many Excel files and
it is frustrating for the users to see windows of the files rapidly poping
up
and closing on their screen.

I tried minimizing the window just after file open command etc. but still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter

open Excel file again and again
 

In that case go with Pascal's suggestion. You could also include something
like this -

Dim wbOrig as workbook

On error resume next
Set wbOrig = ActiveWorkbook
On Error Goto 0 ' or other error handling

Application.ScreenUpdating = False

' code to open other workbooks and do anything else that'll cause screen
flicker

' re-activate the original wb
If not wbOrig Is Nothing Then
wbOrig.Activate
End If
Application.ScreenUpdating = True

Regards,
Peter T


"Peter K. Livingston" wrote in
message ...
Peter T, Thnx for your advice, but the files are not open!

Actualy this is the point:
there is no switch in open method to say "open minimized", or "open
hidden".
When I open the file, Excel creates a new window for it and displays it.

Thanks a lot,

Peter

"Peter T" wrote:

If(?) your workbooks are already open them you do not need to activate
workbook and sheet to get the data

dim sBook as string
Dim sSheet as string
Dim rng as range
dim arrData As Variant

sBook = "SomeBook.xls"
sSheet = "Sheet1"

Set rng = Workbooks(sBook).Worksheets(sSheet).Range("A1:B10" )
arrData = rng.Value

Your data is now in an array (assuming the range range's area size is 2+
cells)

Regards,
Peter T




"Peter K. Livingston" wrote
in
message ...
I cannot find any hint in the VBA help files nor in expert books how
to
get
access to worksheet data in another Excel file without displaying
window
of
that file on the screen. I have just found a workaround using legacy
Office95
macros or so.

I have written an application consolidating data from many Excel files
and
it is frustrating for the users to see windows of the files rapidly
poping
up
and closing on their screen.

I tried minimizing the window just after file open command etc. but
still
having big mess on the screen.

You VBA masters, please, advise.

Thnx,

Peter