Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Runtime error hangs application

Status
Not open for further replies.
jazminecat23

jazminecat23

Programmer
Mar 16, 2007
103
US
Hi again -

well just when I thought it was safe to deploy...

I made another copy of my excel project, without changing the code, because i need to use another array on my first sheet, and now I get a runtime error, "copy of sheets failed". It's in red where this happens below. To make it even more fun, it creates 2 "invisible" new excel workbooks that I can't close without using task manager.

Code: 
Private Sub CmdRunSpreadsheet_Click()
'
'
' To create monthly budget distribution automatically

'Check for login to spreadsheet server
    If MsgBox("Are you logged in to Spreadsheet Server?", vbYesNo, "Login") = vbNo Then
    Exit Sub
    Else

 
    
'1. Get values from DistList sheet to creat individual Monthly Budget Reports
    Dim r As Range
    For Each r In Sheets("DistList").Range(Sheets("DistList").[A1], Sheets("DistList").[A1].End(xlDown))
      sDept = r.Value
      nVal1 = r.Offset(0, 1).Value
      nVal2 = r.Offset(0, 2).Value
      'nVal3 = r.Offset(0, 3).Value
    'BudgetStatus is where the processing occurs
      With Sheets("BudgetStatus")
          sName = UCase(Format(DateSerial(.[G2], .[G4], 1), "mmmyy") & Left(sDept, 3)) & nVal2
         .Cells(6, "G").Value = nVal1
         .Cells(7, "G").Value = nVal2
         '.Cells(11, "G").Value = nVal3
      End With

 '2. Unhide GXE sheet - must be unhidden for detail reports to expand correctly

     Sheets("GXE Source").Visible = True
       
  
 '3. calculate
    Application.Calculate
    
 
 '4. Generate Detail Reports
    Application.Run ("ExpandDetailReports")
    

 
'5. Hide button, worksheets, and all extraneous rows on main worksheet
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True
  
    cmdRunSpreadsheet.Visible = False
    'don't need to hide these, they're not copied over anyway.
    'Sheets("GXE Source").Visible = False
    'Sheets("DistList").Visible = False
    
[red]
'6. Copy sheets to new workbook
   
    Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
        "Detailed Report", "JE Detail")).Copy
[/red]

    Set wbNew = ActiveWorkbook
    With wbNew
        'below line works
        .Sheets("JE Detail").Columns("D:F").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("H:L").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("N:Q").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("V:Z").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("AA:AF").EntireColumn.Hidden = True
    End With
    Sheets("BudgetStatus").Select
    
'7. Replace formulas with values in new workbook

    For Each ws In wbNew.Worksheets
      ws.Cells.Copy
      ws.Cells.PasteSpecial xlPasteValues
    Next
    
'8. save and close new workbook
        
    With wbNew
     .SaveAs "I:\SpreadsheetServer\Month End Reports" & "\" & sName & ".xls"
    .Close
    End With
 
Next

End If

'Lastly, unhide everything on the template so it's ready to use next time.

    cmdRunSpreadsheet.Visible = True
    Sheets("GXE Source").Visible = True
    Sheets("DistList").Visible = True
    
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = False
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = False


End Sub

When I get the error and select debug, that's where the trouble begins. The above red item is highlighted - even though this works perfectly in the original spreadsheet. And it works fine for the first few items in the array.

Once this error has happened, I go back to the main excel window, and if I go to the Window menu item, I will see the sheet I'm working on,(Rdist1.xlt) and "Book 1".

If I select "Book 1" from the Window menu, it simply tiles Rdist1.xlt with empty space. Clicking on either Rdist1.xlt or on the empty space only maximizes Rdist1.xlt. I cannot get rid of the ghost of Book1 unless I end task.

Then, when I end task, I find that there is not just one ghost, but two. The one in the window menu, and another that only shows up when ending task asks if I want to save changes to Book2.

You can imagine what fun troubleshooting this has become...

Anyone have any insight for me? Thanks very much, and have a good weekend.

 
Sort by date Sort by votes



Hi,

I think that I may have suggested (and if I never did, I should have) explicitly referencing every workbook, sheet and range object in your code.

Hanging [tt]Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
"Detailed Report", "JE Detail")).Copy[/tt] out there, without a reference to a specific workbook is asking for trouble, big time.

Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
<sheepish>
yep. you did indeed warn me.
</sheepish>

Ok, I've put the offending bits inside of a
With BSDIST2....End With statement. Are there other bits that are dangling out there waiting to bite me as well? Do I need to put steps 2-5 inside a with statement as well?

Thank you Skip, you've been unbelievably patient with me and I can't tell you how much I appreciate it.
 
Upvote 0 Downvote




Any time you are dealing with multiple workbooks and sheets, you can get snake bit, (Texas talk ;-))

