CHECK last date modify file on web
-
- PlatinumLounger
- Posts: 4380
- Joined: 26 Apr 2010, 17:36
CHECK last date modify file on web
https://raw.githubusercontent.com/opend ... stemmi.csv
and in c:\mydir\stemmi.csv
i need to download the .csv from web/url if the date is newer than the file on c:\mydir\stemmi.csv
is possible?
and in c:\mydir\stemmi.csv
i need to download the .csv from web/url if the date is newer than the file on c:\mydir\stemmi.csv
is possible?
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: CHECK last date modify file on web
How are you doing the download currently?
-
- PlatinumLounger
- Posts: 4380
- Joined: 26 Apr 2010, 17:36
Re: CHECK last date modify file on web
yes
MYURL = "https://raw.githubusercontent.com/opend ... stemmi.csv"
Private Sub DOWNLOAD_STEMMI()
Dim objReq As Object
Dim objStream As Object
Set objReq = CreateObject("MSXML2.XMLHTTP")
objReq.Open "GET", MYURL, False
objReq.send
If objReq.Status = 200 Then
Set objStream = CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = 1
objStream.Write objReq.responseBody
objStream.Position = 0
objStream.SaveToFile "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\STEMMI.csv", 2
objStream.Close
Set objStream = Nothing
End If
Set objReq = Nothing
'Call LEGGI_CAP
End Sub
Last edited by sal21 on 03 Aug 2023, 14:42, edited 2 times in total.
-
- Administrator
- Posts: 78676
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: CHECK last date modify file on web
That's not an answer, Sal. SpeakEasy asked "How".
Best wishes,
Hans
Hans
-
- PlutoniumLounger
- Posts: 15667
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: CHECK last date modify file on web
Yes sal21; this is possible. I do it in reverse in my web compiler: If the file to be uploaded has a later timestamp than the file on the web site, then I upload the newer version.
How good is your knowledge of VBA?
Cheers, Chris
He who plants a seed, plants life.
-
- PlatinumLounger
- Posts: 4380
- Joined: 26 Apr 2010, 17:36
Re: CHECK last date modify file on web
GOOD!ChrisGreaves wrote: ↑03 Aug 2023, 14:42Yes sal21; this is possible. I do it in reverse in my web compiler: If the file to be uploaded has a later timestamp than the file on the web site, then I upload the newer version.
How good is your knowledge of VBA?
Cheers, Chris
But remember i'm on vb6
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: CHECK last date modify file on web
Ok, you can do this with the addition of 1 line of code
Code: Select all
myurl = "https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/stemmi.csv"
mylocalfile = "d:\downloads\deleteme\STEMMI.csv"
Private Sub speakeasyversion()
Dim objReq As Object
Dim objStream As Object
Set objReq = CreateObject("MSXML2.XMLHTTP")
objReq.Open "GET", myurl, False
If Dir(mylocalfile) <> "" Then objReq.setRequestHeader "If-Modified-Since", Format(FileDateTime(mylocalfile), "ddd, dd mmm yyyy hh:MM:ss G\MT")
objReq.send
If objReq.Status = 200 Then
Set objStream = CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = 1
objStream.Write objReq.responseBody
objStream.Position = 0
objStream.SaveToFile mylocalfile, 2
objStream.Close
Set objStream = Nothing
End If
Set objReq = Nothing
End Sub
-
- PlatinumLounger
- Posts: 4380
- Joined: 26 Apr 2010, 17:36
Re: CHECK last date modify file on web
work tks!SpeakEasy wrote: ↑03 Aug 2023, 15:36Ok, you can do this with the addition of 1 line of code
Code: Select all
myurl = "https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/stemmi.csv" mylocalfile = "d:\downloads\deleteme\STEMMI.csv" Private Sub speakeasyversion() Dim objReq As Object Dim objStream As Object Set objReq = CreateObject("MSXML2.XMLHTTP") objReq.Open "GET", myurl, False If Dir(mylocalfile) <> "" Then objReq.setRequestHeader "If-Modified-Since", Format(FileDateTime(mylocalfile), "ddd, dd mmm yyyy hh:MM:ss G\MT") objReq.send If objReq.Status = 200 Then Set objStream = CreateObject("ADODB.Stream") objStream.Open objStream.Type = 1 objStream.Write objReq.responseBody objStream.Position = 0 objStream.SaveToFile mylocalfile, 2 objStream.Close Set objStream = Nothing End If Set objReq = Nothing End Sub
note:
how to debug.print this line to see the info:
objReq.setRequestHeader "If-Modified-Since"
-
- PlatinumLounger
- Posts: 4380
- Joined: 26 Apr 2010, 17:36
Re: CHECK last date modify file on web
sal21 wrote: ↑03 Aug 2023, 15:58work tks!SpeakEasy wrote: ↑03 Aug 2023, 15:36Ok, you can do this with the addition of 1 line of code
Code: Select all
myurl = "https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/stemmi.csv" mylocalfile = "d:\downloads\deleteme\STEMMI.csv" Private Sub speakeasyversion() Dim objReq As Object Dim objStream As Object Set objReq = CreateObject("MSXML2.XMLHTTP") objReq.Open "GET", myurl, False If Dir(mylocalfile) <> "" Then objReq.setRequestHeader "If-Modified-Since", Format(FileDateTime(mylocalfile), "ddd, dd mmm yyyy hh:MM:ss G\MT") objReq.send If objReq.Status = 200 Then Set objStream = CreateObject("ADODB.Stream") objStream.Open objStream.Type = 1 objStream.Write objReq.responseBody objStream.Position = 0 objStream.SaveToFile mylocalfile, 2 objStream.Close Set objStream = Nothing End If Set objReq = Nothing End Sub
note:
how to debug.print this line to see the info:
objReq.setRequestHeader "If-Modified-Since"
and wath is the property for date created of https://raw.githubusercontent.com/opend ... stemmi.csv
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: CHECK last date modify file on web
Firstly, that's a different question from the one you posed in your original post, and secondly If-Modified-Since is a Request Header,, not a Rersponse Header, so you can't retrieve it or see a useful value.to see the info:
objReq.setRequestHeader "If-Modified-Since"
if what you are looking for is the last modified date of a file on a webserver then a) I believe you have already been given a possible solution to this on another website (https://www.tek-tips.com/viewthread.cfm?qid=1824907) and b) that answer points out that not all webservers return a last-modified value - and a quick check of raw.githubusercontent.com/opendatasicilia quickly indicates that it is one such site (this is sometimes done to help optimise certain HTTP cache operations)
That being said, ChrisGreaves seems to suggest he has a method for examining timestamps on the server. Perhaps it differs from what we have been looking at here, and can resolve the issue for you.
-
- PlutoniumLounger
- Posts: 15667
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: CHECK last date modify file on web
Sal, see if you can download the zip file from http://www.chrisgreaves.com/Downloads/Sal21.zip.
Within there is a Word2003/VBA template whose text begins:-
Sal21: My apologies for the delay in responding. It has been a long time since I rewrote WbWrd and of course even longer since the original code was written (circa 1997).
WbWrd works – I use it daily – but by the nature of its design (start anywhere in a document tree and make sure that the most recent version of piece of text and every image is updated on the web by traversing the tree) the code is complex. Many of the routines need to be partitioned so that they once again fit on a computer screen; they just keep growing.
I suspect that your VB is better than my VBA, so please take a quick (<=10 minutes) look at the code in “Sub TESTEnumFiles”. If you think that is enough to get you started, then you are good to go. Otherwise get back to me and I will produce a very small working application that (Word VBA) obtains the server timestamp for a file and tests it against the timestamp for a local file.
and goes on from there. It might be faster for you to develop from scratch from the MSoft https://learn.microsoft.com/en-us/windo ... firstfilea than to fathom my code. If not, then I'll be happy to set up a stripped-down sample VBA code.
Cheers, Chris
He who plants a seed, plants life.
-
- PlatinumLounger
- Posts: 4380
- Joined: 26 Apr 2010, 17:36
Re: CHECK last date modify file on web
I cannot download the zip file!ChrisGreaves wrote: ↑04 Aug 2023, 11:33Sal, see if you can download the zip file from http://www.chrisgreaves.com/Downloads/Sal21.zip.
Within there is a Word2003/VBA template whose text begins:-
Sal21: My apologies for the delay in responding. It has been a long time since I rewrote WbWrd and of course even longer since the original code was written (circa 1997).
WbWrd works – I use it daily – but by the nature of its design (start anywhere in a document tree and make sure that the most recent version of piece of text and every image is updated on the web by traversing the tree) the code is complex. Many of the routines need to be partitioned so that they once again fit on a computer screen; they just keep growing.
I suspect that your VB is better than my VBA, so please take a quick (<=10 minutes) look at the code in “Sub TESTEnumFiles”. If you think that is enough to get you started, then you are good to go. Otherwise get back to me and I will produce a very small working application that (Word VBA) obtains the server timestamp for a file and tests it against the timestamp for a local file.
and goes on from there. It might be faster for you to develop from scratch from the MSoft https://learn.microsoft.com/en-us/windo ... firstfilea than to fathom my code. If not, then I'll be happy to set up a stripped-down sample VBA code.
Cheers, Chris
add it on this post, please.
-
- PlutoniumLounger
- Posts: 15667
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: CHECK last date modify file on web
Sal, why can't you download it?
I just tried again (twice) and tested the link before posting (hence (1) and (2)). It downloads fine from my link.
What message do you see when you click on the link in my post?
Try using Right-Click and "save link as" or similar.
At 500+ Kb the zip file is too large for attaching to a post here; that's why I made it available from my site.
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: CHECK last date modify file on web
Ah, suspected as much - an FTP solution. Yes, if you've talking to an FTP server, then there are options (but with caveats*), but sal21 is talking over an HTTP connection. And Github does not support FTP (heck, it doesn't even support more modern, secure implementations such as SFTP - and even if it did, then wininet API is too old to work with it ...)_
* FTP doesn't actually provide any standard means of enumerating, so some of the common information about files, such as file creation date and time, is not always available or correct. So whilst you can indeed try and retrieve ftLastWrite there is no guarantee whatsoever that it contains correct info - Microsoft's documentations states "FtpFindFirstFile and InternetFindNextFile fill in unavailable information with a best guess based on available information". That being said ftLastWriteTime is one of the bits of info that is often available - shame that sal21 isn't talking FTP
* FTP doesn't actually provide any standard means of enumerating, so some of the common information about files, such as file creation date and time, is not always available or correct. So whilst you can indeed try and retrieve ftLastWrite there is no guarantee whatsoever that it contains correct info - Microsoft's documentations states "FtpFindFirstFile and InternetFindNextFile fill in unavailable information with a best guess based on available information". That being said ftLastWriteTime is one of the bits of info that is often available - shame that sal21 isn't talking FTP
-
- PlutoniumLounger
- Posts: 15667
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: CHECK last date modify file on web
I reduced the scope of the PNG file and eliminated all the VBA code so at least now you can read my notes.
I see Speakeasy's comments ...
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.
-
- PlutoniumLounger
- Posts: 15667
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: CHECK last date modify file on web
Thanks Speakeasy.
As year succeeds each year I know less and less about computing.
I missed the bit about HTTP.
I note too your comments on even FTP "not working".
I know that 25 years ago I was thinking of and possibly wrote the code to "test timestamp on server". Perhaps it didn't work. Perhaps I invested more thinking-time into the design.
At any rate, my new logic that says "Upload the source image if its timestamp is more recent than the source HTML" makes more sense to me.
Thanks, Chris
He who plants a seed, plants life.
-
- PlutoniumLounger
- Posts: 15667
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: CHECK last date modify file on web
Attached the three BAS files which I think contain the essential elements.
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.
-
- PlutoniumLounger
- Posts: 15667
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: CHECK last date modify file on web
Hello again, Speakeasy. As year succeeds each year I try to learn more and more about computing, so I was giving your comments some thought.
I suspect that the difference between my solution and Sal21's problem is that I ***can*** use FTP because it is my web site and I have the password, and hence have access to the folders/files.
In Sal21's case it is NOT his web site, so he has what I think of as a type of read-only access - as we all do to all web sites with http://www.
I can use my phone to read posts on Eileen's Lounge without logging in to Eileen's Lounge, but if I want to post a reply, let alone upload/download a file, then I must login with my user name and thereby gain privileged access to the coding behind the NewTopic, Reply, and Attach command buttons. That coding may well be doing an FTP transfer, but then the phpBB software has the passwords required to do FTPing on my behalf, and allows me to use the commands by virtue of my having logged in.
Sal21 could (I suspect) easily write a bit of VB6 code, mimicking my efforts in VBA, to check the timestamp of a file on a server, but only if the server was accessing a domain "owned" by Sal21.
Am I at least close to the truth?
Thanks, Chris
He who plants a seed, plants life.
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: CHECK last date modify file on web
>I ***can*** use FTP because it is my web site
No, not precisely. It is because your website (or whoever is hosting your website) is running an FTP server. Both HTTP and FTP are protocols for moving files, but they have very different purposes and operate very, very differently and are not interchangeable. FTP is one of the older protocols on the internet (it certainly predates HTTP), dating back to early 1970s. As a result it was designed at a time when the internet was considered a safe environment, and thus had no real security built in to it. These days the internet is a different place, and as a result fewer and fewer commercial sites continue to offer simple FTP. Note that FTP is a plain text protocol, so both your username and password - if connecting to a site without anonymous access enabled - are transmitted in clear, for all the world to see if they so choose. Most implementations these days are SFTP or FTPS (google them ), which are encrypted. But the FTP stack encapsulated in wininet does not support SFTP or FTPS.
No, not precisely. It is because your website (or whoever is hosting your website) is running an FTP server. Both HTTP and FTP are protocols for moving files, but they have very different purposes and operate very, very differently and are not interchangeable. FTP is one of the older protocols on the internet (it certainly predates HTTP), dating back to early 1970s. As a result it was designed at a time when the internet was considered a safe environment, and thus had no real security built in to it. These days the internet is a different place, and as a result fewer and fewer commercial sites continue to offer simple FTP. Note that FTP is a plain text protocol, so both your username and password - if connecting to a site without anonymous access enabled - are transmitted in clear, for all the world to see if they so choose. Most implementations these days are SFTP or FTPS (google them ), which are encrypted. But the FTP stack encapsulated in wininet does not support SFTP or FTPS.
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: CHECK last date modify file on web
I wouldn't bother recency.
With this code you are always uptodate:
With this code you are always uptodate:
Code: Select all
Sub M_snb()
Workbooks.OpenText "http://www.snb-vba.eu/bestanden/Q_test.csv"
ActiveWorkbook.SaveAs "G:\OF\Q_test.csv", 23
ActiveWorkbook.Close 0
End Sub