CODEWITHSUNDEEP
excelvba
gajus21
gajus21

Reputation: 3

Why I have a error when I try to open second excel file?

I want to open and work on the two workbooks but I still have a Error 'Run Time Error '9'' Where I make mistake? I make this code and I have a problem in Line when I Set Wbook1 and Wbook2 

Sub List()
Dim Wbook1 As Workbook, Wbook2 As Workbook
Dim strWbook1 As String, strWbook2 As String

strWbook1 = "C:\CW3_test.xls" 
strWbook2 = "C:\CW3 Document register.xls" 

Set Wbook1 = Workbooks(strWbook1)
Set Wbook2 = Workbooks.Open(strWbook2)

Dim ark1 As Worksheet, ark2 As Worksheet
Set ark1 = Wbook1.Worksheets("Arkusz1")
Set ark2 = Wbook1.Worksheets("MAN")
Dim NextRow As Long
Dim LastRow As Long, lastRow2 As Long
Dim StartTime As Double

StartTime = Timer

Wbook1.ark2.Select
Wbook1.ark2.Cells.Select
Selection.ClearContents

LastRow = Wbook1.ark1.Cells(Rows.Count, 2).End(xlUp).Row

Wbook2.Active
ActiveWorkbook.Close savechanges:=False

 MsgBox "Time:" & Format((Timer - StartTime) / 86400, "hh:mm:ss") 
End Sub```

Upvotes: 0

Views: 45

Answers (1)

Tim Williams
Tim Williams

Reputation: 166825

Wbook1.ark2.Select

ark2 is a worksheet variable - it's not a property or method of Wbook1.

Just ark2.Select would work fine, but there's typically no need to activate/select anything when working with Excel in VBA.

Sub List()

    Dim Wbook1 As Workbook, Wbook2 As Workbook
    Dim strWbook1 As String, strWbook2 As String

    strWbook1 = "C:\CW3_test.xls" 
    strWbook2 = "C:\CW3 Document register.xls" 

    Set Wbook1 = Workbooks(strWbook1)
    Set Wbook2 = Workbooks.Open(strWbook2)

    Dim ark1 As Worksheet, ark2 As Worksheet
    Set ark1 = Wbook1.Worksheets("Arkusz1")
    Set ark2 = Wbook1.Worksheets("MAN")

    Dim NextRow As Long
    Dim LastRow As Long, lastRow2 As Long
    Dim StartTime As Double

    StartTime = Timer

    ark2.Cells.ClearContents  'no need to activate/select

    LastRow = ark1.Cells(ark1.Rows.Count, 2).End(xlUp).Row

    Wbook2.Close savechanges:=False 'no need to activate/select

    MsgBox "Time:" & Format((Timer - StartTime) / 86400, "hh:mm:ss") 

End Sub

Upvotes: 2

Related Questions