VBA: sheet in background
I probably shouldn't respond to a message that starts "All gurus", but
my judgement is still clouded by lack of coffee...
1) Try Application.ScreeenUpdating = False at the start of your macro
and Application.ScreenUpdating = True at the end.
2) It is seldom necessary to select an object before you manipulate it.
So for example, if you want the value in A1 in MySheet rather than
writing
Sheets(MySheet).Select
x = Range("A1").Value
Just write
x = Sheets(MySheet).Range("A1").Value
If you need to get a lot of values from MySheet then it might make
sense to first activate it, but even then you could consider using the
With ... End With construct.
Hopefully the real gurus will be waking up soon
-John Coleman
Wang wrote:
All gurus,
until now I open an excel sheet before the macro read from it, or create an
excel sheet before the macro write to it as follows:
Workbooks.Open FileName:=MyFileName
Sheets(MySheet).Select
(read from the sheet......)
or
Workbooks.Add
(write to it ......)
This way I see each sheet opened or created, a cell is written, ......
(Maybe this causes the macro running very slowly?)
Is it possible to read from or write to an excel sheet without displaying it
in the excel window? Just as, if we open a text file and do reading or
writing, we don't need to open it with notepad. Everything is done in the
background.
Many thanks in advance!
w.wang
|