Is Excel "caching" pages ?

Hi everyone !

In my Excel application, I use the following code to get a table from my web
site:
Sub ReadWeb()
Workbooks.OpenText FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
End Sub

To my greatest surprise, the output does NOT reflect the actual changes done
to that file !

Is Excel using some "caching" as IE does ????



Thanks and regards from Belgium,
Hervé+

Yes, I believe it is. (more precisely, the caching mechanism is being shared
by both applications). You will need to delete the file in the cache
before retrieving I would think.

--
Regards,
Tom Ogilvy

"affordsol" <affordsol@discussions.microsoft.com> wrote in message
news:81F3FCEF-5DF2-4E32-A72C-8770493244E7@microsoft.com...
> Hi everyone !
>
> In my Excel application, I use the following code to get a table from my
web
> site:
> Sub ReadWeb()
> Workbooks.OpenText
FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
> End Sub
>
> To my greatest surprise, the output does NOT reflect the actual changes
done
> to that file !
>
> Is Excel using some "caching" as IE does ????
>
>
>
> Thanks and regards from Belgium,
> Hervé+

Hello Tom, nice to hear from you again !

Have any idea of how to code it so as to delete the file in the cache ?

regards from Belgium,
Hervé+



"Tom Ogilvy" wrote:

> Yes, I believe it is. (more precisely, the caching mechanism is being shared
> by both applications). You will need to delete the file in the cache
> before retrieving I would think.
>
> --
> Regards,
> Tom Ogilvy
>
> "affordsol" <affordsol@discussions.microsoft.com> wrote in message
> news:81F3FCEF-5DF2-4E32-A72C-8770493244E7@microsoft.com...
> > Hi everyone !
> >
> > In my Excel application, I use the following code to get a table from my
> web
> > site:
> > Sub ReadWeb()
> > Workbooks.OpenText
> FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
> > End Sub
> >
> > To my greatest surprise, the output does NOT reflect the actual changes
> done
> > to that file !
> >
> > Is Excel using some "caching" as IE does ????
> >
> >
> >
> > Thanks and regards from Belgium,
> > Hervé+
>
>
>

Here is some code posted previously by Bernie Deitrick:

-------------------------------
Wijnand,

Here's a little sub that will remove the temporary files
named myfile*.*. Just make sure your path is correct, and
change the name to match your needs. (And the msgbox lines
are just for troubleshooting initially.)


HTH,
Bernie


Sub FindKill()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Windows\Temporary Internet Files"
.SearchSubFolders = True
.FileName = "myfile*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s)
found."
For i = 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
------------------------------

--

Regards,

Tom Ogilvy





"affordsol" <affordsol@discussions.microsoft.com> wrote in message
news:AA1C1861-B14A-48B7-BD6B-2E3321035E4A@microsoft.com...
> Hello Tom, nice to hear from you again !
>
> Have any idea of how to code it so as to delete the file in the cache ?
>
> regards from Belgium,
> Hervé+
>
>
>
> "Tom Ogilvy" wrote:
>
> > Yes, I believe it is. (more precisely, the caching mechanism is being
shared
> > by both applications). You will need to delete the file in the cache
> > before retrieving I would think.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "affordsol" <affordsol@discussions.microsoft.com> wrote in message
> > news:81F3FCEF-5DF2-4E32-A72C-8770493244E7@microsoft.com...
> > > Hi everyone !
> > >
> > > In my Excel application, I use the following code to get a table from
my
> > web
> > > site:
> > > Sub ReadWeb()
> > > Workbooks.OpenText
> > FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
> > > End Sub
> > >
> > > To my greatest surprise, the output does NOT reflect the actual
changes
> > done
> > > to that file !
> > >
> > > Is Excel using some "caching" as IE does ????
> > >
> > >
> > >
> > > Thanks and regards from Belgium,
> > > Hervé+
> >
> >
> >

This was posted by Tim Williams:

From: Tim Williams - view profile
Date: Sat, Feb 8 2003 10:11 pm
Email: "Tim Williams" <saxifrax@pacbell*dot*net>
Groups: microsoft.public.excel.programming


Have not tried it with an .xls file, but the following always works with web
content (.html etc)

file2 = "http://mysite/myfile.xls?blah=" & Format(Time, "hhmmss")
Workbooks.open filename:= file2


The dummy querystring forces a reload rather than a fetch from the cache,
since it's considered part of the URL. It's ignored by the webserver
however.


Tim.

====
I've never used it.

If you try it, please post back with your results.


affordsol wrote:
>
> Hi everyone !
>
> In my Excel application, I use the following code to get a table from my web
> site:
> Sub ReadWeb()
> Workbooks.OpenText FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
> End Sub
>
> To my greatest surprise, the output does NOT reflect the actual changes done
> to that file !
>
> Is Excel using some "caching" as IE does ????
>
> Thanks and regards from Belgium,
> Hervé+

--

Dave Peterson

In addition to Tom's suggestion you might also try

'**************************
Sub ReadWeb()
dim r as string
dim fName as string

fName = "http://www.MYSITE.be/TestPHP/profsactual.xls"

r=cstr(rnd() * 1e6)
Workbooks.OpenText FileName:= fName & "?x=" & r

End Sub
'**************************

Addition of a random querystring to the URL should defeat caching.

--
Tim Williams
Palo Alto, CA