Just make it a practice to be rigerous. Otherwise, you might end up be rigor mortis. (sorry!)

Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
I spoke too soon. That actually didn't fix it.

Right now, I'm working directly on the .xlt file. it's named BSDist2.xlt. The offending code now reads:

With BSDist2
Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
"Detailed Report", "JE Detail")).Copy
End With

It works for about three iterations through the array and then errors out, leaving me with the 2 ghost files again.

 
Upvote 0 Downvote
Ok. I've put breaks in the code so I could step through it - here's the problem:

this

Code: 
Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True

errors out now with Runtime error 1004, application-defined or object defined error

whether or not it is enclosed inside
Code: 
with BSDist2
...
End WIth

this is so strange that it would start acting up now when it was working fine early in the week.
 
Upvote 0 Downvote
...whether or not it is enclosed inside..."

Enclosing in a With...End With is not the issue. It is REFERENCING that is the issue...
Code: 
 With BSDist2[b][red]
    v
    v
    v
    .[/red][/b]Sheets(Array([i]whatever[/i])).Copy[b][red]
    ^
    ^
    ^[/red][/b]

 End With
Notice the [red]RED DOT[/red] before Sheets that references BSDist2

Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
Hm, well that gives me a runtime error 424, object required
Code: 
   With BSDist2
    .Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy
    End With


If I change it to be
With ("BSDist2")....


I get a compile error, with object must be user-defined object, type or variant.

I'm also confused why I need to specify the workbook since at this point in the code, this workbook *should* be the only one open. The new workbooks close with each iteration. So the problem only arises when it creates these ghost workbooks. Of course, it's probable the enduser would have other workbooks open when they run this, so it's better to figure this bit out. But I"m wondering if there's any way to put in some error trapping (which I'm not entirely sure how to approach) to find out when/where these ghost workbooks are being created, since the only way to get rid of them is to end task on excel. I guess we should solve this problem above first, huh.
 
Upvote 0 Downvote




Somehow, you have lost the reference to BSDist2, it seems.

When you get that error, DEBUG and test BSDist2 in a Watch Window.

Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
Ok. at that point in the code, it says BSDist2=Empty

This time through I am getting "the object invoked has disconnected from it's clients"

banging head on keyboard.
 
Upvote 0 Downvote
so, I thought about when this started, and it was when I changed my data. If my data reads like this it works:

A B C D
AdminSvc 00324 32400 test1
AdminSvc 00324 32410 test2
AdminSvc 00324 32420 test3
AdminSvc 00324 32430 test4



I ran it 6 times with that data, closed the program, reopened, ran it again, perfect.

but if I change column d to have text with spaces, it starts acting up:

AdminSvc 00324 32400 testing the waters
AdminSvc 00324 32410 to see if it floats
AdminSvc 00324 32420 maybe it will
AdminSvc 00324 32430 maybe it won't

It starts by giving me an error on that same piece, first that the copy method failed. Then I have the ghost workbooks, and it all goes pear shaped from there.


 
Upvote 0 Downvote
oy. well, that's not true now either it seems. this inconsistent results is making me nuts. I can't seem to pinpoint where the problem is, because it's not the same every time. I mean it's either this:
With BSDist2
Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
"Detailed Report", "JE Detail")).Copy
End With

which, if I put . before the Sheets, throws an object not defined error, but as is, is inconsistently failing on the object copy, or the other errors as above. I'm going to copy in my entire code as it stands, because i'm not convinced that there's not a larger problem here. Between at least 3 different error patterns today alone, this is really starting to make me wonder if something larger isn't wrong:

Code: 
Private Sub CmdRunSpreadsheet_Click()
[green]
'
'
' To create monthly budget distribution automatically

'Check for login to spreadsheet server
[/green]
    If MsgBox("Are you logged in to Spreadsheet Server?", vbYesNo, "Login") = vbNo Then
    Exit Sub
    Else

 
 [green]   
'1. Get values from DistList sheet to creat individual Monthly Budget Reports
[/green]
    Dim r As Range
    For Each r In Sheets("DistList").Range(Sheets("DistList").[A1], Sheets("DistList").[A1].End(xlDown))
      sDept = r.Value
      nVal1 = r.Offset(0, 1).Value
      nVal2 = r.Offset(0, 2).Value
      nVal3 = r.Offset(0, 3).Value

[green]      
    'BudgetStatus is where the processing occurs
[/green]
      With Sheets("BudgetStatus")
          sName = UCase(Format(DateSerial(.[G2], .[G4], 1), "mmmyy") & Left(sDept, 3)) & nVal2
         .Cells(6, "G").Value = nVal1
         .Cells(7, "G").Value = nVal2
         .Cells(11, "G").Value = nVal3
      End With
