VBA execution stops at ActiveSheet.Copy -- but only sometimes!

[Ole M Halck]

Hello all,

I have a subroutine like the following:

----------------
Sub AbsTest(fn As String, sec As Long)

Dim resPath As String
Dim mSheet As Worksheet, resultSheet As Worksheet
Dim mBook As Workbook

Set mSheet = ActiveSheet

AbsTestString fn, sec
' AbsTestString is where the actual work is done; it
' saves result data to a simple text file called abstest.txt.

Set mBook = Workbooks.Open("abstest.txt") ' Read the text file
mBook.ActiveSheet.Copy Before:=mSheet ' Copy to this book
mBook.Close False ' Close the text file
' without save prompt

Set resultSheet = ActiveSheet
'<Process the data in resultSheet, which was copied into this book>
Application.DisplayAlerts = False
resultSheet.Delete
Application.DisplayAlerts = True

End Sub
----------------

The sub is repeated several times in a loop, with varying values for
fn and sec.

Now, this works faultlessly most of the time. But occasionally,
execution stops after abstest.txt has been opened -- i.e. it doesn't
get copied to mBook -- without any error message at all. I can see no
pattern to when it works and when it stops -- identical runs of the
outer loop may stop in different iterations, or (usually) finish as it
should.

Any ideas what may be wrong here?

 

[Jim Rech]

Just a shot in the dark but I'd suggest making sure no other code is running
when the text file is opened. Do that by making sure
Application.EnableEvents is set to False and that calculation mode is set to
manual (so no UDFs run). Add a step to enable events when your code is
finished.

--
Jim Rech
Excel MVP
| Hello all,
|
| I have a subroutine like the following:
|
| ----------------
| Sub AbsTest(fn As String, sec As Long)
|
| Dim resPath As String
| Dim mSheet As Worksheet, resultSheet As Worksheet
| Dim mBook As Workbook
|
| Set mSheet = ActiveSheet
|
| AbsTestString fn, sec
| ' AbsTestString is where the actual work is done; it
| ' saves result data to a simple text file called abstest.txt.
|
| Set mBook = Workbooks.Open("abstest.txt") ' Read the text file
| mBook.ActiveSheet.Copy Before:=mSheet ' Copy to this book
| mBook.Close False ' Close the text file
| ' without save prompt
|
| Set resultSheet = ActiveSheet
| '<Process the data in resultSheet, which was copied into this book>
| Application.DisplayAlerts = False
| resultSheet.Delete
| Application.DisplayAlerts = True
|
| End Sub
| ----------------
|
| The sub is repeated several times in a loop, with varying values for
| fn and sec.
|
| Now, this works faultlessly most of the time. But occasionally,
| execution stops after abstest.txt has been opened -- i.e. it doesn't
| get copied to mBook -- without any error message at all. I can see no
| pattern to when it works and when it stops -- identical runs of the
| outer loop may stop in different iterations, or (usually) finish as it
| should.
|
| Any ideas what may be wrong here?
|
|
| --
| OleM

 

[Ole M Halck]

Just a shot in the dark but I'd suggest making sure no other code is running
when the text file is opened. Do that by making sure
Application.EnableEvents is set to False and that calculation mode is set to
manual (so no UDFs run). Add a step to enable events when your code is
finished.

Thanks for your suggestion, Jim -- I'll try that.

 

[Jon]

This is a known bug with Excel. There is a Microsoft KB article that
outlines a workaround. Basically you need to add a new sheet to your
workbook from a template, and paste the contents of the text file onto
this new sheet each time through your loop.

This supposedly only affects earlier versions of Excel, but I've seen
it in Excel 2003 when I copy 100 sheets or more.

Here's a link to the KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684

The description of the issue in the article may not match your
symptoms exactly, but I think if you try the template approach, you
should get the results you want.

-Jon Crowell