Import specification to select filename with latest date

[CeleAZ]

I need to have Access 2K select several files with Filename* based on the latest date from an ftp site. [ftp: Folder/Filename example: "Incoming\ABCD 20070223.csv"] and then download them to a Windows folder: C:\Projects\DataFiles\Filename*.*

Can VBA or SQL do this? If so, could someone help me out with the code?

One other question: Is there a way to automate going through queries and updating filenames in SQL coding with that date string? This is time-consuming and allows possibility of error while modifying in six different queries.

Is this possible in VBA? I'm not very familiar with SQL.

Thanks loads for help with this!!
CeleAZ

 

[AlexCuse]

You should take a look (in VBA) at TransferText method, and using DateAdd to loop through the last 5 dates or so looking for your file.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Remou]

Do you mean something like:

Sub GetFiles()
'See [URL unfurl="true"]http://www.robvanderwoude.com/index.html[/URL]

strfile = "C:\Docs\ftpdat.txt"
Open strfile For Output As #1
Print #1, "Open ftp.mysite.com"
Print #1, "User UserName"
Print #1, "Password"
Print #1, "cd folder/subfolder"
Print #1, "get ABCD" & Format(date(),"yyyymmdd") & ".csv"
Print #1, "bye"

Close #1

ChDir "C:\Docs"

Shell "FTP -n -s:""C:\Docs\ftpdat.txt""", vbNormalFocus
End Sub

 

[CeleAZ]

I have not used Print and Output in VBA before... "Print" shows in a window what's going on?
Is the shell line what brings it over to C? Can I run it as modified below, per name of file (below) I want to get?

"strfile" is where I want to save the file and the name of the file?


Sub GetFiles()

strfile = "C:\Docs\ftpdat.txt" ' ???

Open strfile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
'Print #1, "User UserName" ' not necessary
'Print #1, "Password"
Print #1, "cd Incoming" ' folder in ftp
Print #1, "get Rpt_LanDesk Assets Daily " & Format(Date, "yyyymmdd") & ".zip"
Print #1, "bye"

Close #1

ChDir "C:\_Projects\Data Files" 'where file will be saved?

Shell "FTP -n -s:""C:\_Projects\Data Files\Rpt*.zip""", vbNormalFocus
End Sub

 

[Remou]

Print is writing out the command file, so strFile is the name of the file that hold the commands. It is as well to test the series of commands by running FTP from the command line and entering each of the lines from the command file one by one. I suspect that you will need quotes for a file name with spaces.

From the above comments, you can see that the last line needs to be edited to reference the file in which you are storing the commands. The link noted at the top of the sample code was my starting point, it is well worth a visit.

 

[CeleAZ]

OK, I understand that, but after modifying the code below it does not bring in the indicated file. Where have I gone wrong? It opens a window, shows the Open ftp command, waits 12 seconds, then closes.


strfile = "C:\_Projects\FTP1.txt"

Open strfile For Output As #1
Print #1, "Open ftp://ftpus2-hon.us.com/"

Print #1, "cd Incoming" ' folder in ftp
Print #1, "get Hon Received Report " & Format(Date, "yyyymmdd") & ".zip"
Print #1, "bye"

Close #1

ChDir "C:\_Projects\Data Files" 'where file will be saved?

Shell "FTP -n -s:""C:\_Projects\FTP1.txt""", vbNormalFocus

 

[PHV]

Try this:
Print #1, "get [!]""[/!]Hon Received Report " & Format(Date, "yyyymmdd") & ".zip[!]""[/!]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CeleAZ]

Thanks much for your help.

But even with the last advice to use double quotes, it doesn't bring in the file.

Is each line of the Print... supposed to show inn the black wiindow? They don't at this point.

Maybe I will have to type in the full name of the file since the file I need is not always dated today's date; for instance, the one I need today is dated 20070224. Or Is there a way to get the "Filename " & latestdate & ".xls" or ".csv"??

I would like to code in all the four filenames with appropriate date format and have it take the latest date all saving into \Data Files folder.

If there is code to add to look for latest date only, pls tell me where in the above module it should be placed.

Thanks!!

 

[Remou]

How is latest date calculated? Or do you know the latest date? That is, can you use a variable? Roughly:

Sub GetFiles()

strDate=InputBox("Please enter date yyyymmdd")

strfile = "C:\Docs\ftpdat.txt"    '  ???

Open strfile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
'Print #1, "User UserName"   ' not necessary
'Print #1, "Password"
Print #1, "cd Incoming"      ' folder in ftp
Print #1, "get ""Rpt_LanDesk Assets Daily " & strDate & ".csv"""
Print #1, "get ""Hon Received Report " & strDate & ".zip""" 
Print #1, "bye"

