VBA: Properly Referencing External Workbooks

zero269

Active Member
Joined
Jan 16, 2023
Messages
335
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
'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.

VBA Code:
'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:

VBA Code:
'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:

VBA 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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi zero269. Maybe something like this will work. HTH. Dave
VBA Code:
'**********change file path to suit
Dim WbTracker As Workbook
Set WbTracker = Workbooks.Open("C:\documents\Book Tracker.xlsm")
With WbTracker.Sheets("Books").ListObjects("t_Books")
lCol = .ListColumns("Book Status").Index
lRow = Application.WorksheetFunction.Match(Quiz, .Range("t_Books[Quiz]"), 0)
End With
WbTracker.Close savechanges:=False
 
Upvote 1
Solution

Hi @NdNoviceHlp

Thanks alot for your help. This one was really giving me a hard time...

I used the long version for testing, but I'm definitely gonna need to use the Workbook declaration/datatype considering I'm using it in four places.
Rich (BB code):
' Get Matching Row Index
lRow = Application.WorksheetFunction.Match(Quiz, _
    Workbooks("Book Tracker.xlsm").Worksheets("Books").Range("t_Books[Quiz]"), 0)

' Declare Books table
Set myRange = Workbooks("Book Tracker.xlsm").Worksheets("Books").ListObjects("t_Books").DataBodyRange(lRow, lCol)
It did the trick and pulled the Cell Note value from thier matched Quiz Numbers.

This is why I shouldn't be trying to do things I'm not good at... after midnight. :)

Thank you and best regards,
 
Upvote 0

Forum statistics

Threads
1,226,462
Messages
6,191,177
Members
453,644
Latest member
karlpravin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top