Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Found where program stops, but can't stop it stopping !!
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default Found where program stops, but can't stop it stopping !!
What do you mean by "stopping", does code break on a line of code, or does
Excel crash, etc
You say "it all happens in a second instance", looks like your code is
attempting to open workbooks in the same instance, show us your automation
code.

Difficult to comment with the information given.

Regards,
Peter T



Try and explain what you are doing,
"WhytheQ" wrote in message
...
Hello All,

I've previously asked about finding where my long program is stopping.
I've used several Debug.print lines in the code to find where this is
happening..........and it stops just after the line following 1By.....

With Application
.EnableEvents = False
.ScreenUpdating = True
.Calculation = xlCalculationManual
Debug.Print "1Bx" '++++++++
ChDir "R:\Statistics\"
Debug.Print "1By" '++++++++
.Workbooks.Open "R:
\Statistics\book" & xxx & ".xlsx", False, , , , True
Debug.Print "1Bz" '++++++++

.Calculation =
xlCalculationAutomatic
.ScreenUpdating = False
.EnableEvents = True
End With

It loops through the above about 40 times, opening different books -
it doesn't always stop when trying to open the same workbook. Since
finding the problem I've added in the methods (.EnableEvents =
False / .ScreenUpdating = True / .Calculation = xlCalculationManual)
to try to sort things out but it is still occuring.

All the above happens in a second instance of Excel.

Any help much appreciated

Regards,
Jason.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 246
Default Found where program stops, but can't stop it stopping !!
Hello Peter

By stopping I mean it just sort of drops out i.e no error occurs but
the program just stops at the point I mentioned. When I then look in
the instance of Excel, the workbook with the code in it is open with
the Design Mode button activated and all other books that the program
has opened upto that point are also open ! Because there isn't an
error generated I had a lot of trouble finding exactly where it was
stopping - that is why I added a lot of Debug.Print labels throughout
the program.

Here's the automation Peter (quite a lot of it!! ....you might have
seen some of it before in my previous posts)......

In Excel1:
'================================
'================================
Option Explicit

Public myXLApplication As Excel.Application
Public myXLWorkbook As Excel.Workbook

Public Const myCodeWorkbook As String = "R:\Statistics\Daily Storage
Book Updater.xlsm"


Sub UpdateStorageBooks()

Application.EnableEvents = True

Set myXLApplication = New Excel.Application
myXLApplication.Visible = True
myXLApplication.Workbooks.Open myCodeWorkbook, , False, , , , True
Set myXLApplication = Nothing

End Sub


'================================
'================================
.........then in the second instance the following runs:


Option Explicit

Private Sub Workbook_Open()

Dim ActionTime As Date

ActionTime = Now() + TimeValue("00:00:10")
Application.OnTime ActionTime, "controlRoutine"

End Sub 'Workbook_Open



'================================
'================================
.............then 10 seconds later the following runs.........


Sub controlRoutine()


blUpdateAll = False
Application.ScreenUpdating = True
If MsgBox("Do you wish to update all storage sheets irrespective
as to whether they have already been saved today?", vbYesNo +
vbDefaultButton2, "Overwrite Existing Files") = vbYes Then
Application.ScreenUpdating = False
blUpdateAll = True
End If
Application.ScreenUpdating = False


blUpdateFormatting = False
Application.ScreenUpdating = True
If MsgBox("Do you wish to update sheet formatting?", vbYesNo +
vbDefaultButton2, "Update formatting") = vbYes Then
Application.ScreenUpdating = False
blUpdateFormatting = True
End If
Application.ScreenUpdating = False


Call UpdateFeedWorkbook
Call UpdateStorageBooksAndSummary

Application.ScreenUpdating = True

MsgBox "Completed Routine!"

End Sub 'controlRoutine


'================================
'================================
..............in the above it is stopping part way through a big loop
in the routine "UpdateStorageBooksAndSummary" which contains the
following.........



Public Sub UpdateStorageBooksAndSummary()


Application.ScreenUpdating = False

'========open the summary file
'open summary file
If IsFileOpen(ExtractFileName(mySummaryFilePath)) = False Then
Workbooks.Open mySummaryFilePath, , False, , , , True
End If
Set mySummaryBook = Workbooks(ExtractFileName
(mySummaryFilePath))
'========