[green]
 '2. Unhide GXE sheet - must be unhidden for detail reports to expand correctly
[/green]

     Sheets("GXE Source").Visible = True
     Debug.Print ActiveWorkbook.Name
[red]
debug shows BSDist2.xlt as the active workbook
[/red]
       
[green]  
 '3. calculate
[/green]
    Application.Calculate
    
 
 '4. Generate Detail Reports
    Application.Run ("ExpandDetailReports")
    
    

 [green]
'5. Hide button, worksheets, and all extraneous rows on main worksheet
[/green]

   Sheets("BudgetStatus").Select
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True
   
    
  
    cmdRunSpreadsheet.Visible = False
    
     
[green]
'6. Copy sheets to new workbook
[/green]

    Debug.Print ActiveWorkbook.Name
[red]
debug shows BSDist2.xlt as the active workbook at this point
[/red]
    
   With BSDist2
    Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy
    End With
    
    
    Set wbNew = ActiveWorkbook
    With wbNew
        'below line works
        .Sheets("JE Detail").Columns("D:F").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("H:L").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("N:Q").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("V:Z").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("AA:AF").EntireColumn.Hidden = True
        'hide zero lines
        'Application.Run ("HideZeroLines")
    End With
    Sheets("BudgetStatus").Select
    Debug.Print ActiveWorkbook.Name
[red]
debug shows Bookx as the active workbook where x is a numeral
[/red]
    
 [green]   
'7. Replace formulas with values in new workbook
[/green]
    For Each WS In wbNew.Worksheets
      WS.Cells.Copy
      WS.Cells.PasteSpecial xlPasteValues
    Next
 [green]   
'8. save and close new workbook
 [/green]       
    With wbNew
     .SaveAs "I:\SpreadsheetServer\Month End Reports" & "\" & sName & ".xls"
    .Close
    End With
    
    
    Debug.Print ActiveWorkbook.Name
[red]
debug shows BSDist2.xlt as the active workbook
[/red]
 
Next

End If
[green]
'Lastly, unhide everything on the template so it's ready to use next time.
[/green]

    cmdRunSpreadsheet.Visible = True
    Sheets("GXE Source").Visible = True
    Sheets("DistList").Visible = True
    
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = False
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = False




End Sub

so far today, I've gotten the following errors on that one section:

runtime error, "copy of sheets failed".

Runtime error 1004, application-defined or object defined error

and

the object invoked has disconnected from it's clients

oy vey. is it too early to drink?

;-)






 
Upvote 0 Downvote



Debug.Print ActiveWorkbook.Name
will not show you what the OBJECT BSDist2 is.

Debug.Print BSDist2.Name

will, however.


Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
Tip: use the Option Explicit instruction ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
ok, if i use option explicit, on the very first pass through, it errors on

Sheets("GXE Source").Visible = True
Debug.Print BSDist2.Name

with variable not defined for BSDist2.Name

I'm obviously not an excel programmer. my thing is access. i'm totally lost that i have to actually create a variable to refer to the spreadsheet in which this code resides??

even more confused than ever now. I really really hope y'all can help me figure this out. thanks!
 
Upvote 0 Downvote
You may try to replace this:
With BSDist2
Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
"Detailed Report", "JE Detail")).Copy
End With
With this:
Workbooks("BSDist2.xlt").Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
"Detailed Report", "JE Detail")).Copy

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Ok, I can try that - do I have ot have the .xlt on the end - I want to have this as a template, and when the user uses it as such, it won't have the .xlt file extension.
 
Upvote 0 Downvote
this

Code: 
   Workbooks("BSDist2.xlt").Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy

= error - method copy of objects sheets failes

this
Code: 
    Workbooks("BSDist2").Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy
= runtime error 9, subscript out of range.

sheesh. who knew it would be this difficult just to get it to copy and paste some spreadsheets for me....

 
Upvote 0 Downvote
Is the code in the BSDist2 class module ?
If so, you may try this:
ThisWorkbook.Activate
Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
"Detailed Report", "JE Detail")).Copy

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
hm, well, that doesn't seem to effect it either.

I ran it 6 or 7 times in a row (iterating through 4 items in my data sheet), and it ran fine. The error seems to be random. I run it once more and I get

Runtime error '-2147417848 (80010108)':
Method 'Copy' of object 'Sheets' failed.

step 6 in my code above now reads
Code: 
ThisWorkbook.Activate
    Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy

All of this code is called on the click of a command button on my template. it lives in VBA in VBAProject (BSDist2.xlt)--> Microsoft Excel Objects -->Sheet1 (BudgetStatus)

i am running excel/office 2000.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
f0rg3tfu1
  • Locked
  • Question
VBA Error in Excel
Replies
11
Views
717
combo
combo
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
796
huiettcm
huiettcm
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
3K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top