How to refer to worksheet within a custom Excel add-in?

[eurobonds]

Hello,
I created an add-in to hold my macros. One macro currently performs a lookup on a table of cost center IDs and names that resides as the first sheet in my PERSONAL.XLSB workbook (see screenshot). I have distributed this add-in to a co-worker and I had to show them how to paste that same table into their personal workbook. I would like to have this lookup table reside within the add-in (.xlam) file itself so I can skip messing with personal workbooks. I haven't found any documentation of this online.

Or could I put the lookup table within the VBA itself, perhaps in a switch case?

Here is a snip of where I do the INDEX MATCH against Personal.xlsb.
Thanks!

     'Save to CC folders
        Dim destExistingCCFolder As String
        Dim destNewCCFolder As String
        Dim rawReportCC As String
        Dim destFileName As String
        Dim indirectMonthlyFolderWithYear As String
        rawReportCC = Mid(sourceFileName, 33, 4)
        destFileName = "Ind YE Est CC " & rawReportCC & " " & yearFS & "." & monthFS & ".xlsx"
        'destFileName = Left(sourceFileName, 36) & " " & yearFS & "." & monthFS & ".xlsx"
        indirectMonthlyFolderWithYear = "Indirect Monthly Reports " & yearFS
        
        On Error Resume Next
        'CC not in lookup
        If Application.IsError(Application.Index(Workbooks("Personal.xlsb").Sheets(1).Range("B:B"), Application.Match(rawReportCC, Workbooks("Personal.xlsb").Sheets(1).Range("A:A"), 0))) Then
            destNewCCFolder = "##NEW CC - UPDATE NAME## (CC_" & rawReportCC & ")"
            'CC not in lookup, temporary CC folder already exists (ie, re-ran macro before adding new cc to lookup)
            If checkFolderExists(destTopFolderPathFS & "\" & destNewCCFolder) = True Then
                'CC not in lookup, indirect monthly folder exists
                If checkFolderExists(destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear) = True Then
                    'delete old file if exists
                    If checkFileExists(destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear & "\" & destFileName) = True Then
                        Kill destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear & "\" & destFileName
                    End If
                    wb.SaveCopyAs Filename:=destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear & "\" & destFileName ', FileFormat:=xlWorkbookDefault
                    wb.Close SaveChanges:=False
                Else
                    'CC not in lookup, indirect monthly folder doesn't exist, create it
                    objFso.CreateFolder destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear & "\"
                    wb.SaveCopyAs Filename:=destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear & "\" & destFileName ', FileFormat:=xlWorkbookDefault
                    wb.Close SaveChanges:=False
                End If
            Else
                'CC not in lookup, cc folder doesn't exist, need to create it and indirect monthly folder
                objFso.CreateFolder destTopFolderPathFS & "\" & destNewCCFolder & "\"
                objFso.CreateFolder destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear & "\"
                wb.SaveCopyAs Filename:=destTopFolderPathFS & "\" & destNewCCFolder & "\" & indirectMonthlyFolderWithYear & "\" & destFileName ', FileFormat:=xlWorkbookDefault
                wb.Close SaveChanges:=False
            End If
        Else

 

[Akuini]

One macro currently performs a lookup on a table of cost center IDs and names that resides as the first sheet in my PERSONAL.XLSB workbook

So, why don't you just put the table on the first sheet of the add-in file?
You can refer to it with something like this:
ThisWorkbook.Sheets("Sheet1").Range("A1")

I have distributed this add-in to a co-worker and I had to show them how to paste that same table into their personal workbook.

Do you mean you can manually copy-paste a table (or any data) to a sheet in the Personal workbook? I don't know how to do that, can you share how you do it?

 

[eurobonds]

So, why don't you just put the table on the first sheet of the add-in file?

Because I don't know how to refer to the add-in when it is loaded as an add-in in Excel. ThisWorkbook would return the workbook that is open, not my add-in.

Do you mean you can manually copy-paste a table (or any data) to a sheet in the Personal workbook? I don't know how to do that, can you share how you do it?

View > Window > Unhide PERSONAL.XLSB

 

[Akuini]

ThisWorkbook would return the workbook that is open, not my add-in.

Are you sure? ThisWorkbook should refer to the workbook where the code resides & ActiveWorkbook refer to the open workbook.
Something like this:
ThisWorkbook.Sheets("Sheet1").ListObjects(1)

 

[Jaafar Tribak]

You can refer to it using the workbooks collection:

Workbooks("MyAddin.xlam") '<< change addin name + extension to suit

 

[eurobonds]

You can refer to it using the workbooks collection:

Workbooks("MyAddin.xlam") '<< change addin name + extension to suit

I could have SWORN I tried that at some point. But it works now! Cheers

 

[MikeDBMan]

I entered
Workbooks("MHirsch.xlam").Sheets("Sheet1").ListObjects (1)
And I get an error "Run-time error '438':
Object Doesn't support this property or method

I am just wanting to make the sheet1 visible so I can enter data in it. But I can't seem to get anywhere.

Under the VBA Project for this file, I see 2 "Microsoft Excel Objects":
Sheewt1 (Sheet1)
ThisWorkBook

In properties it shows "-1 - xSheetVisible"

How can I see the sheet and add data to it?