Is there a way to have VBA import a XLSB?

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. 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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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
 
Upvote 0
What exactly are you trying to share?
Data or code?
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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