Add reference file using code

[Hillsy7]

Hi All,

I hope this is reasonably simple - but googling it shows a level of vba code I'm not capable of reading unfortunately - so I need a simpler explanation

I have a fully functioning bit of code that queries a SQL database. It works completely fine. However, I need to pass it on to our finance department who will be using it, and they aren't particularly technical. The code works fine ONLY if the "microsoft activex data objects 2.8 library" and "microsoft activex data objects recordset 2.8 library" references are checked in the tools option. I have zero faith in them wanting to do this every time they add this code to a workbook, so I want to have the sub include it as part of it's process.

Is this possible? and secondly, how do you do it?

It seems to involve the .references.addfromfile command, but I'm really struggling to parse from the code examples I've been able to find exactly how this works so I can substitute in the correct values.

Any help is gratefully received.

Thanks

 

[Mentor82]

Hi,
What do you mean by ”I want to have the Sub include as a part of it’s process?”. A part of what process? A process of excel or process of Accounting?
Why do they need to copy it over and over to a workbook? Do they use the code on different workbooks or specific ones? I’m asking because two things comes to my mind:
1. If those are specific workbooks, add the code and a button, perhaps on a ribbon, to run the code
2. If those are nim-specific workbooks which can be any even newly created one, I’d recommend to create an xlam addin with created XML new tab and butto to run the macro, save it in addins location and turn it on each user excel application. Then, as you open the new workbook or any existing one the newly created tab with the macro button of the addin will appear and that can use it any time they want.
One more thing: there are two ways of referencing to the library:
1. It’s called early binding which requires to set referrence on to the library manually as you described above
2. It’s called late binding which does not require at all to turn on the reference manually but within the code the declaration syntax differs to the early binding and vba intellisence will not work for such declared objects

 

[Hillsy7]

Hi,
What do you mean by ”I want to have the Sub include as a part of it’s process?”. A part of what process? A process of excel or process of Accounting?
Why do they need to copy it over and over to a workbook? Do they use the code on different workbooks or specific ones? I’m asking because two things comes to my mind:
1. If those are specific workbooks, add the code and a button, perhaps on a ribbon, to run the code
2. If those are nim-specific workbooks which can be any even newly created one, I’d recommend to create an xlam addin with created XML new tab and butto to run the macro, save it in addins location and turn it on each user excel application. Then, as you open the new workbook or any existing one the newly created tab with the macro button of the addin will appear and that can use it any time they want.
One more thing: there are two ways of referencing to the library:
1. It’s called early binding which requires to set referrence on to the library manually as you described above
2. It’s called late binding which does not require at all to turn on the reference manually but within the code the declaration syntax differs to the early binding and vba intellisence will not work for such declared objects

Hey, thanks for the reply

Question 1: As in, I want the macro to turn on references in the library as part of the macro so they don't have to activate them every time in order to embed the macro in any one workbook.
Question 2: They use loads of workbooks for various things and the Macro is an add on for that. I built it into a UDF but they sometimes need to populate hundreds of lines and it runs too slow.

So it sounds like I need to do it via late-binding somehow (I have no idea what an XML addin is) - is this possible?

 

[Mentor82]

Hi,
Ad.1 One you do not need to write the macro to turn on the reference. For this reason I’d suggest to change declaration in your code to the libraries as late binding because currently you have an early binding which requires to have libraries set on. Otherwise it’s not required to set them on. Provide me with your vba code and I’ll adjust it for you.

Ad. 2 excel workbooks since 2007 version has had a XML strukture of the workbook/worksheet contents. In order to have a dynamically generated new owncreated tab with button init you need to create such tab in XML,put it in the workbook content. Then save a workbook as addin, płace in user’s add in location, turn on the addin in developers’ tab and than each time user opens any workbook that will have the new tab with a button in it assigned to the macro. I can help you creating such addin that but again I’d need the code of the macro to change it to late binding and assign to the button on addins new tab.

What do you think?