'clear out the data sheets that were previously collated
from the storage sheets
With mySummaryBook
.Sheets("Data_Measures").Range("A2:AZ10000").Clear Contents
.Sheets("Data_MaxMin").Range("A2:AZ10000").ClearCo ntents
.Sheets("Data_Graphs").Range("A4:G10000").ClearCon tents
.Sheets("Data_Graphs").Range("J4:M10000").ClearCon tents
.Sheets("Data_Graphs").Range("P4:R10000").ClearCon tents
End With
'========


'========open the feed file
'open feed file
If IsFileOpen(ExtractFileName(myFeedFilePath)) = False Then
Workbooks.Open myFeedFilePath, , False, , , , True
End If
Set myFeedBook = Workbooks(ExtractFileName(myFeedFilePath))
'========



'========open all storage sheets
i = 1

EndCell = ThisWorkbook.Sheets("Static_Data").Range("C100").E nd
(xlUp).Row

'loop through the category names, which correspond to the
storage book names
'For Each oItem In oPivCatRange.Cells
For j = 6 To EndCell

myItem = ThisWorkbook.Sheets("Static_Data").Cells(j,
3).Value
myStorageName = myItem & ".xlsx"


If myItem < "" Then

'check if NOT saved today;
AlreadyUpdated = False
If FileDateTime(myStorageFileStore &
myStorageName) Date And blUpdateAll = False Then
AlreadyUpdated = True
End If

Debug.Print "1A" '++++++++
Debug.Print myStorageFileStore & myStorageName '++++++++
Debug.Print Application.ScreenUpdating '++++++++
Debug.Print Application.Calculation '++++++++

'=======open each Storage book - always
opens file to move data to summary
Dim myStorageFullPathWay As String
myStorageFullPathWay =
myStorageFileStore & myStorageName

Debug.Print "1B" '++++++++

With Application

.EnableEvents = False
.ScreenUpdating = True
.Calculation = xlCalculationManual
Debug.Print "1Bx" '++++++++
ChDir myStorageFileStore
Debug.Print "1By" '++++++++
.Workbooks.Open CStr
(myStorageFullPathWay), , False, , , , True
Debug.Print "1Bz" '++++++++

.Calculation =
xlCalculationAutomatic
.ScreenUpdating = False
.EnableEvents = True
End With

Debug.Print "1C" '++++++++

' Dim my As String
Set myStorageBook = Workbooks
(myStorageName)

'=======clear out old data if not
already updated
If AlreadyUpdated = True Then
Else
With myStorageBook.Sheets("Input")
.Range
("C6:AZ500").ClearContents
.Range("D2").ClearContents
End With
End If

'=========================================


'=======copy data into Storage sheet
If AlreadyUpdated = True Then
Else
With myFeedBook.Sheets("Pivot")

.Range("E3").Value = myItem

myLastRow = .Cells
(Rows.Count, 4).End(xlUp).Row

Set rSource = .Range("D7:D"
& myLastRow)
Set rDest =
myStorageBook.Sheets("Input").Range("C7")
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value


Set rSource = .Range("B6:B"
& myLastRow)
Set rDest =
myStorageBook.Sheets("Input").Range("D6")
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource = .Range("E6:AZ"
& myLastRow)
Set rDest =
myStorageBook.Sheets("Input").Range("E6")
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09


End With
End If

'=========================================



'=======copy data out of Storage
sheet==========
With Workbooks(myStorageName).Sheets
("Summary")
.Activate

Set rSource = .Range("C5:BG"
& .Range("B46").Value + 4)
Set rDest = mySummaryBook.Sheets
("Data_Measures").Cells(Rows.Count, 4).End(xlUp)(2, 1)
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09


End With
With mySummaryBook.Sheets
("Data_Measures")
.Range("B" & .Cells(.Rows.Count,
2).End(xlUp).Row + 1 & ":B" & .Cells(.Rows.Count, 4).End(xlUp).Row) =
Workbooks(myStorageName).Sheets("Summary").Range(" C2").Value
.Range("C" & .Cells(.Rows.Count,
3).End(xlUp).Row + 1 & ":C" & .Cells(.Rows.Count, 4).End(xlUp).Row) =
myItem
End With
'=======

