PDA

View Full Version : [SOLVED] Download file, how do I know when the file is downloaded?



Ago
03-24-2014, 08:36 AM
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
03-24-2014, 10:01 AM
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
03-24-2014, 04:20 PM
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
03-24-2014, 09:59 PM
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
03-25-2014, 01:03 AM
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
03-25-2014, 06:08 AM
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
03-25-2014, 09:08 AM
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
03-25-2014, 09:32 AM
That's why you should close /uninstall the file first, before downloading it. No big deal.

Ago
03-25-2014, 09:34 AM
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
03-25-2014, 09:50 AM
did you read/understand #5 ?

Ago
03-25-2014, 09:56 AM
did you read/understand #5 ?

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

Look at the attached image.

Paul_Hossler
03-25-2014, 05:15 PM
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
03-27-2014, 02:44 AM
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
03-27-2014, 05:08 AM
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