Is there a way to have VBA import a XLSB?

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
712
Office Version
2016
Platform
Windows
I have a personal macro workbook and i don't want to translate everything on it/change current codes to another XLSM.
Is there a way to import my personal macro workbook XLSB automatically so that another user's XLSB is replaced by mine when opening a specific file?
as well as establish a reference to my XLSB?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,786
Office Version
365
Platform
Windows
This is very confusing. Not sure what you are trying to do.

Note that an "XLSB" is a Binary Excel Workbook File. Not every Binary Excel Workbook File is a Personal Macro Workbook.
Also, not every user has a Personal Macro Workbook.

If you have an Excel Workbook that already has VBA/Macros attached, why would you need to overwrite/replace another users Personal Macros?
You can simply use the VBA/Macros that are in the Workbook that they are opening.

Actually, the potential for doing so could raise a lot of security red flags, as that would be one way to force malicious code down to user's computers.
So for security sake, I sincerely hope that there isn't an easy way to do that.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
712
Office Version
2016
Platform
Windows
Actually, the potential for doing so could raise a lot of security red flags, as that would be one way to force malicious code down to user's computers.
So for security sake, I sincerely hope that there isn't an easy way to do that.
i feel like VBA in general could accomplish that without the use of XLSB/personal macro workbook.

Regardless
What i mean is i have a reference made to my personal macro workbook located in the C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART folder
So that when my program runs Application.Run "PERSONAL.XLSB!dictionaryQTY" i am able to lookup the quanatity of a product via part number from any sheet/module/workbook
Is there a way for VBA to swap from another user's personal XLSB file to the one containing dictionaryQTY? I.E if i create a zip file with the program and share it with a colleague its going to try and run "PERSONAL.XLSB!dictionaryQTY" from that user's XLSTART folder instead of the one in the folder i sent them.

or is my only option to change the code Application.Run "PERSONAL.XLSB!dictionaryQTY"
to open a workbook and hide it until they exit the main workbook?
to the extent of

VBA Code:
filePATH = Application.ActiveWorkbook.Path & "\Dictionaries.xlsm"
Set openWB = Application.Workbooks.Open(filePATH)
openWB.Windows(1).Visible = False
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,786
Office Version
365
Platform
Windows
What exactly are you trying to share?
Data or code?
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
712
Office Version
2016
Platform
Windows
share as in hand off to end user/colleague?
a multi purpose "portable" program
end user unzips folder to wherever (desktop, thumb drive, etc)
unzips rar/zip file >folder tree of program is "installed" to destination
similar to how any modern program installation works, but not tied to program file folders

!!!example1.jpg

!!!example2.jpg

so the current trial shot back an error because i sent it to colleague and when he opened the "ThisTheProgram.xlsm" it tried opening the personal workbook macro/xlsb in MY xlstart folder which is obviously missing from this folder tree. So i'm wondering if there is a way to add my XLSB like below so that whenever ThisTheProgram.xlsm is opened it will replace or add the XLSB file as that users personal macro workbook? or should i just create a new XLSM like Personal.xlsm and hard code opening the workbook and hiding it upon opening ThisTheProgram.xlsm and then closing it when exiting ThisTheProgram.xlsm? hopefully this paints a better picture

!!!example3.jpg
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,786
Office Version
365
Platform
Windows
I really don't follow what you are trying to do or why.
If you are looking to share a bunch of code with users outside of a workbook, in some sort of distributable form, you should look at creating an Add-In and sharing that with your users.
Just Google "Creating an Excel Add-In".
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
712
Office Version
2016
Platform
Windows
The appeal of using the personal XLSB in this way is that the user cant close it, and it remains open and retaining its public variable data with other workbooks/worksheets as long as the instance of excel is still running. This way if someone closes ThisTheProgram.xlsm it still contains the data for price, color, images, etc that i have loaded into public dictionaries using the personal XLSB. So if another workbook needs to call on a dictionary for pricing it isn't "erased" until the user is completely out of excel or in a new instance of excel.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
712
Office Version
2016
Platform
Windows
so say for example a user wants to look up the price of a new tire: he opens the program, beep boop it loads all the info into public dictionaries, he does his price comparisons on another worksheet, he updates the changed pricing via the program, closes the program because the functionality of it is no longer needed BUT later someone comes in and wants a price on another tire . Instead of loading up the program again and wasting time he already has the value in a public dictionary being carried in his personal XLSB. that makes sense to me.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
712
Office Version
2016
Platform
Windows
but if you're saying the danger for such info is too high to post or there isnt a way then thats understandable and i will just do my darndest to hard code an invisible XLSM workbook that does the same thing.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
712
Office Version
2016
Platform
Windows
oh wait tl;dr way of explaining what i want:
can i have excel point to another file when selecting the (or lack there of a) personal macro workbook?
 

Forum statistics

Threads
1,085,757
Messages
5,385,706
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top