Solved

Code interrupted error

Posted on 2011-03-02
53
202 Views
Last Modified: 2012-05-11
Hi,

I am getting the attached error.

When i step in and press F5, i can move through fine and complete my report.

What are common faults that would lead to this error?

Thanks
Seamus
Doc1.doc
0
Comment
Question by:Seamus2626
  • 26
  • 24
  • 2
  • +1
53 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Please show us the code.

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
Comment Utility
hello Seamus2626,
the document you sent doesn't show us anything of the error... can you please send us the code, and if possible, with a highlighted area of where the code stops, when error happens ?

THanks,
0
 

Author Comment

by:Seamus2626
Comment Utility
Thanks Sid
Example.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Which code are you referring to?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
That should be good
--Doc1.doc
0
 

Author Comment

by:Seamus2626
Comment Utility
Run Report Sub
0
 

Author Comment

by:Seamus2626
Comment Utility
Module2

Thanks
Seamus
0
 
LVL 33

Expert Comment

by:jppinto
Comment Utility
Just added the following line and it's working:

Sheets("Output").Select


jppinto
Private Sub Workbook_Open()

Dim r As Long
Sheets("Output").Select
With Sheets("Output")
    r = .Range("H" & Rows.Count).End(xlUp).Row

    .Range("I7").Formula = "=TRIM(MID(INDEX($A:$A,$C7+6),27,8))"

    .Range("I7:I7").Copy .Range("I7:I" & r)
    End With

End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:jppinto
Comment Utility
Oh...it's not this code, it's module 2, right?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Seamus, What is the error that you are getting?

The 1st doc doesn't say anything and the other is corrupt, I believe.

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
the error message is

"Code execution has been interrupted"

it errors in Moudule2, Runreport

Thanks
Seamus
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Ok few other things.

When you say

Range("A:E").EntireColumn.Hidden = False

Please attache the relevant sheet name before it, else it will be difficult for me or the code to understand what you are referring to. for example

Sheets("Output").Range("A:E").EntireColumn.Hidden = False

Other Changes.

Declare

Dim wb1 As Workbook

at the top of your code and then use it when you open the workbook for example

    Dim wb1 As Workbook
    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")

So that you can refer to the relevant range later

for example

wb1.Sheets(1).Columns("A:A").Copy

Just make the relevant changes in Module 2, RunCalc and Module3.Macro3

and you will be fine :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I have done the changes but I don't know which range in which sheet are you referring to. However here is my shot at it. Please amend it to suit your needs :)

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    
    Set wb1 = ActiveWorkbook
    
    wb1.Sheets("Output").Range("A:E").EntireColumn.Hidden = False

    Application.DisplayAlerts = False

    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    '~~> Change path of CP Report.xls
    Set wb3 = Workbooks.Open("G:\CP Report.xls")
    
    wb2.Sheets(1).Columns("A:A").Copy _
    wb3.Sheets(1).Columns("A:A")
    
    wb3.Sheets(1).Columns("A:C").EntireColumn.AutoFit
    wb3.Sheets(1).Columns("A:C").EntireColumn.Hidden = True

    wb2.Close SaveChanges:=False

    wb3.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb3.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Open in new window


Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Hey Sid, thanks for the code, this is the file i refer too

Thanks
Seamus
Example.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Ok... Let's wipe off the slate and start all over again.

If you want me to give you an accurate solution, please reply to my questions in the order they are asked.

1) What is the name of the file from where the code is run?
2) What other files names are involved?
3) What are their paths
4) What exactly do you want to do?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Sorry Sid, i meant thats the source file.

Thats fine, ive adjusted.

However, i got the original error message on line

 Set wb3 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")

Ive attached the changes i made which were amending file path and sheet names.

All in all, im trying to open ML WEEKLY REPORT.xls, copyy its entire column A, and paste it into CP Report.xls, in ColA.

The sheet name for the former is 003409 and the later, output.

Thanks
Seamus


Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    
    Set wb1 = ActiveWorkbook
    
    wb1.Sheets("Output").Range("A:E").EntireColumn.Hidden = False

    Application.DisplayAlerts = False

    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    '~~> Change path of CP Report.xls
    Set wb3 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    
    wb2.Sheets("003049").Columns("A:A").Copy _
    wb3.Sheets("Output").Columns("A:A")
    
    wb3.Sheets(1).Columns("A:C").EntireColumn.AutoFit
    wb3.Sheets(1).Columns("A:C").EntireColumn.Hidden = True

    wb2.Close SaveChanges:=False

    wb3.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb3.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
That's much better

What is the sheet name of ML WEEKLY REPORT.xls.. "003409" ?
What is the sheet name of CP Report.xls? "output" ?

Also what are their paths?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Thats it Sid

CP Report.xls, Sheet name is "Output"
ML WEEKLY REPORT.xls, Sheet name is "003409"

