Hi all,

My project is about pick up info from individual workbook, and merge the
info into a new workbook. But in the end of code, even I set nothing to
workbook and application object, but the Excel process is still there, but
the way the open and close procedure is very slow, is there any way to
expedite it?

Dim SrcWrk As Workbook
Dim DstWrk As Workbook
Dim app As New Application


DstWrk = app.Workbooks.Add()

For Each File As String In Directory.GetFiles(Me.SrcDir, "*.xls",
System.IO.SearchOption.AllDirectories)


SrcWrk = app.Workbooks.Open(File)

SrcWrk.Close()
SrcWrk = Nothing

Next

DstWrk.SaveAs(Me.DesDir & "\" & Me.DesFileName)
DstWrk.Close()
DstWrk = Nothing
app.Quit()
app = Nothing

--

You can see, now there is no action within the loop.

Clara
thank you so much for your help

There is too reasons why the macro is taking a long time

1) If the worksbooks are large it will take time to open and close theh
workbooks

2) You are searching all the subdirectories (Me.SrcDir and subfolders)
which can take a very long time.

The one question I have is how long does it take to open any of these
workbooks by themseleves. Then multiply this number by the number of
workbooks you are opening.

You may havve a problem on your PC with opening any workbook. try
opening one of these workbooks on another PC and see if it also takes a
long time. You may havve a problem on your PC that needs to be fixed.

Also if you are trying to open the workbooks froom a Network drive this
could also slow down the macro.

You may also have INDEXING enabled on your PC or Newwork drive that is
slowing down the process and turing off Indexing may help.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189852

http://www.thecodecage.com/forumz/chat.php