Получить данные из закрытой книги с помощью getopenfilename и vlookup

Question

Я хотел бы получить данные из закрытого листа, используя GetOpenFileName и VLOOKUP, но я новичок в VBA и не знаком с синтаксисом.

Я продолжаю нажимать "Ошибка автоматизации" или "Ошибка объекта", в которой не указано, что не так. Я считаю, что мой диапазон arg в VLOOKUP неверен, но я не уверен, каким он должен быть. Кто-нибудь может мне помочь?

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

Answer 1

Причиной вашей ошибки является то, что вы закрыли рабочую книгу и все еще пытаетесь извлечь диапазон из этой закрытой книги.

Если вы хотите сохранить новую книгу открытой, но используйте оригинальную ActiveCellдиапазон, то вы можете просто создать переменную для этой ячейки.

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

Примечание: Вам не нужно сначала получать имя листа, чтобы установить лист. Так что я убрал это.

Я также добавил бы некоторую проверку того, что активная ячейка находится по крайней мере в правильном столбце / строке. Если ваша активная ячейка всегда должна быть в столбце B, например, то что-то вроде:

If myCell.Column <> 2 then
    Msgbox "ActiveCell is not in the correct column. Please reselect!"
    Exit Sub
End If

Answer 2

Вы можете ссылаться на закрытую книгу с формулой VLOOKUP на листе. Вам нужно будет сохранить строку, представляющую полный путь к диапазону, прежде чем закрывать книгу.

Поскольку вы не указали, что такое ActiveCell, и используйте Offset для

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