Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro suddenly stops mid process through an array
Dear All,
I have the following array (variant): Row,FileName,Path,Use,Done with about 5,000 rows in it. I firstly fill the array columns Use and Done with the string "blank" where there are any "empty" elements. I then loop through each row, concatenate path & file and open the file in read only that it corresponds to (there to ensure it exists). It then reads a cell, writes it to another opened file and then close the file. My problem is that the macro suddenly stops (no debug error warning) and random times, sometimes on file 35 or sometimes on file 435. Any ideas why this would happen would be greatly appreciated. Cheers Mac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro suddenly stops mid process through an array
Hi,
You're going to have to post the code if you want anyone to figure out what's wrong. -- Thanks, Shane Devenshire " wrote: Dear All, I have the following array (variant): Row,FileName,Path,Use,Done with about 5,000 rows in it. I firstly fill the array columns Use and Done with the string "blank" where there are any "empty" elements. I then loop through each row, concatenate path & file and open the file in read only that it corresponds to (there to ensure it exists). It then reads a cell, writes it to another opened file and then close the file. My problem is that the macro suddenly stops (no debug error warning) and random times, sometimes on file 35 or sometimes on file 435. Any ideas why this would happen would be greatly appreciated. Cheers Mac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro suddenly stops mid process through an array
I have two suggestions.
1) Comment out all ON Error statements so the code will stop on the error. 2) change you setting in the VBA Tools Menu Tools - Option - General - Stop on All Errors " wrote: Dear All, I have the following array (variant): Row,FileName,Path,Use,Done with about 5,000 rows in it. I firstly fill the array columns Use and Done with the string "blank" where there are any "empty" elements. I then loop through each row, concatenate path & file and open the file in read only that it corresponds to (there to ensure it exists). It then reads a cell, writes it to another opened file and then close the file. My problem is that the macro suddenly stops (no debug error warning) and random times, sometimes on file 35 or sometimes on file 435. Any ideas why this would happen would be greatly appreciated. Cheers Mac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro suddenly stops mid process through an array
I'd also add to put Option Explicit at the before the Sub MySub() line.
This will force you to declare all of your variables. I've also seen this happen when I open multiple workbooks programmatically and I'm doing something eise in another application and I just happen to press the SHIFT key when a workbook is being opened. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Joel" wrote: I have two suggestions. 1) Comment out all ON Error statements so the code will stop on the error. 2) change you setting in the VBA Tools Menu Tools - Option - General - Stop on All Errors " wrote: Dear All, I have the following array (variant): Row,FileName,Path,Use,Done with about 5,000 rows in it. I firstly fill the array columns Use and Done with the string "blank" where there are any "empty" elements. I then loop through each row, concatenate path & file and open the file in read only that it corresponds to (there to ensure it exists). It then reads a cell, writes it to another opened file and then close the file. My problem is that the macro suddenly stops (no debug error warning) and random times, sometimes on file 35 or sometimes on file 435. Any ideas why this would happen would be greatly appreciated. Cheers Mac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro suddenly stops mid process through an array
If the shift key is pressed, you can use something like this
--- 'Declare API Declare Function GetKeyState Lib "User32" _ (ByVal vKey As Integer) As Integer Const SHIFT_KEY = 16 Function ShiftPressed() As Boolean 'Returns True if shift key is pressed ShiftPressed = GetKeyState(SHIFT_KEY) < 0 End Function --- I'm not sure where I got this, but I'm sure it was from someone on this board. I then put something like this in my code right before the workbook open --- Do While ShiftPressed() MsgBox ("The shift key is pressed. Please release it and press OK.") DoEvents Loop Set oWB = XLApp.Workbooks.Open(oldPath, UpdateLinks:=False, ReadOnly:=True) -- Again, this came from someone on this board. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. " wrote: Dear All, I have the following array (variant): Row,FileName,Path,Use,Done with about 5,000 rows in it. I firstly fill the array columns Use and Done with the string "blank" where there are any "empty" elements. I then loop through each row, concatenate path & file and open the file in read only that it corresponds to (there to ensure it exists). It then reads a cell, writes it to another opened file and then close the file. My problem is that the macro suddenly stops (no debug error warning) and random times, sometimes on file 35 or sometimes on file 435. Any ideas why this would happen would be greatly appreciated. Cheers Mac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shell command in VBA suddenly stops working | Excel Programming | |||
Data Validation in XL2007 suddenly stops functioning | Excel Discussion (Misc queries) | |||
Macro Suddenly Fails | Excel Discussion (Misc queries) |