Their paths are

("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls") &
("G:\MLHSBC\ML EX Date Report\CP Report.xls")

Thanks
Seamus
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Try this and tell me where do you get the error i.e if you get one.

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Application.DisplayAlerts = False

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003409")
    
    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    Set ws2 = wb1.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
My only question at this point of time regarding the above code pertains to

    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

Do you want to do this in workbook ML WEEKLY REPORT or CP Report?

If it is ML WEEKLY REPORT, then change the above code to

    ws1.Columns("A:C").EntireColumn.AutoFit
    ws1.Columns("A:C").EntireColumn.Hidden = True

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Set ws1 = wb1.Sheets("003409")

Same strange error, "Code Execution has been interrupted"

I have ran this code before and its worked. I dont understand.....
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Can you upload the file ML WEEKLY REPORT?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
   ws1.Columns("A:C").EntireColumn.AutoFit
    ws1.Columns("A:C").EntireColumn.Hidden = True

That is for CP Report.xls

Thanks
Seamus
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Try this in the meantime

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Application.DisplayAlerts = False

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    
    DoEvents
    
    Set ws1 = wb1.Sheets("003409")
    
    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    
    DoEvents
    Set ws2 = wb2.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Open in new window


Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Getting stoppped on Do events!

0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Ok which doevents ? The 1st or the 2nd?

If it is the first then check if you have any code in the workbook open event of ML WEEKLY REPORT.xls or better still, can you upload the file ML WEEKLY REPORT.xls?

Sid
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Seamus2626
Comment Utility
1st Do Event

No code in the Workbook Open for ML WEEKLY REPORT.xls

I uploaded the file earlier

Message 35019691

"example.xls"

That is ML WEEKLY REPORT.xls

Thanks
Seamus

Thanks
Seamus

That is the
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Ah!

Change the line

Set ws1 = wb1.Sheets("003409")

to

Set ws1 = wb1.Sheets("003049")

Also remove Doevents.

Now try :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Also remove the line

Application.DisplayAlerts = False

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
In fact try this

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003049")
    
    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    Set ws2 = wb2.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Open in new window


Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
This code is gonna crack me up!!

Set ws1 = wb1.Sheets("003049")

Its stopping there.

Then, when i step through, its stops at

wb2.Activate

Then, once complete, again at
 Application.DisplayAlerts = True

What is its problem?!

Thanks
Seamus
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I just tried the above code and it works fine for me?

Did you try the above code as it is?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Also a quick question. From where are you running the code? Which workbook?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
I edited it slightly, no need to re-open CP Report

Thats it.

Same as above, i think!
Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003049")
    
    Set wb2 = Workbooks("CP Report.xls")
    Set ws2 = wb2.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:Seamus2626
Comment Utility
Im running it from CP Report.xls........
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
ok try this

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb2 = ActiveWorkbook
    Set ws2 = wb2.Sheets("Output")

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003049")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:Seamus2626
Comment Utility
Stopped again on  

Set ws1 = wb1.Sheets("003049")

Same error message

I have to shoot off Sid, i will look at this again in the morning with a fresh pair of eyes.

Thanks for your help
Seamus
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
1) Copy the sheetname from that workbook by double clicking on the sheet tab and pasting it in the code between quotes.
2) Can you post the screenshot of the error message?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
I tried (1) same error

I have attached the error message


error.doc
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
It is definitely working for me. Can you upload your CP Report.xls file?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Thanks Sid
CP-Report.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Give me 15 mins

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Thanks Sid!
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I was able to make it work here. So the only thing that pulls my attention is the path of the file

"G:\MLHSBC\ML EX Date Report\"

Is this on a network location?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
"G:\MLHSBC\ML EX Date Report\"

Thats a shared drive

Do you think i should try the UNC path?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
Comment Utility
Yes try that. But before that copy that file to C: and then test it whether it works with C: or not :)

Sid
0
 

Author Closing Comment

by:Seamus2626
Comment Utility
Neither made a difference Sid, i think il have to rethink this report altogether!!

Thanks for all your help on this one

Cheers,
Seamus
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Why did you close the thread?

Also did it work when you put it in C:?

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
Nope, never worked on C, ive just given up Sid, its 8, i must head home. Tomorrow i may try and think about a different way of doing the report. It seems like the code is doomed!
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
No it is not :) And I don't want to leave this unfinished ;)

We will wait till tomorrow.

Sid
0
 

Author Comment

by:Seamus2626
Comment Utility
I appreciate that Sid, i will have a look tomorrow again.

Thanks
Seamus :-)
0
 

Author Comment

by:Seamus2626
Comment Utility
Found a piece of code to get rid of that annoyhing ba$tard of an error!!

Application.EnableCancelKey = xlDisabled

Thanks for tidying my code up, works now :-)
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Glad it is resolved ;)

Sid
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now