'copy graph data out of Storage sheet
With Workbooks(myStorageName).Sheets
("All Operator")
.Activate
'#########################NEW 19AUG09

Set rSource = .Range
("AH7:AL43")
Set rDest =
mySummaryBook.Sheets("Data_Graphs").Cells(mySummar yBook.Sheets
("Data_Graphs").Rows.Count, 3).End(xlUp)(2, 1)
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With


rDest.Value = rSource.Value
Set rSource = Nothing
Set rDest = Nothing

Set rSource = .Range
("AJ6:AL6")
Set rDest =
mySummaryBook.Sheets("Data_Graphs").Cells(mySummar yBook.Sheets
("Data_Graphs").Rows.Count, 11).End(xlUp)(2, 1)
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value
Set rSource = Nothing
Set rDest = Nothing


Set rSource = .Range
("Y6:Z136")
Set rDest =
mySummaryBook.Sheets("Data_Graphs").Cells(mySummar yBook.Sheets
("Data_Graphs").Rows.Count, 17).End(xlUp)(2, 1)
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value
Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09

End With


With mySummaryBook.Sheets("Data_Graphs")
.Range("B" & .Cells(.Rows.Count,
2).End(xlUp).Row + 1 & ":B" & .Cells(.Rows.Count, 3).End(xlUp).Row) =
myItem
.Range("J" & .Cells(.Rows.Count,
10).End(xlUp).Row + 1 & ":J" & .Cells(.Rows.Count, 11).End(xlUp).Row)
= myItem
.Range("P" & .Cells(.Rows.Count,
16).End(xlUp).Row + 1 & ":P" & .Cells(.Rows.Count, 17).End(xlUp).Row)
= myItem
End With

'=========================================




'=======format each sheet in data
storage book==========
If AlreadyUpdated = True Then
Else

If blUpdateFormatting = True Then
'#########################NEW 20AUG09
myStorageBook.Activate

For Each mySheet In
myStorageBook.Worksheets

'check to
see if the storage sheet is being used
'if it
isn't then delete it
If mySheet.Name
< "Input" And mySheet.Name < "Summary" Then
With
mySheet
.Activate

' .Calculate
End With
If
mySheet.Range("C2").Value = "Empty" Then

Application.DisplayAlerts = False

mySheet.Delete

Application.DisplayAlerts = True
Else

mySheet.Range
("D:G,J:L,N:N,O:P,T:T,Z:AB,AJ:AL,AO:AQ,AU:AV").Ent ireColumn.AutoFit
End If
End If
Next
End If
'#########################NEW 20AUG09


End If


'=====only save the storage sheets if
necessary========
If AlreadyUpdated = True Then
myStorageBook.Close False
Else
myStorageBook.Sheets
("Input").Activate
With Application
.ScreenUpdating =
True
.EnableEvents = True
.Calculation =
xlCalculationAutomatic
End With
myStorageBook.Close True
End If
Set myStorageBook = Nothing
'=======
End If
Next j
'========


Set myStorageBook = Nothing '++++++++new



'========tidy up the summary file and then close it
With mySummaryBook.Sheets("Data_Measures")
.Range("A2").FormulaR1C1 = "=RC[1]&RC[2]&RC[3]"
.Range("A2").AutoFill Destination:=.Range("A2:A" & .Cells
(.Rows.Count, 2).End(xlUp).Row)

