Get Data from a seperate Workbook VBA error

[gheyman]

This is getting an error on: BOE_WrkBk = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")

It open and lets me select a workbook, but as soon as I do it errors on the line of code above.
Run-time error '91:
Object variable or With block variable not set

I don't know what this means. I thought I was setting the variable.

Sub WBS_Dictionary_Data()

    Dim BOE_WrkBk As Workbook
    Dim Pricing_WrkBk As Workbook
    Dim BOELstRow As Long

    Set Pricing_WrkBk = ThisWorkbook
    BOE_WrkBk = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    
    If BOE_WrkBk <> False Then
        Workbooks.Open (BOE_WrkBk)
        Set BOE_WrkBk = ActiveWorkbook
        
         If Not Evaluate("isref(WBS_Dictionary!a1)") Then
            MsgBox "The Selected Workbook does not contain a WBS_Dictionary tab, check to validate the tab is named correctly or select a different Workbook"
            Exit Sub
        Else
        
    ActiveWorkbook.Sheets("WBS_Dictionary").Activate
    BOE_WrkBk = ws.Range("C" & Rows.Count).End(xlUp).Row
    
    Range("B9:I" & BOE_WrkBk).Select
    Selection.Copy
    
    
    
    End If
    End If
    
End Sub

What am I doing wrong?

Once I get the data from the opened workbook I want to past value it into the workbook where I am running the code from.

 

[John_w]

Change BOE_WrkBk to a Variant.

 

[gheyman]

Thanks, John. But then how do I identify what I opened as a workbook so that I can go to it in my code? When I change it to a variant I then error on:

Workbooks.Open (BOE_WrkBk)

 

[John_w]

The change works for me. The original error occurs because GetOpenFilename returns a Variant and you were assigning it to a Workbook object.

With BOE_WrkBk As Variant, this variable is a Variant/String after GetOpenFileName, and a Variant/Object after Set BOE_WrkBk = ActiveWorkbook.

You could replace:

        Workbooks.Open (BOE_WrkBk)
        Set BOE_WrkBk = ActiveWorkbook

with:

Set BOE_WrkBk = Workbooks.Open(BOE_WrkBk)

Using a Variant for 2 purposes is a bit 'dirty', so I would use 2 variables - a Variant and a Workbook variable.

 

[gheyman]

Thanks

 

[gheyman]

Strange, it worked when I changed the code but now when I run it I get an error on that new line of code:

Set BOE_WrkBk = Workbooks.Open(BOE_WrkBk)