"affordsol" <affordsol@discussions.microsoft.com> wrote in message
news:AA1C1861-B14A-48B7-BD6B-2E3321035E4A@microsoft.com...
> Hello Tom, nice to hear from you again !
>
> Have any idea of how to code it so as to delete the file in the cache ?
>
> regards from Belgium,
> Hervé+
>
>
>
> "Tom Ogilvy" wrote:
>
> > Yes, I believe it is. (more precisely, the caching mechanism is being
shared
> > by both applications). You will need to delete the file in the cache
> > before retrieving I would think.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "affordsol" <affordsol@discussions.microsoft.com> wrote in message
> > news:81F3FCEF-5DF2-4E32-A72C-8770493244E7@microsoft.com...
> > > Hi everyone !
> > >
> > > In my Excel application, I use the following code to get a table from
my
> > web
> > > site:
> > > Sub ReadWeb()
> > > Workbooks.OpenText
> > FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
> > > End Sub
> > >
> > > To my greatest surprise, the output does NOT reflect the actual
changes
> > done
> > > to that file !
> > >
> > > Is Excel using some "caching" as IE does ????
> > >
> > >
> > >
> > > Thanks and regards from Belgium,
> > > Hervé+
> >
> >
> >

Thanks Dave - missed your post before adding yet another variant...

--
Tim Williams
Palo Alto, CA


"Tim Williams" <timjwilliams at gmail dot com> wrote in message
news:%23WUmIUmNGHA.2920@TK2MSFTNGP10.phx.gbl...
> In addition to Tom's suggestion you might also try
>
> '**************************
> Sub ReadWeb()
> dim r as string
> dim fName as string
>
> fName = "http://www.MYSITE.be/TestPHP/profsactual.xls"
>
> r=cstr(rnd() * 1e6)
> Workbooks.OpenText FileName:= fName & "?x=" & r
>
> End Sub
> '**************************
>
> Addition of a random querystring to the URL should defeat caching.
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "affordsol" <affordsol@discussions.microsoft.com> wrote in message
> news:AA1C1861-B14A-48B7-BD6B-2E3321035E4A@microsoft.com...
> > Hello Tom, nice to hear from you again !
> >
> > Have any idea of how to code it so as to delete the file in the cache ?
> >
> > regards from Belgium,
> > Hervé+
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > Yes, I believe it is. (more precisely, the caching mechanism is being
> shared
> > > by both applications). You will need to delete the file in the cache
> > > before retrieving I would think.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "affordsol" <affordsol@discussions.microsoft.com> wrote in message
> > > news:81F3FCEF-5DF2-4E32-A72C-8770493244E7@microsoft.com...
> > > > Hi everyone !
> > > >
> > > > In my Excel application, I use the following code to get a table
from
> my
> > > web
> > > > site:
> > > > Sub ReadWeb()
> > > > Workbooks.OpenText
> > > FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
> > > > End Sub
> > > >
> > > > To my greatest surprise, the output does NOT reflect the actual
> changes
> > > done
> > > > to that file !
> > > >
> > > > Is Excel using some "caching" as IE does ????
> > > >
> > > >
> > > >
> > > > Thanks and regards from Belgium,
> > > > Hervé+
> > >
> > >
> > >
>
>

Hello,
As you asked, I revert to the subject:
I've tried all of the suggestions : they all work sucessfully.
Only the FindKill of Bernie Deitrick poses problem because of the "myfile*"
which, in this case, cannot be precisely defined.
So : a big thank you

NOW: In this specific case, I came to think that a full refresh was not
necessary.
In fact, IF I could just access two cells of the web-site resident .xls
file, I could accomplish my update as well.

QUESTION: how can I get the contents of
http://www.MYSITE.be/TestPHP/profsactual.xls
Range ("AJ64") and Range("AK64")
(my Excel 97 SR-2 is localized in French and I keep using Excel97 when
developing for others just for compatibility reasons)
I think this could be done even without opening the file ?

Another one more way would be to access to the mysql dbase from which the
..xls file is derived : but, here again, this might pose problems when porting
to different users who don't necessary have the right references checked.

This is really an interesting thread and I do appreciate the collaboration
of all of you.
regards from Belgium.
Hervé+




"Dave Peterson" wrote:

> This was posted by Tim Williams:
>
> From: Tim Williams - view profile
> Date: Sat, Feb 8 2003 10:11 pm
> Email: "Tim Williams" <saxifrax@pacbell*dot*net>
> Groups: microsoft.public.excel.programming
>
>
> Have not tried it with an .xls file, but the following always works with web
> content (.html etc)
>
> file2 = "http://mysite/myfile.xls?blah=" & Format(Time, "hhmmss")
> Workbooks.open filename:= file2
>
>
> The dummy querystring forces a reload rather than a fetch from the cache,
> since it's considered part of the URL. It's ignored by the webserver
> however.
>
>
> Tim.
>
> ====
> I've never used it.
>
> If you try it, please post back with your results.
>
>
> affordsol wrote:
> >
> > Hi everyone !
> >
> > In my Excel application, I use the following code to get a table from my web
> > site:
> > Sub ReadWeb()
> > Workbooks.OpenText FileName:="http://www.MYSITE.be/TestPHP/profsactual.xls"
> > End Sub
> >
> > To my greatest surprise, the output does NOT reflect the actual changes done
> > to that file !
> >
> > Is Excel using some "caching" as IE does ????
> >
> > Thanks and regards from Belgium,
> > Hervé+
>
> --
>
> Dave Peterson
>