Set rSource = .Range("A2:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
Set rDest = .Range("A2:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

End With


With mySummaryBook.Sheets("Data_Graphs")
.Range("A4").FormulaR1C1 = "=RC[1]&RC[2]"
.Range("A4").AutoFill Destination:=.Range("A4:A" & .Cells
(.Rows.Count, 2).End(xlUp).Row)

Set rSource = .Range("A4:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
Set rDest = .Range("A4:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

End With




With mySummaryBook.Sheets("Data_Available")
.Range("F4:F100").ClearContents
.PivotTables("PivotTable2").PivotCache.Refresh
Set rSource = .Range(.Cells(5, 14), .Cells(.Cells
(.Rows.Count, 14).End(xlUp).Row, 14))
Set rDest = .Range("F4")
End With
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value



mySummaryBook.Sheets("Data_Available").PivotTables
("PivotTable1").PivotCache.Refresh
mySummaryBook.Sheets(1).Activate
mySummaryBook.Close True
'===========



'===========
Workbooks(ExtractFileName(myFeedFilePath)).Close False
ThisWorkbook.Sheets("Static_Data").Activate


Set rSource = Nothing '=+++++++++
Set rDest = Nothing '++++++++++++
Set mySummaryBook = Nothing
Set oPivCatRange = Nothing

Application.ScreenUpdating = True


End Sub



'================================
'================================




Any help much appreciated
Jason.



On 10 Sep, 16:00, "Peter T" <peter_t@discussions wrote:
What do you mean by "stopping", does code break on a line of code, or does
Excel crash, etc
You say "it all happens in a second instance", *looks like your code is
attempting to open workbooks in the same instance, show us your automation
code.

Difficult to comment with the information given.

Regards,
Peter T

Try and explain what you are doing,"WhytheQ" wrote in message

...



Hello All,

I've previously asked about finding where my long program is stopping.
I've used several Debug.print lines in the code to find where this is
happening..........and it stops just after the line following 1By.....

* * * * * * * * * * * * * * *With Application
* * * * * * * * * * * * * * * * * *..EnableEvents = False
* * * * * * * * * * * * * * * * * *..ScreenUpdating = True
* * * * * * * * * * * * * * * * * *..Calculation = xlCalculationManual
Debug.Print "1Bx" '++++++++
* * * * * * * * * * * * * * * * * * * * *ChDir "R:\Statistics\"
Debug.Print "1By" '++++++++
* * * * * * * * * * * * * * * * * * * * *.Workbooks.Open "R:
\Statistics\book" & xxx & ".xlsx", False, , , , True
Debug.Print "1Bz" '++++++++

* * * * * * * * * * * * * * * * * *..Calculation =
xlCalculationAutomatic
* * * * * * * * * * * * * * * * * *..ScreenUpdating = False
* * * * * * * * * * * * * * * * * *..EnableEvents = True
* * * * * * * * * * * * * * *End With

It loops through the above about 40 times, opening different books -
it doesn't always stop when trying to open the same workbook. Since
finding the problem I've added in the methods (.EnableEvents =
False / .ScreenUpdating = True / .Calculation = xlCalculationManual)
to try to sort things out but it is still occuring.

All the above happens in a second instance of Excel.

Any help much appreciated

Regards,
Jason.- Hide quoted text -

- Show quoted text -

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default Found where program stops, but can't stop it stopping !!
It's difficult to recreate your code environment so at a glance can't
suggest what might cause code to stop, in particular with the automated
instance in Design mode. Perhaps there's something you know about that might
be causing that.

You say other workbooks are still open, presumably they shouldn't be so that
should be easy to track down.

Instead of opening in an automated instance why not open in the same
instance, simply change
'Set myXLApplication = New Excel.Application
Set myXLApplication = Application

Guessing, are you opening several similar workbooks each with similar OnTime
code, if so that's going to cause loads of confusion.

Regards,
Peter T


"WhytheQ" wrote in message
...
Hello Peter

By stopping I mean it just sort of drops out i.e no error occurs but
the program just stops at the point I mentioned. When I then look in
the instance of Excel, the workbook with the code in it is open with
the Design Mode button activated and all other books that the program
has opened upto that point are also open ! Because there isn't an
error generated I had a lot of trouble finding exactly where it was
stopping - that is why I added a lot of Debug.Print labels throughout
the program.

Here's the automation Peter (quite a lot of it!! ....you might have
seen some of it before in my previous posts)......

In Excel1:
'================================
'================================
Option Explicit

Public myXLApplication As Excel.Application
Public myXLWorkbook As Excel.Workbook

Public Const myCodeWorkbook As String = "R:\Statistics\Daily Storage
Book Updater.xlsm"


Sub UpdateStorageBooks()

Application.EnableEvents = True

Set myXLApplication = New Excel.Application
myXLApplication.Visible = True
myXLApplication.Workbooks.Open myCodeWorkbook, , False, , , , True
Set myXLApplication = Nothing

End Sub


'================================
'================================
.........then in the second instance the following runs:


Option Explicit

Private Sub Workbook_Open()

Dim ActionTime As Date

ActionTime = Now() + TimeValue("00:00:10")
Application.OnTime ActionTime, "controlRoutine"

End Sub 'Workbook_Open



'================================
'================================
.............then 10 seconds later the following runs.........


Sub controlRoutine()


blUpdateAll = False
Application.ScreenUpdating = True
If MsgBox("Do you wish to update all storage sheets irrespective
as to whether they have already been saved today?", vbYesNo +
vbDefaultButton2, "Overwrite Existing Files") = vbYes Then
Application.ScreenUpdating = False
blUpdateAll = True
End If
Application.ScreenUpdating = False


blUpdateFormatting = False
Application.ScreenUpdating = True
If MsgBox("Do you wish to update sheet formatting?", vbYesNo +
vbDefaultButton2, "Update formatting") = vbYes Then
Application.ScreenUpdating = False
blUpdateFormatting = True
End If
Application.ScreenUpdating = False


Call UpdateFeedWorkbook
Call UpdateStorageBooksAndSummary

Application.ScreenUpdating = True

MsgBox "Completed Routine!"

End Sub 'controlRoutine


'================================
'================================
..............in the above it is stopping part way through a big loop
in the routine "UpdateStorageBooksAndSummary" which contains the
following.........



Public Sub UpdateStorageBooksAndSummary()


Application.ScreenUpdating = False

'========open the summary file
'open summary file
If IsFileOpen(ExtractFileName(mySummaryFilePath)) = False Then
Workbooks.Open mySummaryFilePath, , False, , , , True
End If
Set mySummaryBook = Workbooks(ExtractFileName
(mySummaryFilePath))
'========


'clear out the data sheets that were previously collated
from the storage sheets
With mySummaryBook
.Sheets("Data_Measures").Range("A2:AZ10000").Clear Contents
.Sheets("Data_MaxMin").Range("A2:AZ10000").ClearCo ntents
.Sheets("Data_Graphs").Range("A4:G10000").ClearCon tents
.Sheets("Data_Graphs").Range("J4:M10000").ClearCon tents
.Sheets("Data_Graphs").Range("P4:R10000").ClearCon tents
End With
'========


'========open the feed file
'open feed file
If IsFileOpen(ExtractFileName(myFeedFilePath)) = False Then
Workbooks.Open myFeedFilePath, , False, , , , True
End If
Set myFeedBook = Workbooks(ExtractFileName(myFeedFilePath))
'========



'========open all storage sheets
i = 1

EndCell = ThisWorkbook.Sheets("Static_Data").Range("C100").E nd
(xlUp).Row

'loop through the category names, which correspond to the
storage book names
'For Each oItem In oPivCatRange.Cells
For j = 6 To EndCell

myItem = ThisWorkbook.Sheets("Static_Data").Cells(j,
3).Value
myStorageName = myItem & ".xlsx"


If myItem < "" Then

'check if NOT saved today;
AlreadyUpdated = False
If FileDateTime(myStorageFileStore &
myStorageName) Date And blUpdateAll = False Then
AlreadyUpdated = True
End If

Debug.Print "1A" '++++++++
Debug.Print myStorageFileStore & myStorageName '++++++++
Debug.Print Application.ScreenUpdating '++++++++
Debug.Print Application.Calculation '++++++++

'=======open each Storage book - always
opens file to move data to summary
Dim myStorageFullPathWay As String
myStorageFullPathWay =
myStorageFileStore & myStorageName

Debug.Print "1B" '++++++++

With Application

.EnableEvents = False
.ScreenUpdating = True
.Calculation = xlCalculationManual
Debug.Print "1Bx" '++++++++
ChDir myStorageFileStore
Debug.Print "1By" '++++++++
.Workbooks.Open CStr
(myStorageFullPathWay), , False, , , , True
Debug.Print "1Bz" '++++++++

.Calculation =
xlCalculationAutomatic
.ScreenUpdating = False
.EnableEvents = True
End With

Debug.Print "1C" '++++++++

' Dim my As String
Set myStorageBook = Workbooks
(myStorageName)

'=======clear out old data if not
already updated
If AlreadyUpdated = True Then
Else
With myStorageBook.Sheets("Input")
.Range
("C6:AZ500").ClearContents
.Range("D2").ClearContents
End With
End If

'=========================================


'=======copy data into Storage sheet
If AlreadyUpdated = True Then
Else
With myFeedBook.Sheets("Pivot")

.Range("E3").Value = myItem

myLastRow = .Cells
(Rows.Count, 4).End(xlUp).Row

Set rSource = .Range("D7:D"
& myLastRow)
Set rDest =
myStorageBook.Sheets("Input").Range("C7")
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value


Set rSource = .Range("B6:B"
& myLastRow)
Set rDest =
myStorageBook.Sheets("Input").Range("D6")
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource = .Range("E6:AZ"
& myLastRow)
Set rDest =
myStorageBook.Sheets("Input").Range("E6")
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09


End With
End If

'=========================================



'=======copy data out of Storage
sheet==========
With Workbooks(myStorageName).Sheets
("Summary")
.Activate

Set rSource = .Range("C5:BG"
& .Range("B46").Value + 4)
Set rDest = mySummaryBook.Sheets
("Data_Measures").Cells(Rows.Count, 4).End(xlUp)(2, 1)
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09


End With
With mySummaryBook.Sheets
("Data_Measures")
.Range("B" & .Cells(.Rows.Count,
2).End(xlUp).Row + 1 & ":B" & .Cells(.Rows.Count, 4).End(xlUp).Row) =
Workbooks(myStorageName).Sheets("Summary").Range(" C2").Value
.Range("C" & .Cells(.Rows.Count,
3).End(xlUp).Row + 1 & ":C" & .Cells(.Rows.Count, 4).End(xlUp).Row) =
myItem
End With
'=======

'copy graph data out of Storage sheet
With Workbooks(myStorageName).Sheets
("All Operator")
.Activate
'#########################NEW 19AUG09

Set rSource = .Range
("AH7:AL43")
Set rDest =
mySummaryBook.Sheets("Data_Graphs").Cells(mySummar yBook.Sheets
("Data_Graphs").Rows.Count, 3).End(xlUp)(2, 1)
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With


rDest.Value = rSource.Value
Set rSource = Nothing
Set rDest = Nothing

Set rSource = .Range
("AJ6:AL6")
Set rDest =
mySummaryBook.Sheets("Data_Graphs").Cells(mySummar yBook.Sheets
("Data_Graphs").Rows.Count, 11).End(xlUp)(2, 1)
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value
Set rSource = Nothing
Set rDest = Nothing


Set rSource = .Range
("Y6:Z136")
Set rDest =
mySummaryBook.Sheets("Data_Graphs").Cells(mySummar yBook.Sheets
("Data_Graphs").Rows.Count, 17).End(xlUp)(2, 1)
With rSource
Set rDest =
rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value
Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09

End With


With mySummaryBook.Sheets("Data_Graphs")
.Range("B" & .Cells(.Rows.Count,
2).End(xlUp).Row + 1 & ":B" & .Cells(.Rows.Count, 3).End(xlUp).Row) =
myItem
.Range("J" & .Cells(.Rows.Count,
10).End(xlUp).Row + 1 & ":J" & .Cells(.Rows.Count, 11).End(xlUp).Row)
= myItem
.Range("P" & .Cells(.Rows.Count,
16).End(xlUp).Row + 1 & ":P" & .Cells(.Rows.Count, 17).End(xlUp).Row)
= myItem
End With

'=========================================




'=======format each sheet in data
storage book==========
If AlreadyUpdated = True Then
Else

If blUpdateFormatting = True Then
'#########################NEW 20AUG09
myStorageBook.Activate

For Each mySheet In
myStorageBook.Worksheets

'check to
see if the storage sheet is being used
'if it
isn't then delete it
If mySheet.Name
< "Input" And mySheet.Name < "Summary" Then
With
mySheet
.Activate

' .Calculate
End With
If
mySheet.Range("C2").Value = "Empty" Then

Application.DisplayAlerts = False

mySheet.Delete

Application.DisplayAlerts = True
Else

mySheet.Range
("D:G,J:L,N:N,O:P,T:T,Z:AB,AJ:AL,AO:AQ,AU:AV").Ent ireColumn.AutoFit
End If
End If
Next
End If
'#########################NEW 20AUG09


End If


'=====only save the storage sheets if
necessary========
If AlreadyUpdated = True Then
myStorageBook.Close False
Else
myStorageBook.Sheets
("Input").Activate
With Application
.ScreenUpdating =
True
.EnableEvents = True
.Calculation =
xlCalculationAutomatic
End With
myStorageBook.Close True
End If
Set myStorageBook = Nothing
'=======
End If
Next j
'========


Set myStorageBook = Nothing '++++++++new



'========tidy up the summary file and then close it
With mySummaryBook.Sheets("Data_Measures")
.Range("A2").FormulaR1C1 = "=RC[1]&RC[2]&RC[3]"
.Range("A2").AutoFill Destination:=.Range("A2:A" & .Cells
(.Rows.Count, 2).End(xlUp).Row)

Set rSource = .Range("A2:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
Set rDest = .Range("A2:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

End With


With mySummaryBook.Sheets("Data_Graphs")
.Range("A4").FormulaR1C1 = "=RC[1]&RC[2]"
.Range("A4").AutoFill Destination:=.Range("A4:A" & .Cells
(.Rows.Count, 2).End(xlUp).Row)

Set rSource = .Range("A4:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
Set rDest = .Range("A4:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

End With




With mySummaryBook.Sheets("Data_Available")
.Range("F4:F100").ClearContents
.PivotTables("PivotTable2").PivotCache.Refresh
Set rSource = .Range(.Cells(5, 14), .Cells(.Cells
(.Rows.Count, 14).End(xlUp).Row, 14))
Set rDest = .Range("F4")
End With
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value



mySummaryBook.Sheets("Data_Available").PivotTables
("PivotTable1").PivotCache.Refresh
mySummaryBook.Sheets(1).Activate
mySummaryBook.Close True
'===========



'===========
Workbooks(ExtractFileName(myFeedFilePath)).Close False
ThisWorkbook.Sheets("Static_Data").Activate


Set rSource = Nothing '=+++++++++
Set rDest = Nothing '++++++++++++
Set mySummaryBook = Nothing
Set oPivCatRange = Nothing

Application.ScreenUpdating = True


End Sub



'================================
'================================




Any help much appreciated
Jason.



On 10 Sep, 16:00, "Peter T" <peter_t@discussions wrote:
What do you mean by "stopping", does code break on a line of code, or does
Excel crash, etc
You say "it all happens in a second instance", looks like your code is
attempting to open workbooks in the same instance, show us your automation
code.

Difficult to comment with the information given.

Regards,
Peter T

Try and explain what you are doing,"WhytheQ" wrote in
message

...



Hello All,

I've previously asked about finding where my long program is stopping.
I've used several Debug.print lines in the code to find where this is
happening..........and it stops just after the line following 1By.....

With Application
.EnableEvents = False
.ScreenUpdating = True
.Calculation = xlCalculationManual
Debug.Print "1Bx" '++++++++
ChDir "R:\Statistics\"
Debug.Print "1By" '++++++++
.Workbooks.Open "R:
\Statistics\book" & xxx & ".xlsx", False, , , , True
Debug.Print "1Bz" '++++++++

.Calculation =
xlCalculationAutomatic
.ScreenUpdating = False
.EnableEvents = True
End With

It loops through the above about 40 times, opening different books -
it doesn't always stop when trying to open the same workbook. Since
finding the problem I've added in the methods (.EnableEvents =
False / .ScreenUpdating = True / .Calculation = xlCalculationManual)
to try to sort things out but it is still occuring.

All the above happens in a second instance of Excel.

Any help much appreciated

Regards,
Jason.- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't find bug because program justs Stops !! WhytheQ Excel Programming 3 August 25th 09 04:42 PM
Excel functions not available then program stops K Excel Discussion (Misc queries) 0 August 20th 09 07:49 PM
Interrupting long calculations in Excel? Hi all, I am having a verylong calculation in Excel using VBA. Of course I know I can hit ESC orCTRL+BREAK to interrupt the VBA program. But I found due to too long runningprocess, the Excel window stop to LunaMoon Excel Discussion (Misc queries) 3 July 31st 08 04:47 PM
Interrupting long calculations in Excel? Hi all, I am having a verylong calculation in Excel using VBA. Of course I know I can hit ESC orCTRL+BREAK to interrupt the VBA program. But I found due to too long runningprocess, the Excel window stop to LunaMoon Excel Programming 3 July 31st 08 04:47 PM
VBA Stops Before competing the program ch Excel Programming 3 February 14th 06 03:25 PM


All times are GMT +1. The time now is 12:34 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement