Download file, how do I know when the file is downloaded?

[Ago]

I use the following code and it has been working very well in the past, but now I'm geting worried that the filesize is geting larger and larger.

Code snippet:

sub test()
 DownloadXlam "http://www.hellis.me/Install_files/Flysight.xlam", "C:\Flysight\Flysight.xlam"
        MyFile = "C:\Flysight\MoveFlysight.bat"
        Set objExcel = New Excel.Application
        strExcelPath = objExcel.Path & "\"
        Set objExcel = Nothing
        
        fnum = FreeFile()
        Open MyFile For Output As #fnum
        If Application.OperatingSystem = "Windows (32-bit) NT 5.01" Then
            Print #fnum, "ping -n 3 127.0.0.1>nul"
        Else
            Print #fnum, "timeout /T 2 >nul"
        End If
        Print #fnum, "move /Y C:\Flysight\Flysight.xlam " & Chr(34) & Application.UserLibraryPath & "Flysight.xlam" & Chr(34)
        Print #fnum, "start " & Chr(34) & " " & Chr(34) & " " & Chr(34) & strExcelPath & "excel.exe" & Chr(34) & " " & Chr(34) & Application.ActiveWorkbook.Path & "\" & wkbName & Chr(34)
        Close #fnum
        
        Shell MyFile, vbNormalFocus
        On Error Resume Next
        Application.Interactive = False
        AppActivate "Microsoft Excel"
        Application.Quit
end sub
 
Private Function DownloadXlam(URL As String, LocalFilename As String) As Boolean
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadXlam = True
End Function

The code probably won't run due to many variables are "missing".

The first line downloads the file to a folder and after that it creates a bat-file that later moves the xlam-file (installs it). (the ? & Now is to make sure it does not read from cache memory)
Because the xlam file is currently 1.2 MB I'm worried that if the user has a slow internet the VBA code will finnish creating the bat-file and running the bat-file before the file has been downloaded.
Is this something I should be worried about? Or does the code "wait" for the file?
Anyone who knows? Or if it does not wait what do I need to do to make it wait?

[snb]

Why don't you simply use:

Sub M_snb() 
    with workbooks.open("http://www.hellis.me/Install_files/Flysight.xlam")
      .saveas "C:\Flysight\Flysight.xlam",52
    end with
End Sub

[Paul_Hossler]

There's a difference I think between just copying a xlam file to the UserLibrary, and installing the same xlam.

That seems like a lot of effort if all you want to do is have an open Excel WB copy an XLAM from the server, put it into the user's add in folder, and I assume you want it installed as well (not just copied?)

The WB to remain open but the add in installed to be used ??

Paul

[Ago]

I don't think I can open the file from web like that.
This code is to update the xlam installed.
So the user already have a file installed and open called Flysight.xlam.

The reason (as far as I know) I need to download the file to a different location is because windows don't allow you to overwrite a already open file.

That's why I use a bat file to run while Excel is closed to replace the downloaded file with the new version.

When I upload a new version all users get informed about it and asked if they wish to update, the code above runs and replaces the xlam.
After the update is done Excel opens again and shows release notes to the user.


So this is why I probably need to download the file, and need to know when it has been downloaded.

[snb]

to 'download'

Sub M_snb() 
    workbooks.open("http://www.hellis.me/Install_files/Flysight.xlam") 
End Sub

In the workbook

Private Sub Workbook_Open()
  ' on error resume next

  if thisworkbook.path<>"C:\Flysight" then
     thisworkbook.saveCopyas "C:\Flysight\Flysight.xlam"

     addins.add "C:\Flysight\Flysight.xlam"
     addins("Flysight.xlam").installed=true

     thisworkbook.close
  end if
End Sub

[/FONT]

[Paul_Hossler]

The reason (as far as I know) I need to download the file to a different location is because windows don't allow you to overwrite a already open file.

What I've done in the past is to uninstall the xlam ( .Installed = False), delete the xlam (Kill file.xlam), save the updated xlam in the UserLibrary, and then reinstall the xlam (.Installed = True)

That way it isn't open.

OF course this assumes that the active WB with the macro is not the xlam file. If it is, then it's a little more complicated.

Paul

[Ago]

What I've done in the past is to uninstall the xlam ( .Installed = False), delete the xlam (Kill file.xlam), save the updated xlam in the UserLibrary, and then reinstall the xlam (.Installed = True)

That way it isn't open.

OF course this assumes that the active WB with the macro is not the xlam file. If it is, then it's a little more complicated.

Paul

And this is exactly the thing I have here.
The xlam is updating itself by downloading a new version, closing Excel, and running a bat-file that replaces the xlam.

And that is why I mean I can't open the file from internet as snb keeps saying. Excel can't have two files with the same name open at the same time and overwriting a active file.
And it's why I use the code above, because it can update a running xlam. (by closing Excel and replacing the file with the bat-file)

The problem with updating is already solved, but I need to know if Excel waits for the file to be downloaded or not.

[snb]

That's why you should close /uninstall the file first, before downloading it. No big deal.

[Ago]

That's why you should close /uninstall the file first, before downloading it. No big deal.

You have not read a single word have you?

[snb]

did you read/understand #5 ?

[Ago]

did you read/understand #5 ?

Yes.
I can't have the same file open twice. Please understand....

Look at the attached image.

[Paul_Hossler]

Maybe you could integrate the command processor START command with the /WAIT /B options into your batch file

Print #fnum, "start /wait /b move /Y C:\Flysight\Flysight.xlam " & Chr(34) & Application.UserLibraryPath & "Flysight.xlam" & Chr(34)

I can't test your specific case

Paul

[Ago]

At that point in the code, if you are talking about when the actual bat-file is beeing opened, Excel has already closed and if I then notice the file has not been downloaded it's "too late".

I ended up looking for the file and if it's not there then application.wait 5 seconds and test again.
When you have waited 15 seconds you get the option to abort the update or download the file manually.

Very ugly solution, but it "works"...

 MsgBox ("Excel will close during the update." & vbCr & "When the update is finnsihed Excel will open the CSV-file again")
        DownloadXlam "http://www.hellis.me/Install_files/Flysight.xlam?" & Now, "C:\Flysight\Flysight.xlam"
LookAgain:
        If Len(Dir("C:\Flysight\Flysight.xlam")) = 0 Then
            If i >= 3 Then
                MsgBox ("File is not downloaded yet. Could be internet connection." & vbCrLf & "You can download the file manually and place it in C:\Flysight\" & vbCrLf & vbCrLf & "http://www.hellis.me/Install_files/Flysight.xlam")
                Ans = MsgBox("Do you wish to abort update?" & vbCrLf & "Yes = continue with FlySight macro without update" & vbCrLf & "No = you have downloaded the file and it's in C:\Flysight\ now.", vbYesNo)
                If Ans = vbYes Then
                    GoTo NoUpdate
                End If
            End If
            Application.Wait (Now + TimeValue("0:00:05"))
            i = i + 1
            GoTo LookAgain
        Else
            MyFile = "C:\Flysight\MoveFlysight.bat"
            Set objExcel = New Excel.Application
            strExcelPath = objExcel.Path & "\"
            Set objExcel = Nothing
            
            fnum = FreeFile()
            Open MyFile For Output As #fnum
            If Application.OperatingSystem = "Windows (32-bit) NT 5.01" Then
                Print #fnum, "ping -n 3 127.0.0.1>nul"
            Else
                Print #fnum, "timeout /T 2 >nul"
            End If
            Print #fnum, "move /Y C:\Flysight\Flysight.xlam " & Chr(34) & Application.UserLibraryPath & "Flysight.xlam" & Chr(34)
            Print #fnum, "start " & Chr(34) & " " & Chr(34) & " " & Chr(34) & strExcelPath & "excel.exe" & Chr(34) & " " & Chr(34) & Application.ActiveWorkbook.Path & "\" & wkbName & Chr(34)
            Close #fnum
            
            Shell MyFile, vbNormalFocus
            On Error Resume Next
            Application.Interactive = False
            AppActivate "Microsoft Excel"
            Application.Quit
            Exit Sub
        End If

[Paul_Hossler]

No

if you are talking about when the actual bat-file is being opened, Excel has already closed

What I was suggesting is to build the "START /WAIT /B ..." into the move .BAT file line, use .OnTIme to schedule running the .BAT, close Excel, let the .BAT execute, and then have the .BAT start Excel

It seems it 99.99% of what you already had

Paul