Sleeper: Auto Update VBA Project

[LeonardH]

Hi,

Requirement: Automatically update a xls workbook including all VBA project elements.

Use case: User is given a xls file which contains VBA code that allows for auto update by checking a master file located on the web. If an updated master file is present ALL VBA project elements (i.e. userforms, worksheets, worksheet code, modules, etc) are copied to the local drive replacing the current VBA project - an "application updater" if you will...

I was hoping someone else may have come across the above requirement and use case.

The code posted by johnske (Updating Your Project From A Web-Site) is partially there but it does not copy all project elements like forms etc.

Apologies if this has been covered / answered elsewhere - have searched for hours and found nothing...

Regards,

Len

[Bob Phillips]

How does johnske's code do it, import and export the code modules? If so, that can be extended to forms and classes. The gotcha is workbook and worksheet code, you cannot delete these modules, you would need to delete the code then insert new code.

[LeonardH]

Hi,

Many thanks for the prompt reply - being a Newbie on this forum my post count does not allow me to share the link to the post. From what I understand he creates a .bas file locally by reading the remote file in read only mode...

Ideally the VBA update routine should be able to delete & replace (and add if needed) worksheets, worksheet code, workbook code etc etc.

I'm not entirely sure if this is even possible - I may be looking for something which is a pipedream...

Simply put I'm looking for a VBA solution to do an in-code file replace - if that makes any sense...

Len

[Bob Phillips]

It is possible, as I said, just extend johnske's code to include forms and classes (they will create .cls files), extend it to delete the forms and classes and import the new versions.

Is workbook and worksheet code a requirement?

[LeonardH]

Hi,

Yes to worksheets and workbook / worksheet code - there are for sure going to be cases where changes / additions are made to worksheets, worksheet code and the workbook code in general.

IF it's possible to do all the above then I guess it's worth the effort as it would ultimatley result in VBA code that acts as "auto updater", but at this point I'm not clued up enough on VBA to make that call...

Your thoughts?

Thanks.

Len

[Bob Phillips]

Can you point me at johnske's article, and I will knock you up some code extending beyond that. Bear with me though, it won't be immediate.

[LeonardH]

Hi - post to get post count up...

[LeonardH]

Another one...

[LeonardH]

Hi - apologies for the above - needed to get post count up to post this URL:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=174#instr

No rush - many thanks for your assistance...

[nzeser]

Hello, has there been any progress on coming up with a solution(s) for this task? I am addressing virtually the same task and was hoping that someone may have been able to solve this and would be willing to share what they've learned. Thank you in advance for any information!