Excel

Updating Your Project From A Web-Site

Ease of Use

Hard

Version tested with

2000 

Submitted by:

johnske

Description:

A user clicks a "Check For Updates" type button and the code modules for the latest version of your project are imported and the older code modules are removed. 

Discussion:

You may have distributed your project to a number of (perhaps unknown) users. You may later fix some minor bugs or make improvements or add additional features to your project. This procedure allows all these users to download the latest code without changing the data they may already have in their copy of the earlier version... 

Code:

instructions for use

			

'<< You will need to upload a workbook to your own site, the demonstration in the >> '<< attached zip has been placed on my site for you to try the code to see how it >> '<< works... '<< THIS CODE GOES INTO THE WORKBOOK THAT HAS TO BE UPDATED >> Option Explicit Sub BeamMeUpScotty() Dim Answer As VbMsgBoxResult, N%, MyFile$ Answer = MsgBox("1) You need to be on-line to update" & vbLf & _ "2) The update may take a few minutes" & vbLf & _ "3) Please do not interrupt the process once started" & vbLf & _ "" & vbLf & _ "SEARCH FOR UPDATE?", vbYesNo, "Update?") If Answer = vbNo Then Exit Sub 'otherwise - carry on Application.ScreenUpdating = False Application.EnableCancelKey = xlDisabled On Error GoTo ErrorProcedure 'you can put your own path here Application.Workbooks.Open ("http://www.geocities.com/johnske100" & _ "/HookToMySite_MASTER.xls") 'The book on the site opens and you can do whatever you 'want now (note that the remote book is "Read Only") - in 'this particular case a workbook_Open event now triggers 'a procedure to export the new module to the PC 'close the remote book Workbooks("HookToMySite_MASTER.xls").Close savechanges:=False 'now check if an updated module is available '(it should be on your PC in "MyFile" now) MyFile = Dir(ActiveWorkbook.Path & "\Version101.bas") If MyFile = Empty Then MsgBox "No new file found" '< (download failed) Else 'download was successful With ActiveWorkbook.VBProject 'already using latest version For N = 1 To .VBComponents.Count If .VBComponents(N).Name = "Version101" Then MsgBox "Sorry, there are no later versions available", _ , "Already Using Current Version..." Exit Sub 'there is an updated module - remove the old module ElseIf .VBComponents(N).Name = "Version100" Then .VBComponents.Remove .VBComponents(N) MsgBox "Old file removed" Exit For End If Next N End With 'import the new module Application.VBE.ActiveVBProject.VBComponents _ .Import (ActiveWorkbook.Path & "\Version101.bas") MsgBox "Version upgrade complete..." End If 'put the new versions name on the sheet Run ("NewModuleV101") 'save the changes to the slave ActiveWorkbook.Save Exit Sub ErrorProcedure: MsgBox Err.Description End Sub '<< THIS CODE GOES INTO THE "REMOTE" BOOK (ON THE SITE) >> '<< IT'S TRIGGERED BY THE WORKBOOK_OPEN EVENT BELOW >> Option Explicit Sub ExportModule() Dim MyFile$, N% Workbooks("HookToMySite_slave.xls").Activate MyFile = Dir(ActiveWorkbook.Path & "\Version101.bas") If MyFile = Empty Then 'ThisWorkbook is MASTER With ThisWorkbook.VBProject For N = 1 To .VBComponents.Count If .VBComponents(N).Name = "Version101" Then 'Export the module from ThisWorkbook (MASTER) Application.VBE.ActiveVBProject.VBComponents(N) _ .Export (ActiveWorkbook.Path & "\Version101.bas") Exit For End If Next N End With Else MsgBox "No later version is available", , "Already updated..." End If End Sub '<< THIS GOES INTO THE REMOTE BOOKS "THISWORKBOOK" MODULE >> Option Explicit Private Sub Workbook_Open() Run ("ExportModule") End Sub

How to use:

  1. This code is NOT intended for complete "VBA beginners" so download the zip and try it
  2. I trust that if you already have a distributed project you'll know how to modify to suit it
 

Test the code:

  1. Open the zip file and extract the contents to wherever you want
  2. Open the folder "downloading"
  3. Open the workbook "HookToMySite_slave"
  4. Click the "Search for updated version" button (you need to be on-line)
 

Sample File:

downloading.zip 33.09KB 

Approved by mdmackillop


This entry has been viewed 341 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express