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!
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