CHECK last date modify file on web

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

CHECK last date modify file on web

Post by sal21 »

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?

User avatar
SpeakEasy
5StarLounger
Posts: 601
Joined: 27 Jun 2021, 10:46

Re: CHECK last date modify file on web

Post by SpeakEasy »

How are you doing the download currently?

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

Re: CHECK last date modify file on web

Post by sal21 »

SpeakEasy wrote:
03 Aug 2023, 13:01
How are you doing the download currently?
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.

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CHECK last date modify file on web

Post by HansV »

That's not an answer, Sal. SpeakEasy asked "How".
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15890
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: CHECK last date modify file on web

Post by ChrisGreaves »

sal21 wrote:
02 Aug 2023, 16:11
i need to download the .csv from web/url if the date is newer than the file on c:\mydir\stemmi.csv
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
I’ve been tidying the junk out of my shed for five years, and now can hardly get into the shed

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

Re: CHECK last date modify file on web

Post by sal21 »

ChrisGreaves wrote:
03 Aug 2023, 14:42
sal21 wrote:
02 Aug 2023, 16:11
i need to download the .csv from web/url if the date is newer than the file on c:\mydir\stemmi.csv
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
GOOD!
But remember i'm on vb6

User avatar
SpeakEasy
5StarLounger
Posts: 601
Joined: 27 Jun 2021, 10:46

Re: CHECK last date modify file on web

Post by SpeakEasy »

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

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

Re: CHECK last date modify file on web

Post by sal21 »

SpeakEasy wrote:
03 Aug 2023, 15:36
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
work tks!

note:
how to debug.print this line to see the info:

objReq.setRequestHeader "If-Modified-Since"

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

Re: CHECK last date modify file on web

Post by sal21 »

sal21 wrote:
03 Aug 2023, 15:58
SpeakEasy wrote:
03 Aug 2023, 15:36
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
work tks!

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

User avatar
SpeakEasy
5StarLounger
Posts: 601
Joined: 27 Jun 2021, 10:46

Re: CHECK last date modify file on web

Post by SpeakEasy »

to see the info:

objReq.setRequestHeader "If-Modified-Since"
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.

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.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15890
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: CHECK last date modify file on web

Post by ChrisGreaves »

sal21 wrote:
03 Aug 2023, 14:49
GOOD! But remember i'm on vb6
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
I’ve been tidying the junk out of my shed for five years, and now can hardly get into the shed

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

Re: CHECK last date modify file on web

Post by sal21 »

ChrisGreaves wrote:
04 Aug 2023, 11:33
sal21 wrote:
03 Aug 2023, 14:49
GOOD! But remember i'm on vb6
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
I cannot download the zip file!

add it on this post, please.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15890
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: CHECK last date modify file on web

Post by ChrisGreaves »

sal21 wrote:
04 Aug 2023, 12:30
I cannot download the zip file!
Untitled.png
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.
I’ve been tidying the junk out of my shed for five years, and now can hardly get into the shed

User avatar
SpeakEasy
5StarLounger
Posts: 601
Joined: 27 Jun 2021, 10:46

Re: CHECK last date modify file on web

Post by SpeakEasy »

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

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15890
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: CHECK last date modify file on web

Post by ChrisGreaves »

sal21 wrote:
04 Aug 2023, 12:30
add it on this post, please.
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.
I’ve been tidying the junk out of my shed for five years, and now can hardly get into the shed

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15890
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: CHECK last date modify file on web

Post by ChrisGreaves »

SpeakEasy wrote:
04 Aug 2023, 14:09
... - shame that sal21 isn't talking FTP
Thanks Speakeasy.
As year succeeds each year I know less and less about computing. :grin: :sad:

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
I’ve been tidying the junk out of my shed for five years, and now can hardly get into the shed

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15890
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: CHECK last date modify file on web

Post by ChrisGreaves »

sal21 wrote:
04 Aug 2023, 12:30
add it on this post, please.
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.
I’ve been tidying the junk out of my shed for five years, and now can hardly get into the shed

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15890
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: CHECK last date modify file on web

Post by ChrisGreaves »

SpeakEasy wrote:
04 Aug 2023, 14:09
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.
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? :crossfingers:

Thanks, Chris
I’ve been tidying the junk out of my shed for five years, and now can hardly get into the shed

User avatar
SpeakEasy
5StarLounger
Posts: 601
Joined: 27 Jun 2021, 10:46

Re: CHECK last date modify file on web

Post by SpeakEasy »

>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 :smile: ), which are encrypted. But the FTP stack encapsulated in wininet does not support SFTP or FTPS.

snb
5StarLounger
Posts: 606
Joined: 14 Nov 2012, 16:06

Re: CHECK last date modify file on web

Post by snb »

I wouldn't bother recency.
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