Check Version & Download Latest

[dry_flood]

Hello Everyone

I have been a long time lurker and have found the boards very useful, I have nearly always been able to find an answer or at least get pointed in the right direction from previous posts and for this I am grateful.

I now have a problem to solve and have been unable to find anything to help me.

The company I work for has 18 different depots and I have created a manifesting spreadsheet for when they are sending pallets into us. They poplulate the spreadsheet and press the send button. It then prints out sequentially numbered pallet labels and E-mails a tracking number via lotus notes to my depot, the E-mail also contains the spreadsheet version number which I input into a hidden cell. This way I can tell if people are using the newest version like they should.

The issue I am having is:

When I need to make a change / bugfix or implement a new idea, I have to E-mail the new spreadsheet to all the depots and then hope that they give the new version to all the people who require it. In meantime I am wasting alot of resource having to reply to E-mails informing people that they need to use the "new" sheet.

What I would like to do is:

On startup the spreadsheet compares the value of a cell against a value in a file on our Intranet. I have full access and can upload any files required.
It doesnt matter if the file is a blank html document containing a version number or even a text file.

If the version number is lower than the one online then it downloads the new file. The new file will always replace the old file online so neither the file name or location will change.
If possible I would prefer for this to run in the background, e.g, IE doesnt open in front of the spreadsheet. Just the download file confirmation box if possible.

Does all that make sense?

If anyone could help me out on this one I would be eternally grateful.

Gaz

[TomSchreiner]

An old post of mine that I cannot really speak much of. Maybe it will give you some ideas...

http://www.mrexcel.com/forum/showthread.php?t=212642&highlight=version

The download links are no longer available. If you think this might be a plausible solution and need some assistance, just reply in this thread.

[dry_flood]

Thats exactly the kind of thing I am looking for maybe just a little more simple though. If I can make excel check to see if the name of a txt file on a server is a higher value than the one in a cell on a worksheet then it downloads a file. The file will always be called manifest.xls and the file location can be "hard coded" into excel as it will always be the same. For example:

Updated spreadsheet location = server//logistics/dry_flood/manifest.xls
txt file location = server//logistics/dry_flood/2.txt
Cell A1's value = 1

Gaz

[joeyc]

not anymore. Thank you for that link to the other forum. Good stuff. Really.

[TomSchreiner]

The code should work as is. Edit your text file by adding two lines. The first with a date or other value. The second line contains the link to the updated file.

If you use a value other than a date, you must account for that here:
ThisWorkbook.Names.Add "Update_DateTime", Now, False

For example, to use a simple version number of 1, you would run this code:
ThisWorkbook.Names.Add "Update_DateTime", 1, False

All of your distributed workbooks will have this name embedded which contains the value of 1. These workbooks will check the text file you created which will look like this initially:

1
server//logistics/dry_flood/manifest.xls

When you have created an update, update the workbook name as such:
ThisWorkbook.Names.Add "Update_DateTime", 2, False

Now change the textfile:
2
server//logistics/dry_flood/manifest.xls

All distributed copies will note that 2 > 1 and will prompt the user to update.


Option Explicit
Option Private Module

'This code, within your client's workbooks, will automatically check for an updated version.
'If an update exists, they will be prompted. If they decide to allow the newest version to
'replace the older one, the code will download the new version, kill the older version, and
'then load it'self into Excel. Download the example and open it. The workbook should replace
'itself with the newer version located on my server without any user intervention. (after the messagebox).
'Close it and open it again. You will not be prompted because your now have the latest file. Note
'the status text in the statusbar each time you open the workbook...


'Initial setup on your end.
'1. Copy and paste the code below into your workbook.
'
'2. Create a small textfile with two lines contained within. The date and time of your current file and the URL on the server...
'
'Example:
'
'07/08/2006 15:33
'server//logistics/dry_flood/manifest.xls
'
'Post this textfile to your server
'
'4. Change the Private Constant, "UpdateNotificationRemoteFileFullName" to reflect the URL of the textfile. This is
'the file that your clients' workbooks will check every time they are opened. The date will be compared with the date
'stored within their workbook. If the date in the textfile is more current, the update will download and replace the older version.
'
'5. After you have updated your file with this code and have performed other typical updates, run the following macro.
'"Sub Update_UpdateNotificationWorkbookName()"
'
'6. Obviously, you will need to perform an initial distribution (to your clients) of copies of the new file containing
'the macros code before this is functionally automated.
'
'7. Post your xls file to your server.
'Thereafter, the only activity that should be required of you before posting an updated file to your server is to run
'the macro, "Sub Update_UpdateNotificationWorkbookName()" after you have completed any changes. This could be automated
'as well but that's another post...
'
'NOTE! Use copies when testing. Your workbook will be replaced with the one on the server.
'Also. This code needs work and error handling.


