Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA execution stops at ActiveSheet.Copy -- but only sometimes!
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 Befo=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA execution stops at ActiveSheet.Copy -- but only sometimes!
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 "Ole M Halck" wrote in message ... | 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 Befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA execution stops at ActiveSheet.Copy -- but only sometimes!
On Tue, 17 Aug 2004 10:50:05 -0400, "Jim Rech"
wrote: 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. -- OleM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA execution stops at ActiveSheet.Copy -- but only sometimes!
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...b;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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveSheet.Copy | Excel Programming | |||
name of the activesheet | Excel Programming | |||
How to copy text from a TextBox in a ActiveSheet to a variable | Excel Programming | |||
How to copy text from a TextBox in a ActiveSheet to a variable | Excel Programming | |||
ActiveSheet.Name? | Excel Programming |