VBA: Properly Referencing External Workbooks

[zero269]

Hello,

I'm having some trouble figuring out how to modify some existing VBA code to work between different workbooks. The existing code was designed and works within the same workbook. However, I'm having issues with properly converting the 'same workbook' references to references for a 'different workbook'.

Note: wsBooks is the worksheets CodeName.
Is it safe to say that wsBooks = Workbooks("Book Tracker.xlsm").Sheets("Books") ?
lCol and lRow are Long data types.

The following is the working code that works when everything is within the same workbook:

'Same Workbook
lCol = wsBooks.ListObjects("t_Books").ListColumns("Book Status").Index

The following is what I changed it to in order to get the Column Index from the different workbook; although a bit messy.

'Different Workbook
Dim loBooks As ListObject
Set loBooks = Workbooks("Book Tracker.xlsm").Sheets("Books").ListObjects("t_Books")
lCol = loBooks.ListColumns("Book Status").Index

The other part is using Application.WorksheetFunction.Match to find the value and return the Table Row number.

The following is the working code that works when everything is within the same workbook:

'Get Matching Row in Books table
lRow = Application.WorksheetFunction.Match(Quiz, Range("t_Books[Quiz]"), 0)

Currently, this is the one I'm unable to figure out how to properly change the Range portion of the code to point to the external workbook. I've tried other variations as well to no avail.

Workbooks("Book Tracker.xlsm").Sheets("Books").ListObjects("t_Books").Range("t_Books[Quiz]")
Workbooks("Book Tracker.xlsm").Range("t_Books[Quiz]")

Any help would be greatly appreciated…
Thank you, and best regards,


If it helps better understand what I'm working with, here's my original Single Workbook working code:

Sub Log_Get_Cell_Notes()

  'Suspend Settings
  Application.ScreenUpdating = False
  Application.EnableEvents = False

  'Declarations
  Dim cell As Range, myRange As Range
  Dim Quiz As Long, lRow As Long, lCol As Long, iRow As Long
  Dim c As Comment
  
  ' Get Last Row
  iRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("J3:J" & iRow).SpecialCells(xlCellTypeVisible).Select
  
'**************************************************************************************************
  'Same Workbook
'  lCol = wsBooks.ListObjects("t_Books").ListColumns("Book Status").Index

  'Different Workbook
  Dim loBooks As ListObject
  Set loBooks = Workbooks("Book Tracker.xlsm").Sheets("Books").ListObjects("t_Books")
  lCol = loBooks.ListColumns("Book Status").Index

'**************************************************************************************************

  For Each cell In Selection
    
    If IsEmpty(Range("A" & cell.Row)) Then
    
      GoTo NextIteration
    
    Else
    
      If IsNumeric(Range("A" & cell.Row)) = True Then
      
        Quiz = Range("A" & cell.Row)
        
        ' Get Matching Row Number in 'Books' table
        
        'SAME Workbook
'        lRow = Application.WorksheetFunction.Match(Quiz, Range("t_Books[Quiz]"), 0)

        'Different Workbook
        lRow = Application.WorksheetFunction.Match(Quiz, loBooks.Range("t_Books[Quiz]"), 0)
        ' or Workbooks("Book Tracker.xlsm").Range("t_Books[Quiz]"), 0)
        
        'Declare Books table
        
        'Same Workbook
'        Set myRange = wsBooks.ListObjects("t_Books").DataBodyRange(lRow, lCol)

        'Different Workbook
            'unsure if this works as I can't get past the 'lRow' line above
        Set myRange = Workbooks("Book Tracker.xlsm").ListObjects("t_Books").DataBodyRange(lRow, lCol)
        
        On Error Resume Next
          'Set c = myRange.Comment
          'cell = Application.Trim(myRange.NoteText)
        cell.Value = myRange.Comment.Text
      
      Else
        GoTo NextIteration
      
      End If 'IsNumeric
    
    End If  'IsEmpty
    
NextIteration:
    
  Next cell
  
  'Resume Settings
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  
  'Deselect Range
  Range("A1").Select

End Sub