Excel VBA

[cunningruss]

Learning VBA on the job, I'm not able to get the following code to work. Would appreciate some support. I receive Runtime error '91' at Line 6

Dim xlApp As New Excel.Application
Dim xlBook As Excel.WorkBook
Dim xlSheet As Excel.WorkSheet
Dim FoundCell As Range
xlApp.WorkBooks.Open ("C:\temp\inventory.xlsx")
xlBook.WorkSheets("Sheet1").Activate
Set xlSheet = xlBook.ActiveSheet
With xlSheet.Range("B3:B145")
Set FoundCell = .Find(What:=MyName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End With
.....

 

[akmatz]

Try this. This two lines I made changes to have a double asterick (**) at the end:

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim FoundCell As Range
xlApp.Workbooks.Open ("C:\temp\inventory.xlsx")
Worksheets("Sheet1").Activate '**
'xlBook.Worksheets("Sheet1").Activate
'Set xlSheet = xlBook.ActiveSheet
Set xlSheet = ActiveWorkbook.ActiveSheet '**
With xlSheet.Range("B3:B145")
Set FoundCell = .Find(What:=MyName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End With

 

[JLGWhiz]

You need to set xlBook instead of xlApp.Workbooks.open("C\temp\inventory.xlsx
Code:
Set xlBook = Worbooks.Open("C:\temp\inventory.xlsx")
Code:

 

[cunningruss]

Thanks for solution, with reference in hand I can't resolve Run-time error '424' at line 15. Because passing MyName as variant?


Dim MyName As Variant
Dim strName As String
MyName =Application.UserName
Selection.TypeText (MyName)
strName = ActiveDocument.Name
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim FoundCell As Range
xlApp.Workbooks.Open ("C:\temp\inventory.xlsx")
Worksheets("Sheet1").Activate
'Set xlSheet = xlBook.ActiveSheet
With xlSheet.Range("B3:B145")
Set FoundCell = .Find(What:=MyName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not FoundCell Is Nothing Then
.Rows(FoundCell).EntireRoll.Copy
ActiveDocument.Bookmarks(Equipment).Select
Selection.Paste
Else
' FindNext code
End If
End With