Auto load macros

  • Thread starter Thread starter ksgoodwin
  • Start date Start date
K

ksgoodwin

I would like to know the suggested method(s) to load updates to a set of
macros contained in a .xls file. Using Excel 2000. Reason: Instead of
sending out the updated .xls files I would like the .xls files to look for,
check to see if there is a more recent update and then, if there is, load it
from a designated server. Maybe even change the server location where
updates are to come from next time.

I have seen .xls programs that have modules that check and load .xla files
with the updates. I have also seen code that will load individual modules.
Other methods? Recommended approach to follow?

Ken
 
Ken,

I'm developing something similar at present, so this may help. The process
I'm using is as follows:

Before using any of the routines in my system, the user has to invoke a
loader addin. This addin

1. Checks a list of files on a server. In my case I'm doing this as a
disconnected ADO query of a database table (because I already have this
functionality built and because it's all based on a distributed system) but
there are simpler ways, including just doing a directory scan if you know
where the files are located and you have directory access.
2. Check the last update date of these files, stored in the db table,
against saved values in the registry to see if a download is needed. You
could also do this as a directory scan of the local machine.
3. If a download is needed, use an API call, URLDownloadToFile, to copy the
target file to the local machine from the server. If you have directory
access, you could just copy it directly within your network.
3b. Invoke regsvr if needed to register any ActiveX components needed by the
new addins. (Administrators might not like this unless they control the
server)
4. Load the target file - typically an xla addin if needed by the routine
being run.

The most difficult part given that I'm not good with Windows system calls
was finding the API call.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal strURL As String, _
ByVal strFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Used like this..

lReturn = URLDownloadToFile(0, strFullURL, strLocation, 0, 0)
If lReturn = 0 Then 'it got the file

Hope that helps a bit.

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in
 
Back
Top