Code interrupted error

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
Seamus2626Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SiddharthRoutConnect With a Mentor Commented:
Yes try that. But before that copy that file to C: and then test it whether it works with C: or not :)

Sid
0
 
SiddharthRoutCommented:
Please show us the code.

Sid
0
 
FernandoFernandesCommented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Seamus2626Author Commented:
Thanks Sid
Example.xls
0
 
SiddharthRoutCommented:
Which code are you referring to?

Sid
0
 
Seamus2626Author Commented:
That should be good
--Doc1.doc
0
 
Seamus2626Author Commented:
Run Report Sub
0
 
Seamus2626Author Commented:
Module2

Thanks
Seamus
0
 
jppintoCommented:
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
 
jppintoCommented:
Oh...it's not this code, it's module 2, right?
0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
the error message is

"Code execution has been interrupted"

it errors in Moudule2, Runreport

Thanks
Seamus
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
Hey Sid, thanks for the code, this is the file i refer too

Thanks
Seamus
Example.xls
0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
Can you upload the file ML WEEKLY REPORT?

Sid
0
 
Seamus2626Author Commented:
   ws1.Columns("A:C").EntireColumn.AutoFit
    ws1.Columns("A:C").EntireColumn.Hidden = True

That is for CP Report.xls

Thanks
Seamus
0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
Getting stoppped on Do events!

0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
Ah!

Change the line

Set ws1 = wb1.Sheets("003409")

to

Set ws1 = wb1.Sheets("003049")

Also remove Doevents.

Now try :)

Sid
0
 
SiddharthRoutCommented:
Also remove the line

Application.DisplayAlerts = False

Sid
0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
I just tried the above code and it works fine for me?

Did you try the above code as it is?

Sid
0
 
SiddharthRoutCommented:
Also a quick question. From where are you running the code? Which workbook?

Sid
0
 
Seamus2626Author Commented:
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
 
Seamus2626Author Commented:
Im running it from CP Report.xls........
0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
I tried (1) same error

I have attached the error message


error.doc
0
 
SiddharthRoutCommented:
It is definitely working for me. Can you upload your CP Report.xls file?

Sid
0
 
Seamus2626Author Commented:
Thanks Sid
CP-Report.xls
0
 
SiddharthRoutCommented:
Give me 15 mins

Sid
0
 
Seamus2626Author Commented:
Thanks Sid!
0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
"G:\MLHSBC\ML EX Date Report\"

Thats a shared drive

Do you think i should try the UNC path?
0
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
Why did you close the thread?

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

Sid
0
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
No it is not :) And I don't want to leave this unfinished ;)

We will wait till tomorrow.

Sid
0
 
Seamus2626Author Commented:
I appreciate that Sid, i will have a look tomorrow again.

Thanks
Seamus :-)
0
 
Seamus2626Author Commented:
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
 
SiddharthRoutCommented:
Glad it is resolved ;)

Sid
0
All Courses

From novice to tech pro — start learning today.