Updating a Module Automatically
I am using Excel 2003 and have a Module which is regularly added to and ammended on my local machine for distribution to five other machines. The ammended module is exported to a folder ( G:\LatestVBA ) on my server.
I then go around and delete the existing module and import the new one five times over to update evryone.
Can the experts suggest code to reside on the users excel application that will achieve this routine every time each persons excel application is opened. The Module is kept in personal.xls (in the usual xlstart up folder) and the project is password protected (lets say ZZZ) to prevent people messing with the code.
Thanks for your help.
PeterWhitts
I then go around and delete the existing module and import the new one five times over to update evryone.
Can the experts suggest code to reside on the users excel application that will achieve this routine every time each persons excel application is opened. The Module is kept in personal.xls (in the usual xlstart up folder) and the project is password protected (lets say ZZZ) to prevent people messing with the code.
Thanks for your help.
PeterWhitts
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi,
Please refer to an existing (presently, open) question in the "Programming" Topic Area:
"Patch/add modules in Excel application"
[ https://www.experts-exchange.com/questions/21608902/Patch-add-modules-in-Excel-application.html ]
Specifically, the posted comment by Matt:
===
I want to start off by saying that creating COM addins or DLLs to distribute would probably be your best bet. Unfortunately, I do not have any direct experience doing this so I cannot help with that, but I am sending out an email to someone that should be able to walk you through it.
As another method, do you and your users have access to an intranet, or even the internet? If so, you could use the script control to get the code from the external source every time at runtime (would probably slow your processes down though). For example, I've placed a generic subroutine at http://www.hastalavidas.com/mvidascode.txt
You could run the following code from VBA to run the code at that site:
Sub RunCode()
Dim ScriptCont As Object, CodeAsString As String
Set ScriptCont = CreateObject("msscriptcont
ScriptCont.Language = "vbscript"
'get website text into a string variable
CodeAsString = GetWebIE("http://www.hastalavidas.com/mvidascode.txt")
'add code to the scriptcontrol
ScriptCont.AddCode CodeAsString
'run the macro
ScriptCont.Run "msgboxhi"
Set ScriptCont = Nothing
End Sub
Function GetWebIE(ByVal vWebSite As String) As String
Dim IE As InternetExplorer
Set IE = CreateObject("internetexpl
IE.Navigate2 vWebSite
Do While IE.readyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop
GetWebIE = IE.Document.Body.innertext
Set IE = Nothing
End Function
As I said, it would make the whole thing a little slower, but would only require you to update your code in one location (unprotected though, unless you make it a secure site). It has it's advantages, and also disadvantages. If all of your users had access to a shared network drive that you could keep the code on, that would make it a little quicker as it wouldn't have to access the code from the internet via IE. Storing it on the internet would just ensure that more people have access to the code.
Also as I said, I'll send out notification to someone who I believe can help you with the COM/DLL route if you were interested.
===
And my follow-up..
===
I have used the method Matt describes in the past to good effect. I didn't rely upon the InternetExplorer.Applicati
===
BFN,
fp.
Please refer to an existing (presently, open) question in the "Programming" Topic Area:
"Patch/add modules in Excel application"
[ https://www.experts-exchange.com/questions/21608902/Patch-add-modules-in-Excel-application.html ]
Specifically, the posted comment by Matt:
===
I want to start off by saying that creating COM addins or DLLs to distribute would probably be your best bet. Unfortunately, I do not have any direct experience doing this so I cannot help with that, but I am sending out an email to someone that should be able to walk you through it.
As another method, do you and your users have access to an intranet, or even the internet? If so, you could use the script control to get the code from the external source every time at runtime (would probably slow your processes down though). For example, I've placed a generic subroutine at http://www.hastalavidas.com/mvidascode.txt
You could run the following code from VBA to run the code at that site:
Sub RunCode()
Dim ScriptCont As Object, CodeAsString As String
Set ScriptCont = CreateObject("msscriptcont
ScriptCont.Language = "vbscript"
'get website text into a string variable
CodeAsString = GetWebIE("http://www.hastalavidas.com/mvidascode.txt")
'add code to the scriptcontrol
ScriptCont.AddCode CodeAsString
'run the macro
ScriptCont.Run "msgboxhi"
Set ScriptCont = Nothing
End Sub
Function GetWebIE(ByVal vWebSite As String) As String
Dim IE As InternetExplorer
Set IE = CreateObject("internetexpl
IE.Navigate2 vWebSite
Do While IE.readyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop
GetWebIE = IE.Document.Body.innertext
Set IE = Nothing
End Function
As I said, it would make the whole thing a little slower, but would only require you to update your code in one location (unprotected though, unless you make it a secure site). It has it's advantages, and also disadvantages. If all of your users had access to a shared network drive that you could keep the code on, that would make it a little quicker as it wouldn't have to access the code from the internet via IE. Storing it on the internet would just ensure that more people have access to the code.
Also as I said, I'll send out notification to someone who I believe can help you with the COM/DLL route if you were interested.
===
And my follow-up..
===
I have used the method Matt describes in the past to good effect. I didn't rely upon the InternetExplorer.Applicati
===
BFN,
fp.
Beat me to it, fp :)
FWIW, I don't usually use the IE object either (nor fso), but my usual method was gave me trouble yesterday (MSXML2.XMLHTTP, although the trouble was from my using my asp version of MSXML2.ServerXMLHTTP -- figured it out too late). I've had issues using fso and http addresses so I generally stay away. I'd love to see what you would use to load an internet file with fso though!
Matt
FWIW, I don't usually use the IE object either (nor fso), but my usual method was gave me trouble yesterday (MSXML2.XMLHTTP, although the trouble was from my using my asp version of MSXML2.ServerXMLHTTP -- figured it out too late). I've had issues using fso and http addresses so I generally stay away. I'd love to see what you would use to load an internet file with fso though!
Matt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am going to think about this. have followed the link above and noted fp comments.
"I have seen a method whereby everytime a change is made to a workbook, a macro is recorded to capture all the amendments. Each subsequent revision carries a version number & the macro recorded during that update is renamed "Update_To_Version_XX"."
Thanks for your help.
"I have seen a method whereby everytime a change is made to a workbook, a macro is recorded to capture all the amendments. Each subsequent revision carries a version number & the macro recorded during that update is renamed "Update_To_Version_XX"."
Thanks for your help.
You're very welcome.
Thanks for your grading.
BFN,
fp.
Thanks for your grading.
BFN,
fp.
What do you mean: persons excel application is opened
do you thing a new file created from personal.xls
You can put code in open event, you can prepare personal.xlt template instead of
personal.xls.
Private Sub Workbook_Open()
End Sub