Passing a workbook as an object in VBA ByRef - having a problem

[James Snyder]

I am working with multiple workbooks from a master workbook and need to pass a workbook object and worksheet object as ByRef parameters. I am successful in other functions, but am not figuring out how to do it into a function that is generic.

I am using Excel 2010, workbooks are made in 2003 and saved to 2010 before being referenced, and the passed references get used in multiple functions.

The declarations are in Main():

    Dim obstWkBook As Workbook
    Dim obstWkSheet As Worksheet
    Dim [COLOR=#008000]workOWkBook[/COLOR] As Workbook
    Dim [COLOR=#008000]workOWkSheet[/COLOR] As Worksheet
    Dim ftpWkBook As Workbook
    Dim ftpWkSheet As Worksheet
    Dim arrDupes() As String
    Static sendDate As String
    Dim funcReturn As String
    Dim failReturn As String
    Dim errString As String

The function call that isn't working:

    funcReturn = PSIDDupeCheck([COLOR=#008000]workOWkBook[/COLOR], [COLOR=#008000]workOWkSheet[/COLOR], arrDupes, sendDate)
    If funcReturn <> "Success" Then
        errString = "DupeCheck fail:   Error while checking for PSID duplicates"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    End If

The function:

Private Function PSIDDupeCheck([COLOR=#008000]ByRef wkBook As Workbook[/COLOR], _
    [COLOR=#008000]ByRef wkSheet As Worksheet[/COLOR], _
    ByRef dupeArray() As String, _
    ByRef sendDate As String) As String
    
    Dim rowMax As Long
    Dim dupeRow As Long
    Dim i As Long
    Dim failReturn As String
    Dim errString As String
    
    [COLOR=#008000]wkBook[/COLOR].Open      [COLOR=#ff0000]<=== Fails here with Run-time error '424' Object Required[/COLOR]
    Workbooks.Open [COLOR=#008000]wkBook[/COLOR] [COLOR=#ff0000]<=== or fails here with Run-time error '424' Object Required[/COLOR]
    If Not [COLOR=#008000]wkBook[/COLOR] Is Nothing Then
'<<snip>>
    Else
        errString = "WorkOrders:       Failed to open workbook get dupes"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        PSIDDupeCheck = errString
    End If
    wkSheet = Nothing
    wkBook = Nothing
End Function

</snip>

 

[Jonmo1]

We need to see the code "leading up to" this line
funcReturn = PSIDDupeCheck(workOWkBook, workOWkSheet, arrDupes, sendDate)

How is the variable workOWkBook and workOWkSheet assigned an Object?

 

[ChrisM]

How are you setting the value of workOWkBook?

I'm not quite sure why you are trying to run "WkBook.Open" in your function when you already had to "open" it to set it equal to workOWkBook.

Also, WkBook.Open is not a valid function.

Workbooks.Open xxx is okay, but it requires a file path as an attribute, not a workbook object.

I think you'd be all set changing your function to accept two file paths instead of two workbook objects, then use the Workbooks.Open command with those. Maybe like:

Set wkBook = Workbooks.Open "My file path"

 

[James Snyder]

Here is the code immediately before the call (including the call):

    ' Get the WorkOrder file path
    fileName = "WorkOrders.xlsx"
    Set cellRef = Range("$I$3")
    
    workOInFile = RetrieveFilePath(cellRef, fileName, sendDate)
    If workOInFile = "" Then
        errString = "WorkOrder.xlsx:   Unable to find file path."
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    Else
        ' Open the Obstructed .xlsx and correct headers
        funcReturn = CorrectWorkOHeaders([COLOR=#008000]workOInFile[/COLOR], [COLOR=#008000]workOWkBook[/COLOR], [COLOR=#008000]workOWkSheet[/COLOR], sendDate)
        If funcReturn <> "Success" Then
            GoTo ExitPoint
        End If
    End If
    
    ' Get the FTP file path
    fileName = "FTP.xlsx"
    Set cellRef = Range("$I$4")
    
    ftpInFile = RetrieveFilePath(cellRef, fileName, sendDate)
    If ftpInFile = "" Then
        errString = "FTP.xlsx:         Unable to find file path."
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    Else
        ' Correct the leading space in " Assigned"
        funcReturn = CorrectFTPHeaders(ftpInFile, ftpWkBook, ftpWkSheet, sendDate)
        If funcReturn <> "Success" Then
            GoTo ExitPoint
        End If
    End If
    
    ' Check for PSID Duplicates
    funcReturn = PSIDDupeCheck([COLOR=#008000]workOWkBook[/COLOR], [COLOR=#008000]workOWkSheet[/COLOR], arrDupes, sendDate)
    If funcReturn <> "Success" Then
        errString = "DupeCheck fail:   Error while checking for PSID duplicates"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    End If

I have tried setting it where I am assigning it with Set wkBook = Workbooks(wkBook), and tried a few other things, but no good combo yet. In the CorrectWorkOHeaders() function, I handle it like this:

Private Function CorrectWorkOHeaders(ByRef workOInFile As String, _
    ByRef workOWkBook As Workbook, _
    ByRef workOWkSheet As Worksheet, _
    ByRef sendDate As String) As String
    
    Dim errString As String
    Dim failReturn As String
    
    Workbooks.Open fileName:=workOInFile
    Set workOWkBook = ActiveWorkbook
    If Not workOWkBook Is Nothing Then
        Set workOWkSheet = workOWkBook.Sheets(1)
        If Not workOWkSheet Is Nothing Then
            workOWkSheet.Range("B1") = "PSID"
            workOWkSheet.Range("C1") = "Item No"
            Application.EnableEvents = False        ' Suppress BeforeSave event
            workOWkBook.Close savechanges:=True
            Application.EnableEvents = True
            CorrectWorkOHeaders = "Success"
        Else
            errString = "WorkOrders:       Failed to open worksheet to fix headers"
            failReturn = ProblemReport(errString, sendDate)
            Err.Clear
            CorrectWorkOHeaders = errString
        End If
    Else
        errString = "WorkOrders:       Failed to open workbook to fix headers"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        CorrectWorkOHeaders = errString
    End If
End Function

It is going to a generic function that I am having problems. I could add the parameter for the file path again.

 

[James Snyder]

Sorry Chris! Answered before seeing your post...

 

[RoryA]

You either have to pass an open workbook, or you need to pass a path so that the function can open the correct workbook. I'm not really clear why you would need to do the latter and if you do the former you don't, as ChrisM already stated, need to try and open the workbook again.

 

[ChrisM]

You're still not showing where you assign the values to

workOWkBook and workOWkSheet

 

[James Snyder]

Thanks Rory! In a previous function, I make changes and close it:

Private Function CorrectWorkOHeaders(ByRef workOInFile As String, _
    ByRef workOWkBook As Workbook, _
    ByRef workOWkSheet As Worksheet, _
    ByRef sendDate As String) As String
    
    Dim errString As String
    Dim failReturn As String
    
    Workbooks.Open fileName:=workOInFile
    Set workOWkBook = ActiveWorkbook
    If Not workOWkBook Is Nothing Then
        Set workOWkSheet = workOWkBook.Sheets(1)
        If Not workOWkSheet Is Nothing Then
            workOWkSheet.Range("B1") = "PSID"
            workOWkSheet.Range("C1") = "Item No"
            Application.EnableEvents = False        ' Suppress BeforeSave event
  [B]          workOWkBook.Close savechanges:=True[/B]
            Application.EnableEvents = True
            CorrectWorkOHeaders = "Success"
...

@ChrisM
I worked on it a half day on Friday and sent it in the state I left it. In the function I use before this call, it gets assigned, opened, then saved and closed. I need to play with what Rory gave me. He is correct in that it is already assigned, and I need only to reopen it. I will play with it a bit more and return here for an update as to what either finally works or what I can't get through my thick head!

 

[James Snyder]

SOLVED: Instead of closing and using the .SaveAs, I leave it open and use the .Save:

Before:

workOWkBook.Close savechanges:=True

After:

workOWkBook.Save

It does mean I will have to explicitly close them before using ADO against them. Thanks Rory and Chris!