Close #1

 

[CeleAZ]

If I have to go look for the date I may as well just drag it over. My plan is to not open up the ftp folder thus saving time.

 

[AlexCuse]

If you look in the FAQ's for this forum there are some functions for FTP'ing. Here is the FAQ with the functions, it has a link to another FAQ's containing the declarations section. faq705-6533

What I would do in your situation is take out the error handling from the download function and use a loop. You can start with today's date, and keep subtracting one day at a time, and trying again to get the file. When the function returns true (or you have been through 15 iterations or so, wherever you want to set the max number of days to go back) you can exit the loop. Does this make sense?

If not, I am sure people here will be able to help you through it.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Remou]

Some more notes.
This is built around:

http://groups.google.ie/group/micro...iis.ftp/browse_thread/thread/83887926248a5e4b

Sub GetFileList()
Dim strFile, ln, l1, d1
Dim rs As DAO.Recordset
Dim strSQL

strfile = "C:\_Projects\FTP1.txt"

Open strFile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
'Print #1, "User UserName"   ' not necessary
'Print #1, "Password"
Print #1, "cd Incoming"      ' folder in ftp
Print #1, "ls *.*"
Print #1, "bye"

Close #1

ChDir "C:\_Projects\"
Shell "ftp -s:ftp1.txt | find /v ""*"" > gen_CSVFileList.dat"

'Temporary table to hold generated file
If IsNull(DLookup("Name", "MSysObjects", "Name='tmp'")) Then
    strSQL = "Create Table tmp (FNameDate Char(75),FName Char(75),FDate Char(25))"
Else
    strSQL = "Delete From tmp"
End If

CurrentDb.Execute strSQL

Open "gen_CSVFileList.dat" For Input As #1
Do While Not EOF(1)
    Line Input #1, ln
    If ln Like "*.csv*" Or ln Like "*.zip*" Then
        l1 = Mid(ln, 1, Len(ln) - 11)
        d1 = Mid(ln, Len(ln) - 11, 8)
        strSQL = "Insert Into tmp (FNameDate,FName,FDate) " _
               & "Values ('" & ln & "','" _
               & l1 & "','" _
               & d1 & "')"
        CurrentDb.Execute strSQL
    End If
Loop
Close #1

strSQL = "SELECT tmp.FName, Max(tmp.FNameDate) AS MaxOfFNameDate " _
& "From tmp GROUP BY tmp.FName"

Set rs = CurrentDb.OpenRecordset(strSQL)

strfile = "C:\_Projects\FTP1.txt"

Open strFile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
'Print #1, "User UserName"   ' not necessary
'Print #1, "Password"
Do While Not rs.EOF
    Print #1, "get " & Chr(34) & Trim(rs!MaxOfFNameDate) & Chr(34)
    rs.MoveNext
Loop
Print #1, "bye"

Close #1
rs.Close
End Sub

 

[Remou]

I left out the final transfer on the end, but see now that I should have put it in, for completeness:

[tt]<...>
Print #1, "bye"

Close #1
rs.Close

Shell "FTP -n -s:""C:\_Projects\FTP1.txt""", vbNormalFocus[/tt]

 

[CeleAZ]

Thanks Remou and Alex,
I'll work on your suggestions and do more research on ftp downloading! Some of this is above my head but it's a good learning experience, right?
Thanks so much!

 

[CeleAZ]

Remou,
When I run the code I get an error "File not Found" on "gen_CSVFileList.dat" in the line :

Open "gen_CSVFileList.dat" For Input As #1

Since I follow all your code, I'm a bit lost.
Thx.

 

[Remou]

Fill in the full path, it should be in "C:\_Projects\", if it isn't, can you search and find where it is, please?

 

[PHV]

You may try to replace this:
Shell "ftp -s:ftp1.txt | find /v ""*"" > gen_CSVFileList.dat"
with this:
Shell "cmd /C ftp -s:ftp1.txt | find /v ""*"" > gen_CSVFileList.dat"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CeleAZ]

Obviously my last msg should have read "since I DON'T follow/understand all your code..." !!

OK, I did a search for that file, which was not found anywhere on C:\ but entered the path anyway.


This is what I have at this point:

Function GetFileList()
Dim strFile, ln, l1, d1

Dim rs As DAO.Recordset
Dim strSQL

strFile = "C:\_Projects\FTP1.txt"

Open strFile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
Print #1, "User UserName"
Print #1, "Password"
Print #1, "cd Incoming"
Print #1, "ls *.*"
Print #1, "bye"

