0

I am trying to open a workbook so I can read data from it and populate fields in another worksheet. When I run the code, it isn't detecting the workbook or opening it. The workbook is on a shared drive and must be opened read only.

When I tried to set "wb", a workbook variable, set wb = Workbooks("filepath"), when debugging it is showing wb = Empty.

I changed the code slightly to use a With statement instead, but still have the same problem. The code jumps from the With Workbooks.Open to the line below, then back and forth between the two forever, not allowing me to close the workbook.

I eventually have to force close and reopen the file. I can break the code, but it immediately starts the function again without giving me a chance to save or close the workbook. Code below, please help me understand what is happening.

[EDIT]: When I first manually open the parts book, then replace the Workbooks.Open line with this code: Set wb = Workbooks("New Parts Book - Official.xlsx") and type the function into a cell, I have no issues and it works just fine (also requires declaration Dim wb as Workbook). The cell formula looks like this: =Lookup_By_PN([@[Part Number]],"name") where the part number argument is coming from a named table column on the same row as the function cell.

Function Lookup_By_PN(ByVal PN As String, Optional ByVal returnField As String = "Price") As Variant
Dim vCell As Variant, result As Variant
result = ""
With Workbooks.Open(Filename:="Z:\Shared\Materials\Parts Book\New Parts Book - Official.xlsx", UpdateLinks:=0, ReadOnly:=1)
For Each vCell In .Sheets("PARTS BOOK").Range("$F$260:$F$3872")
    With vCell
        If InStr(1, .Value2, PN, vbTextCompare) > 0 Then
            Select Case True
                Case InStr(1, returnField, "Price", vbTextCompare) > 0
                    If IsNumeric(.Offset(, 3).Value2) Then
                        result = .Offset(, 3).Value2
                    Else
                        result = .Offset(, 2).Value2
                    End If
                Case InStr(1, returnField, "Name", vbTextCompare) > 0
                    result = .Value2
                Case InStr(1, returnField, "Size", vbTextCompare) > 0
                    Dim quotePosR As Integer, spacePosL As Integer
                    quotePosR = InStr(1, .Value2, """", vbTextCompare)
                    spacePosL = InStrRev(.Value2, " ", quotePosR, vbBinaryCompare)
                    result = Evaluate(Replace(Mid(.Value2, spacePosL + 1, quotePosR - spacePosL - 1), "-", "+", compare:=vbTextCompare))
                Case InStr(1, returnField, "Type", vbTextCompare) > 0
                    result = .Offset(, -2).Value2
                Case Else
                    result = "Not Found"
            End Select
        End If
    End With
Next vCell
End With
    Lookup_By_PN = result
End Function
Tommy
  • 25
  • 1
  • 7
  • 1
    Are you calling the function from a worksheet formula? – Excelosaurus Dec 11 '18 at 19:38
  • 1
    I wouldn't do the `With Worbooks.Open(...` thing. Go back to setting a variable and then try to figure out why the variable is not getting set. – StoneGiant Dec 11 '18 at 19:42
  • You open the workbook but never close it... It will not close itself just because it goes out of scope. – Tim Williams Dec 11 '18 at 19:55
  • The workbook I am unable to close is the one I am working in. The workbook I am opening via code is a different workbook that never gets opened at all – Tommy Dec 11 '18 at 20:33
  • Please let us know *how you call your function*. – Excelosaurus Dec 11 '18 at 20:37
  • 2
    If you're calling this function from a worksheet cell it will not work: there are restrictions on what a function called from a cell can do. Opening other workbooks would be something not "allowed" in this context. See: https://support.microsoft.com/en-nz/help/170787/description-of-limitations-of-custom-functions-in-excel – Tim Williams Dec 11 '18 at 22:09
  • Tim - I did not know this. That seems very likely to be the problem. Unfortunately, I really need to be able to insert this function into the worksheet's cells since the arguments are not always going to be in the same place and the user needs to be able to easily specify them without having to write more code. Is there any kind of workaround that would allow me to open this workbook another way? Like maybe using the function to call a sub that opens the workbook? – Tommy Dec 11 '18 at 23:23
  • Tim - actually I just stumbled across your own posted question from years ago outlining your approach to the workaround. Thanks for the help, I'll try your approach and see what happens! Link to your question: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet – Tommy Dec 11 '18 at 23:32
  • That might work. In gneral it might be worth trying to cache the data in an array after the first call, so that every recalculation doesn't need to open/close the other workbook – Tim Williams Dec 12 '18 at 16:21

0 Answers0