2

I would like to pull data from a closed worksheet using GetOpenFileName and VLOOKUP, but I am new to VBA and unfamiliar with the syntax.

I keep hitting an "Automation error" or "Object error" that doesn't specify what is wrong. I believe my range arg in VLOOKUP is incorrect but am not sure what it should be. Can anyone help me please?

Sub GetAmazonInventory()

Dim wbk As Workbook
Set wbk = Workbooks.Open(FileName:=Application.GetOpenFilename, ReadOnly:=True)
wbk.Activate

Dim shtName As String
shtName = wbk.Worksheets(1).Name

Dim sh As Worksheet
Set sh = wbk.Worksheets(shtName)

wbk.Close

ActiveCell.Value = Application.IfError(Application.VLookup(ActiveCell.Offset(0, -12), sh.Range("A2:F900"), 5, False), 20)

End Sub
suhg
  • 29
  • 2
  • 1
    Just to clarify - you closed the workbook with `wbk.Close` and want to know why your `sh.Range("A2:F900")` isn't working when `sh` is referring to the closed workbook? – K.Dᴀᴠɪs Oct 26 '18 at 23:53
  • @K.Davis I'm pretty new to VBA :( Is it impossible to grab the data when the wbk is closed? If I keep it open, I am not sure how to reference the ActiveCell and get the first Arg for the VLookUp function. – suhg Oct 26 '18 at 23:58
  • You can set a variable to the active cell first. I will add an answer – K.Dᴀᴠɪs Oct 26 '18 at 23:59

2 Answers2

2

The reason for your error is that you closed the workbook, and are still attempting to pull a range from that closed workbook.

If you are wanting to keep the new workbook open but use the original ActiveCell's range, then you can just create a variable for that cell.

Sub GetAmazonInventory()

    ' Set a reference to your 'Active Cell'
    Dim myCell As Range
    Set myCell = ActiveCell

    Dim wbk As Workbook, ws As Worksheet
    Set wbk = Workbooks.Open(Filename:=Application.GetOpenFilename, ReadOnly:=True)
    Set ws = wbk.Worksheets(1)

    myCell.Value = Application.IfError(Application.VLookup(myCell.Offset(0, -12), _
            ws.Range("A2:F900"), 5, False), 20)

    wbk.Close

End Sub

Side Note: You do not need to get the worksheet's name first just to set the worksheet. So I removed that.

I would also add some validation that the activecell is at least in the correct column / row. If your active cell should always be in column B for example, then something like:

If myCell.Column <> 2 then
    Msgbox "ActiveCell is not in the correct column. Please reselect!"
    Exit Sub
End If
K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
  • 1
    Looks like ActiveCell has to be in column M or right of column M (i.e. 13th column or higher). –  Oct 27 '18 at 00:24
  • 1
    True. I just did it because for many it's easier to know that `B = 2` more than `M = 13` xD – K.Dᴀᴠɪs Oct 27 '18 at 00:29
1

You can reference a closed workbook with a VLOOKUP formula on the worksheet. You will need to store a string representing a complete qualified path to the range before closing the workbook.

Since you've given no indication what ActiveCell is and use Offset to 

Sub GetAmazonInventory()

    Dim wbk As Workbook, addr as string

    Set wbk = Workbooks.Open(FileName:=Application.GetOpenFilename, ReadOnly:=True)
    addr = wbk.Worksheets(1).Range("A2:F900").address(external:=true)
    wbk.Close

    ActiveCell.formula = "=iferror(vLookup(" & ActiveCell.Offset(0, -12).address(0, 0) & _
                           ", " & addr & ", 5, False), 20)"

End Sub