Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error while running macro, but not if stepping through lines.
Hello,
If I run my macro code it errors at a specific spot each time, but if I break before and step through the code and resume it works. The error comes affter the delete "Sheet1". Any advice? Here are the specifc lines if it helps: h = Sheets.Count Dim wbA As Workbook Dim wbNew As Workbook With Application SheetsInWb = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .DisplayAlerts = False .ScreenUpdating = False End With Set wbA = ThisWorkbook Set wbNew = Workbooks.Add With wbNew .SaveAs Filename:="C:\Documents and Settings\ty756c\My Documents\contract project\" & programname & ".xls" End With hh = h - 5 hhh = 1 For copysheet = 1 To hh wbA.Sheets(5 + hhh).Copy wbNew.Sheets(hhh) hhh = hhh + 1 Next copysheet wbNew.Sheets("Sheet1").Delete ActiveWorkbook.BreakLink Name:= _ "C:\Documents and Settings\ty756c\Desktop\nbfgraph macro.xls", Type:= _ xlExcelLinks ActiveWorkbook.Save ActiveWorkbook.Close |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error while running macro, but not if stepping through lines.
I wonder if you'll find something here useful
Option Explicit Sub BreakAWBLinks() Dim myName As Name Dim WorkbookLinks As Variant Dim i As Long Dim myWB As Excel.Workbook Set myWB = ActiveWorkbook WorkbookLinks = myWB.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsArray(WorkbookLinks) Then MsgBox "No Links to other workbooks in workbook" End End If For i = LBound(WorkbookLinks) To UBound(WorkbookLinks) myWB.BreakLink _ Name:=WorkbookLinks(i), _ Type:=xlLinkTypeExcelLinks Next i MsgBox ("Workbook links broken") End Sub -- HTH, Barb Reinhardt "Brian S" wrote: Hello, If I run my macro code it errors at a specific spot each time, but if I break before and step through the code and resume it works. The error comes affter the delete "Sheet1". Any advice? Here are the specifc lines if it helps: h = Sheets.Count Dim wbA As Workbook Dim wbNew As Workbook With Application SheetsInWb = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .DisplayAlerts = False .ScreenUpdating = False End With Set wbA = ThisWorkbook Set wbNew = Workbooks.Add With wbNew .SaveAs Filename:="C:\Documents and Settings\ty756c\My Documents\contract project\" & programname & ".xls" End With hh = h - 5 hhh = 1 For copysheet = 1 To hh wbA.Sheets(5 + hhh).Copy wbNew.Sheets(hhh) hhh = hhh + 1 Next copysheet wbNew.Sheets("Sheet1").Delete ActiveWorkbook.BreakLink Name:= _ "C:\Documents and Settings\ty756c\Desktop\nbfgraph macro.xls", Type:= _ xlExcelLinks ActiveWorkbook.Save ActiveWorkbook.Close |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
error while running macro, but not if stepping through lines.
Brian S wrote:
Hello, If I run my macro code it errors at a specific spot each time, but if I break before and step through the code and resume it works. The error comes affter the delete "Sheet1". I will hazard a guess that it is some kind of race condition where the failing command doesn't find "Sheet1" or cannot break the link because it is still in use by another process. Commenting out the DisplayAlerts and ScreenUpdating lines might shed some more light. I have seen some race conditions in chart axes on XL2007 but never on worksheets. Obviously in single step or debug mode there is more time for whatever needs to happen to complete. Adding a judicious delay after copying the worksheets to the new workbook might be sufficient. Regards, Martin Brown Any advice? Here are the specifc lines if it helps: h = Sheets.Count Dim wbA As Workbook Dim wbNew As Workbook With Application SheetsInWb = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .DisplayAlerts = False .ScreenUpdating = False End With Set wbA = ThisWorkbook Set wbNew = Workbooks.Add With wbNew .SaveAs Filename:="C:\Documents and Settings\ty756c\My Documents\contract project\" & programname & ".xls" End With hh = h - 5 hhh = 1 For copysheet = 1 To hh wbA.Sheets(5 + hhh).Copy wbNew.Sheets(hhh) hhh = hhh + 1 Next copysheet wbNew.Sheets("Sheet1").Delete ActiveWorkbook.BreakLink Name:= _ "C:\Documents and Settings\ty756c\Desktop\nbfgraph macro.xls", Type:= _ xlExcelLinks ActiveWorkbook.Save ActiveWorkbook.Close |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
error while running macro, but not if stepping through lines.
Thanks for the help.
"Martin Brown" wrote: Brian S wrote: Hello, If I run my macro code it errors at a specific spot each time, but if I break before and step through the code and resume it works. The error comes affter the delete "Sheet1". I will hazard a guess that it is some kind of race condition where the failing command doesn't find "Sheet1" or cannot break the link because it is still in use by another process. Commenting out the DisplayAlerts and ScreenUpdating lines might shed some more light. I have seen some race conditions in chart axes on XL2007 but never on worksheets. Obviously in single step or debug mode there is more time for whatever needs to happen to complete. Adding a judicious delay after copying the worksheets to the new workbook might be sufficient. Regards, Martin Brown Any advice? Here are the specifc lines if it helps: h = Sheets.Count Dim wbA As Workbook Dim wbNew As Workbook With Application SheetsInWb = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .DisplayAlerts = False .ScreenUpdating = False End With Set wbA = ThisWorkbook Set wbNew = Workbooks.Add With wbNew .SaveAs Filename:="C:\Documents and Settings\ty756c\My Documents\contract project\" & programname & ".xls" End With hh = h - 5 hhh = 1 For copysheet = 1 To hh wbA.Sheets(5 + hhh).Copy wbNew.Sheets(hhh) hhh = hhh + 1 Next copysheet wbNew.Sheets("Sheet1").Delete ActiveWorkbook.BreakLink Name:= _ "C:\Documents and Settings\ty756c\Desktop\nbfgraph macro.xls", Type:= _ xlExcelLinks ActiveWorkbook.Save ActiveWorkbook.Close . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stepping through v. running code | Excel Programming | |||
Different results when running macro vs stepping into macro | Excel Programming | |||
Stepping through gives different result than running | Excel Programming | |||
Error Message When running code but not stepping through it. | Excel Programming | |||
VBA Code works by stepping through, not by running | Excel Programming |