open Excel file again and again

  • Thread starter Thread starter Peter K. Livingston
  • Start date Start date
P

Peter K. Livingston

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
 
Hello Peter
Try:
Application.ScreenUpdating = False
'your code
Application.ScreenUpdating = True

HTH
Cordially
Pascal

"Peter K. Livingston" <[email protected]> a écrit
dans le message de (e-mail address removed)...
 
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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Thanx a lot, I will definitely test this workaround asap.

all the best,

Peter
 
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
 
Back
Top