'change this to the correct URL
Private Const UpdateNotificationRemoteFileFullName As String = "server//logistics/dry_flood/2.txt"

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

'run this everytime you are finished updating the source file
'adds or over-writes a workbook name containing the current date and time
Sub Update_UpdateNotificationWorkbookName()
ThisWorkbook.Names.Add "Update_DateTime", Now, False
ThisWorkbook.Save
'add code here to upload to server or to save to your web folder
End Sub

Sub CheckForUpdates()
Dim UpdateNotificationLocalFileFullName As String
Dim FileNumber As Integer
Dim RemoteXlsFileDate As Double
Dim RemoteXlsFileFullName As String
Dim LocalXlsFileFullName As String
Dim InputCheckForValidDate As String

UpdateNotificationLocalFileFullName = ThisWorkbook.Path & "\FileUpdateStatus.txt"

If URLDownloadToFile(0&, UpdateNotificationRemoteFileFullName, _
UpdateNotificationLocalFileFullName, 0&, 0&) = 0 Then

FileNumber = FreeFile
On Error Resume Next
Open UpdateNotificationLocalFileFullName For Input As #FileNumber
Input #FileNumber, InputCheckForValidDate
Input #FileNumber, RemoteXlsFileFullName
Close #FileNumber
Kill UpdateNotificationLocalFileFullName
On Error GoTo 0

If IsDate(InputCheckForValidDate) Then
RemoteXlsFileDate = CDate(InputCheckForValidDate)
End If

'if the name does not yet exist, an error will not be raised
'however, the statement will evaluate to True and
'attempt to download the update rather it is actually
'more recent or not
If RemoteXlsFileDate > CDbl([Update_DateTime]) Then
If MsgBox("An update is available. Click 'OK' to overwrite" & _
" the current local version with the newest version.", vbOKCancel) = vbOK Then

LocalXlsFileFullName = Replace(ThisWorkbook.FullName, ".xls", "_temp.xls")
'download updated file
If URLDownloadToFile(0&, RemoteXlsFileFullName, _
LocalXlsFileFullName, 0&, 0&) = 0 Then

On Error Resume Next
Kill Replace(LocalXlsFileFullName, "_temp.xls", "_temp_2.xls")
On Error GoTo 0

'must temporarily change the name of the activeworkbook and
'change the file access to readonly
ThisWorkbook.SaveAs Replace(LocalXlsFileFullName, "_temp.xls", "_temp_2.xls")
If ThisWorkbook.ReadOnly = False Then
ThisWorkbook.ChangeFileAccess xlReadOnly
End If

'create a name at the application level so that Open code is not run
'flag here. checked in workbook open
Application.ExecuteExcel4Macro "SET.NAME(""RunCode"",""NO"")"
Kill Replace(LocalXlsFileFullName, "_temp.xls", ".xls")

'you need to account for a file access error here
Name LocalXlsFileFullName As Replace(LocalXlsFileFullName, "_temp.xls", ".xls")
Workbooks.Open Replace(LocalXlsFileFullName, "_temp.xls", ".xls")
Application.ExecuteExcel4Macro "SET.NAME(""RunCode"")"
Kill ThisWorkbook.FullName
ThisWorkbook.Close False
Application.StatusBar = "Update Successful. You have the most current version..."

Else
Application.StatusBar = "Update available. Failed to download updated version..."
End If
Else
Application.StatusBar = "Update available. User cancelled update..."
End If
Else
Application.StatusBar = "You have the most current version..."
End If
Else
Application.StatusBar = "Failed to download update notification file..."
End If
End Sub