Bizarre macro problem

[Guest]

Hi all
This one has me stumped! I have a macro that at a certain point opens
another workbook in order to copy some data into the current worksheet.
However, it has suddenly started to stop running as soon as the workbook has
opened. By 'stop' I mean literally that, the macro just stops running - no
error message, Excel doesn't crash or any of the usual - the macro just
stops. However, when I try putting a break point just before the line that
opens the workbook and step through line by line it works fine. If the break
point is just after this line then the macro stops before it gets to it.
This started happening yesterday and re-booting did not help but shutting
down overnight did seem to fix it for the first few attempts but it has no
reverted to stopping again. Any help would be greatly appreciated!
Thanks
Andy

 

[Bob Phillips]

Sounds like it is expecting something to be active which isn't. Post the
code, sample data and highlight the problem area.

 

[Guest]

Hi Bob
Sorry, I meant to post the code originally. The problem occurs where the
asterisks are. The workbook opens but that is as far as it gets. As I
stated previously, stepping through the code allows it to work but running
the macro straight through doesn't. The macro is quite long so I have just
posted a few lines before and a few lines after where I believe the problem
is, let me know if you want the whole lot!
Thanks
Andy

'Other code here

'Add a new sheet and open up the regression template workbook
Sheets.Add
ChDir TEMPLATE_DIR
Workbooks.Open Filename:=TEMPLATE_NAME

*****************************************************

'Copy the regression template and paste it into the new sheet
Cells.Select
Selection.Copy
Windows(strBookName).Activate
Range("A1").Select
ActiveSheet.Paste

'....Macro continues

 

[Bob Phillips]

Nothing obvious there, but one thought occurs to me. If you have many
drives, the drive may not be active. Try adding

ChDrive TEMPLATE_DIR

before the ChDir.

 

[NickHK]

Andy,
Which workbook and sheet are you accessing with "Cells.Select" ?

To avoid confusion and bad references, it is better to:
Dim SourceWB as WorkBook
Set SourceWB = Workbooks.Open Filename:=TEMPLATE_NAME

Then you can use "SourceWB.Whatever" to ensure you are referencing the
correct object.

Also, what is the value TEMPLATE_NAME ?
By the way, it is seldom necessary to .Select objects in Excel in order to
manipulate them, if you have a refence to them (see above).

NickHK

 

[Guest]

Hi Nick
Cells.select accesses the workbook opened by:
Workbooks.Open Filename:=TEMPLATE_NAME

I had referenced the workbook as you suggest but had changed it to this as
part of trying to track down the problem. However, explicitly assigning
objects to variables is something I have only recently started doing so I'm
probably not as thorough as I should be!

TEMPLATE_NAME is a string constant containing the full path and name of the
workbook: TEMPLATE_DIR & "\Regression Template - TO.xls"

TEMPLATE_DIR is another string constant containing the path of the workbook:
"L:\Publications Analysis\UKAnalysisSep2004\Parent"

The macro does actually open the workbook before it stops running - so I am
left with the newly opened workbook displayed on screen but the cells are not
selected.

Incidentally, I have Adobe Acrobat installed on my machine and about the
same time the macro started going wrong the Adobe addin started throwing a
debug error each time Excel was opened - something to do with an invalid
method of the CommandBars object. I ran the Office repair utility and also
re-installed Office but that didn't help so in the end I just removed the
Adobe addin file from the startup folder. I don't know if that provides any
more clues or is just coincidence...

Thanks
Andy

 

[Guest]

Hi Bob
That's a shame!

I have tried as you suggest but that doesn't seem to have worked. The macro
successfully manages to open the workbook but stops immediately afterwards.
I even tried the following:

MsgBox (Workbooks.Open(Filename:=TEMPLATE_NAME).Name)

And still it opens the workbook and stops i.e. it doesn't display the
message box.

I have a bad feeling that my system has got screwed somewhere (see my reply
to Nick) - I might try copying the code into a new module and see if that
makes a difference...

Thanks
Andy

 

[Bob Phillips]

Andy,

That seems like a drastic, albeit understandable, course of action. You
could also try Rob Bovey's Code Cleaner, it often cures such problems.

 

[Guest]

Hi Bob

Copying the code into a new module didn't work, however the plot thickens.
It seems that I do not need to step through each line for it to work. As
long as there is at least one breakpoint before the workbook is opened, I can
press F5 and all is well. Similarly if I add a msgbox (msgbox "Hello")
somewhere before the point of failure, again it works. Curiouser and
curiouser...

I have downloaded Code Cleaner and will give that a go.

Thanks for your help
Andy

 

[Bob Phillips]

I am sure it is something silly that we are all missing, but it is
intriguing.

Good luck with it, and keep us posted.

Bob

 

[Tom Ogilvy]

does the workbook your opening have a workbook_Open event that runs when you
open it.

If so, does this cause an error?

If so, and it doesn't cause an error, does the code you posted get run using
a shortcut key combination? Does that combination include the Shift key?

 

[Guest]

Hi Bob

I have used the code cleaner and it works

However, the next time I run the macro the same problem happens :-(

I can then clean the code again, which is fine for the first time the macro
is run but not subsequent ones.

However, even this doesn't seem to be working now...

 

[tony h]

two suggestions

1. try using a doevents just before and after your troublesome line
The doEvents statement tell excel to stop doing what it is doing an
let other processes have a go. It maybe that your breakpoint is jus
releasing the process and that the doevents would do the same thing.

2. Put an error trap around the code. depending on how the code wa
activated an error may just halt the code. It is particularly importan
to do this in user defined functions and dependant code that is calle
from the worksheet.

Sub mycode()

Dim str As String

On Error GoTo AnError

'my code ...........
str = 1 / 0

GoTo TheEnd
AnError:
MsgBox "error : " & Err.Number & " " & Err.Description
Stop 'you will have to decide what to do here
TheEnd:

End Sub

hope this help

 

[Guest]

Hi Tom

No, the workbook being opened does not have a workbook_Open event. However,
the code is run as a result of a shortkey combination that includes the shift
key (ctrl + shift + t).

Thanks
Andrew

 

[Rich_z]

Have you tried running your work book on a different machine t
determine whether it's your machine, or your code that's causing th
probs

 

[Rich_z]

Have you tried running your work book on a different machine t
determine whether it's your machine, or your code that's causing th
probs

 

[Guest]

Hi Tony

Your first suggestion works!!! In fact I only needed to add DoEvents before
opening the workbook.

Out of interest I tried error trapping but that didn't work.

Thank you all sooooo much for your help and for maintaining my sanity!
Andy

 

[Dave Peterson]

Remove the shift-key from the shortcut key.

Holding the shiftkey while opening a workbook tells excel not to run the open
procedures. The shiftkey in the short cut confuses excel.

 

[Dave Peterson]

I think it was the combination of doEvents and releasing the shiftkey.