How to use VBA to automatically update macro in another workbook

[kaileong]

Hi,
I am currently writing some VBA script for excel, due to time constraint, I have to release the early version of utility to the users, all together there are more than 6 copies of them distributed out.

Question,
1) For every change of new revision, I have to copy the latest macro to the users workbook manually, it is not a present experience of manual updating. Is there a way to automatically delete the old macro module then add the latest module onto the workbook without overwriting the data on the workbook.

I have tried using the macro recorder to extra the code out..but no code is generated.


2) How to enforce user select macro running option while excel startup. If user do not select macro running, is there a way to detect it.


thanks alot
regards
kaileong

[ardiaz]

The safest way I have found to update a VBA script in excel is to save the macro as a .bas file and copy it to every user with step by step instructions of how to install the new macro. I haven´t found a way to extract code from one macro with another macro, I think it´s not possible at all.

If you want to force a user to use macros you have to use an auto_open procedure and put some code in it, let me give you an example

---------------------
Sub auto_open()
end_demo = DateSerial(2001, 10, 15)
difference = DateDiff("d", Now(), end_demo)
If difference < 0 Then
MsgBox ("Sory! Demo is good until " & end_demo)
ActiveWorkbook.Close savechanges = False
Else
If diferencia < 8 Then
MsgBox ("This Demo Version is Good for: " & diferencia & " more days")
End If
End If
End Sub
_____________

I use this macro to make sure that the prices in my quote are up to date, also if you chose to open the workbook without macros you'll get a dialog box telling you that the macro cannot be disabled and either you open it with macros or don´t openit at all.

Hope it works

Armando Diaz

[kaileong]

thanks for the reply !

where should I place the auto_open() ?
How to initiate the auto_open procedure


I have tried creating a macro with name auto_open() and place it under thisworkbook module. it didn't work,


May be you can guide me to initiate the launch of auto_open procedure.

I believe this is the old way..before Office 97, is there a better way.

thanks

[ardiaz]

I just place it in the first module, usualy Module1. I´m using Excel 97, and works fine for me.

The auto_open procedure starts automatically when you open the workbook. I don´t know if this will work in Excel 2000.

Can you e-mail me you workbook, let me check it out.

Armando

[TheOnly]

Hi kaileong !!!

do 1)
You have 2 choiches

Make a addin, where the whole programm is in it, only
tables which u need.

or

u have 2 modules
e.g. module 1 which hold the code to check
if a new module exist
and module 2 which u replace every time u have a
new release

do 2)
it is not possible, to run a macro if the user have
not allowed this.

-cu TheOnly

ps: feel free to ask

[kaileong]

HI the only,

can give some working example, well, what I want is..the moment user disable the macro, I would not allow user to key in data.

[TheOnly]

Hi kaileong !!!

You can not prevent the user to diable macro,
so take the other way.

Protect all sheets for manipulation and unprotect them
as first statement in your code !!!!
Note: don't forget to protect them during the closing of
the workbook

This should do what you want !

-cu TheOnly

[kaileong]

Hi,
In this case, I was left with one option that is lock the workbook, using the initialize routine for the workbook to open it..another addin to do the tracking is it ? Well, in the past, I remember I have encountered a workbook, the moment you disable the workbook macro, you would not be able to input data into the worksheet.

regards
kaileong