Member Avatar for divakar.it

Hello,

I am getting a below error while accessing excel second time

run time error 1004 method sheets of object_global failed in vb6

When i run the code at first time to open a excel and take a copy of the sheet, it works fine however when i run the code second time am getting the above error

Please find the code snippet where exactly am getting error

For N = i - 1 To 0 Step -1
        xlTmp.Sheets("Practice_wise_Summary").Select
        xlTmp.Sheets("Practice_wise_Summary").Copy Before:=Sheets("3")
        xlTmp.Sheets("Practice_wise_Summary (2)").Select
        xlTmp.Sheets("Practice_wise_Summary (2)").Name = P(N) & "_Summary"
        MyRecSet1.CursorLocation = adUseClient
        xlTmp.Cells(3, 2).Value = P(N)
            MyRecSet1.Open ("Exec [SP_PRACWISESUMMARY1] '" & P(N) & "'"), MyConn, adOpenForwardOnly
            MyRecSet1.MoveFirst
            With MyRecSet1
            Do While Not .EOF
            xlTmp.Cells(MyRecSet1![RowIdinExcel], MyRecSet1![ColIdinExcel]).Value = MyRecSet1![Metrics]
            MyRecSet1.MoveNext
            'MyRecSet1.Close
            Loop
            End With
            MyRecSet1.Close
    
            MyRecSet1.CursorLocation = adUseClient
            MyRecSet1.Open ("Exec [SP_PRACWISESUMMARY2] '" & P(N) & "'"), MyConn, adOpenForwardOnly
            C = MyRecSet1.RecordCount
                
            Cel = Array(7, 9, 10, 11, 12)
                
            For j = 0 To MyRecSet1.Fields.Count - 1
            K = 7
            MyRecSet1.MoveFirst
            Do While Not MyRecSet1 Is Nothing And Not MyRecSet1.BOF And Not MyRecSet1.EOF
                xlTmp.Cells(K, Cel(j)).Value = MyRecSet1(j).Value
                K = K + 1
                MyRecSet1.MoveNext
            Loop
            Next j
            MyRecSet1.Close
    
    Next N

The error coming when the VB reading the below line

xlTmp.Sheets("Practice_wise_Summary").Copy Before:=Sheets("3")

Please help me whats wrong in the code. I have added the below line at the end of the code but even then i am getting the error

Set xlTmp = Nothing
  • 2 Contributors
  • 3 Replies
  • 623 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by AndreRet
Member Avatar for divakar.it

I found the solution for this. Hence closing the thread

Member Avatar for divakar.it

Sorry the solution is

Need to mention Excel object reference everwhere you use Excel objects

xlTmp.Sheets("Practice_wise_Summary").Copy Before:=xlTmp.Sheets("3")
Member Avatar for AndreRet

Nicely done. Some kudos for you!;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.