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

eurobonds

New Member
Joined
Mar 22, 2016
Messages
35
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!


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

Attachments

  • 2024-02-26_16-32-50.png
    2024-02-26_16-32-50.png
    74.5 KB · Views: 23

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
You can refer to it using the workbooks collection:
VBA Code:
Workbooks("MyAddin.xlam") '<< change addin name + extension to suit
 
Upvote 0
Solution

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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