Close #1

ChDir "C:\_Projects\"
Shell "cmd /C ftp -s:ftp1.txt | find /v ""*"" > C:\_Projects\Data Files\gen_CSVFileList.dat"

'Temporary table to hold generated file
If IsNull(DLookup("Name", "MSysObjects", "Name='tmp'")) Then
strSQL = "Create Table tmp (FNameDate Char(75),FName Char(75),FDate Char(25))"
Else
strSQL = "Delete From tmp"
End If

CurrentDb.Execute strSQL

Open "C:\_Projects\Data Files\gen_CSVFileList.dat" For Input As #1
Do While Not EOF(1)
Line Input #1, ln
If ln Like "*.csv*" Or ln Like "*.zip*" Then
l1 = Mid(ln, 1, Len(ln) - 11)
d1 = Mid(ln, Len(ln) - 11, 8)
strSQL = "Insert Into tmp (FNameDate,FName,FDate) " _
& "Values ('" & ln & "','" _
& l1 & "','" _
& d1 & "')"
CurrentDb.Execute strSQL
End If
Loop
Close #1

strSQL = "SELECT tmp.FName, Max(tmp.FNameDate) AS MaxOfFNameDate " _
& "From tmp GROUP BY tmp.FName"

Set rs = CurrentDb.OpenRecordset(strSQL)

strFile = "C:\_Projects\Data Files\FTP1.txt"

Open strFile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
Print #1, "User UserName" ' not necessary
Print #1, "Password"
Do While Not rs.EOF
Print #1, "get " & Chr(34) & Trim(rs!MaxOfFNameDate) & Chr(34)
rs.MoveNext
Loop
Print #1, "bye"

Close #1
rs.Close

Shell "FTP -n -s:""C:\_Projects\Data Files\FTP1.txt""", vbNormalFocus

End Function

 

[Remou]

I cannot test fully, because I can only use my own ftp site, however, the code below works for me in that I end up with some files in the expected places. I cannot list files from your server like I can from mine. Is there any thing in particular that is not working for you? Can you step through the code?

Function GetFileList()
Dim strFile, ln, l1, d1

Dim rs As DAO.Recordset
Dim strSQL

strFile = "C:\_Projects\Data Files\FTP1.txt"

Open strFile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
Print #1, "User UserName"
Print #1, "Password"
Print #1, "cd Incoming"
Print #1, "ls *.*"
Print #1, "bye"

Close #1

ChDir "C:\_Projects\Data Files\"
Shell "cmd /C ftp -s:ftp1.txt | find /v ""*"" > gen_CSVFileList.dat"

'Temporary table to hold generated file
If IsNull(DLookup("Name", "MSysObjects", "Name='tmp'")) Then
    strSQL = "Create Table tmp (FNameDate Char(75),FName Char(75),FDate Char(25))"
Else
    strSQL = "Delete From tmp"
End If

CurrentDb.Execute strSQL

Open "C:\_Projects\Data Files\gen_CSVFileList.dat" For Input As #1
Do While Not EOF(1)
    Line Input #1, ln
    If ln Like "*.csv*" Or ln Like "*.zip*" Then
        l1 = Mid(ln, 1, Len(ln) - 11)
        d1 = Mid(ln, Len(ln) - 11, 8)
        strSQL = "Insert Into tmp (FNameDate,FName,FDate) " _
               & "Values ('" & ln & "','" _
               & l1 & "','" _
               & d1 & "')"
        CurrentDb.Execute strSQL
    End If
Loop
Close #1

strSQL = "SELECT tmp.FName, Max(tmp.FNameDate) AS MaxOfFNameDate " _
& "From tmp GROUP BY tmp.FName"

Set rs = CurrentDb.OpenRecordset(strSQL)

strFile = "C:\_Projects\Data Files\FTP1.txt"

Open strFile For Output As #1
Print #1, "Open ftp://ftpus2-honeywellus.us.getronics.com/"
Print #1, "User UserName"   ' not necessary
Print #1, "Password"
Do While Not rs.EOF
    Print #1, "get " & Chr(34) & Trim(rs!MaxOfFNameDate) & Chr(34)
    rs.MoveNext
Loop
Print #1, "bye"

Close #1
rs.Close

Shell "FTP -n -s:""C:\_Projects\Data Files\FTP1.txt""", vbNormalFocus

End Function

 

[PHV]

Probably some synch issue.
You may try this:
CreateObject("WScript.Shell").Run "cmd /C ftp -s:ftp1.txt | find /v ""*"" > gen_CSVFileList.dat", 2, True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886