Import from the web

[FA]

Hello,
I am using Excel, Get External Data, From Web to import data regulary from
this site: ftp://ftp.bls.gov/pub/time.series/sm/sm.data.40.PuertoRico

If I want to get the latest data I just click on the refresh button and then
refresh the pivot table.

I would like maintain my data in Access instead, but I can't find how to
link the table to the web the way I do it with Excel.

I read in some post that you have to save the html file in your hard disk.
That in my understanding beats the prupose because I want to do the least
steps possibe because I would do this with several web sources. I understand
I would have to save the html in my hard drive everytime, for each of the
websites, right?

How can I do what I need to do? Any suggestions?

 

[Albert D. Kallal]

There is an example code snip here:

http://www.mvps.org/access/modules/mdl0015.htm

 

[FA]

Thank you for your response.
I've been using Access for some time now but I have never used the VBA that
it includes. I have used Excel's enough (i'm not an expert but I usually
would copy and modify codes just like yours) but not Access'.

This is what I'm trying:
I placed your code in a module.

Sub sFTP(stSCRFile As String)
Dim stSysDir As String
stSysDir = Environ$("COMSPEC")
stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))
Call Shell(stSysDir & "ftp.exe -s:" & stSCRFile, vbNormalFocus)
End Sub

Now I'm trying to run the code through a Macro but is not working.
This is my first time working with VBA in Access so probably I'm way out of
the mark...

Maybe if you break the steps down a little further...

Regards,

FA

 

[Albert D. Kallal]

Maybe if you break the steps down a little further...

Regards,

Ok, you can paste hte follwoing code into a standard access module.

Option Compare Database
Option Explicit

'
Public Sub DownLoad(strWebSite As String, strWebFile As String, strLocalFile
As String)

Dim intF As Integer
Dim strF As String
Dim strCmd As String
Const q = """"


' now create our batch file...
strF = CurrentProject.Path & "\ftp.txt"
On Error Resume Next
Kill strF
On Error GoTo 0

intF = FreeFile()
Open strF For Output As #intF
Print #intF, "open " & strWebSite
Print #intF, "anonymous"
Print #intF, "(e-mail address removed)"
'Print #intF, "cd " & strWebDir
Print #intF, "get " & q & strWebFile & q & " " & q & strLocalFile & q
Print #intF, "bye"
Close intF

strCmd = "ftp -s:" & q & strF & q
Call Shell(strCmd, vbNormalFocus)

End Sub


Sub MyTestDown()

Call DownLoad("ftp.bls.gov", "/pub/time.series/sm/sm.data.40.PuertoRico",
"c:\t2.txt")

End Sub


You should change the above (e-mail address removed) to your email (you can actually
use any email if you want).

Save the code module (you usually have to give it a name like module1 (make
sure the module name does not conflict with any sub name inside of the
module

Once you saved the above module. then open up the module, and in the command
line type in

MytestDown

That above code will run.

You could also from behind a form button code simply call that above code:

Call DownLoad("ftp.bls.gov", "/pub/time.series/sm/sm.data.40.PuertoRico",
"c:\t2.txt")

After hte download, then you have a nice text file and you can simply improt
that.

In fact, once you get the download working, then you add addiotnoal code to
automatic import the code. So, the whole process can occur with one click of
the button...

 

[FA]

Thank you very much!

Thank you!

FA

Ok, you can paste hte follwoing code into a standard access module.

Option Compare Database
Option Explicit

'
Public Sub DownLoad(strWebSite As String, strWebFile As String, strLocalFile
As String)

Dim intF As Integer
Dim strF As String
Dim strCmd As String
Const q = """"


' now create our batch file...
strF = CurrentProject.Path & "\ftp.txt"
On Error Resume Next
Kill strF
On Error GoTo 0

intF = FreeFile()
Open strF For Output As #intF
Print #intF, "open " & strWebSite
Print #intF, "anonymous"
Print #intF, "(e-mail address removed)"
'Print #intF, "cd " & strWebDir
Print #intF, "get " & q & strWebFile & q & " " & q & strLocalFile & q
Print #intF, "bye"
Close intF

strCmd = "ftp -s:" & q & strF & q
Call Shell(strCmd, vbNormalFocus)

End Sub


Sub MyTestDown()

Call DownLoad("ftp.bls.gov", "/pub/time.series/sm/sm.data.40.PuertoRico",
"c:\t2.txt")

End Sub


You should change the above (e-mail address removed) to your email (you can actually
use any email if you want).

Save the code module (you usually have to give it a name like module1 (make
sure the module name does not conflict with any sub name inside of the
module

Once you saved the above module. then open up the module, and in the command
line type in

MytestDown

That above code will run.

You could also from behind a form button code simply call that above code:

Call DownLoad("ftp.bls.gov", "/pub/time.series/sm/sm.data.40.PuertoRico",
"c:\t2.txt")

After hte download, then you have a nice text file and you can simply improt
that.

In fact, once you get the download working, then you add addiotnoal code to
automatic import the code. So, the whole process can occur with one click of
the button...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


.