Link to home
Start Free TrialLog in
Avatar of marellano
marellano

asked on

Simple FTP Methods from Microsoft Access - Help with existing VB script

Hello EE,
I'm trying to have Ms Access 2007 run an existing vb script that will download a daily file from an FTP site.   The single filename will change on the daily basis.

I've tried dissecting and testing the following popular code being used:  

http://www.databasejournal.com/features/msaccess/article.php/3513061/Simple-FTP-Methods-from-Microsoft-Access.htm?comment=16204-0

There's a version of this code at the following EE posting:

https://www.experts-exchange.com/questions/22084190/Batch-file-to-download-from-an-FTP-site.html

 I've followed the steps indicated but when running the code i get a compile error in the following portion of the code:

sLocalFLD  & "download.scr"

I'm very much a novice with VB.  Can someone please walk me through in testing and making modifying this code work for my purpose?

Thanks in advance!
Public Function DownloadFTPFile(ByVal sFile As String, _
                                sSVR As String, _
                                sFLD As String, _
                                sUID As String, _
                                sPWD As String) As String
   Dim sLocalFLD As String
   Dim sScrFile As String
   Dim sTarget As String
   Dim iFile As Integer
   Dim sExe As String
   Const q As String = """"
   On Error GoTo Err_Handler
   sLocalFLD = CurrentProject.Path & "" & sFLD
   ' The scr file will contain the FTP commands
   ' (If it exists from previous run, delete it)
   sScrFile =
   sLocalFLD  & "download.scr"
   If Dir(sScrFile) <>  "" Then Kill sScrFile
   
   ' For a download, the Target is the destination file
   sTarget = q & sLocalFLD & "" & sFile & q
   sFile = q & sFile & q
   ' Open a new text file to hold the FTP script and load it 
   ' with the appropriate commands.  (Thanks Dev Ashish !!!)
   ' ///////////////////////////////////////////////////////
   iFile = FreeFile
   Open sScrFile For Output As iFile
   Print #iFile, "open " & sSVR
   Print #iFile, sUID
   Print #iFile, sPWD
   Print #iFile, "cd " & sFLD
   Print #iFile, "binary"
   Print #iFile, "lcd " & q & sLocalFLD & q
   Print #iFile, "get " & sFile & " " & sTarget
   Print #iFile, "bye"
   Close #iFile
   sExe = Environ$("COMSPEC")
   sExe = Left$(sExe, Len(sExe) - Len(Dir(sExe)))
   sExe = sExe & "ftp.exe -s:" & q & sScrFile & q
   ' The download contains the API functions, including ShellWait.
   ShellWait sExe, vbHide
   DoEvents
   ' ///////////////////////////////////////////////////////
Exit_Here:
   Exit Function
Err_Handler:
   MsgBox Err.Description, vbExclamation, "E R R O R"
   Resume Exit_Here
End Function

Open in new window

Avatar of Jim P.
Jim P.
Flag of United States of America image

This should be one line, not two.
   ' (If it exists from previous run, delete it)
   sScrFile =   sLocalFLD  & "download.scr"
   If Dir(sScrFile) <>  "" Then Kill sScrFile

Open in new window

Avatar of marellano
marellano

ASKER

jimpen. Thanks, that part worked. However, now I'm getting another compile error. "Sub of Function not defined" on the following line:

' The download contains the API functions, including ShellWait.
   ShellWait
That is additional code in the API Function Module.
Public Sub ShellWait(Pathname As String, Optional WindowStyle As Long)
On Error GoTo Err_Handler
 
   Dim proc As PROCESS_INFORMATION
   Dim start As STARTUPINFO
   Dim ret As Long
   
   ' Initialize the STARTUPINFO structure:
   With start
      .cb = Len(start)
      If Not IsMissing(WindowStyle) Then
         .dwFlags = STARTF_USESHOWWINDOW
         .wShowWindow = WindowStyle
      End If
   End With
   ' Start the shelled application:
   ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
   ' Wait for the shelled application to finish:
   ret& = WaitForSingleObject(proc.hProcess, INFINITE)
   ret& = CloseHandle(proc.hProcess)
 
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "E R R O R"
   Resume Exit_Here
    
End Sub

Open in new window

ok, I read the article more carefully and downloaded the functions.  I added the API, FTP and Startup Functions as given to my DB and was able to compile.

I also added the dowload.scr on C:\Access DemoDBJ as instructed.

Can you please instruct on what I have to do next.  Do I run the modules in a RunCode macro??
Breaking out the routine

DownloadFTPFile(
ByVal sFile As String, _  <-- File name to download
sSVR As String, _           <--  FTP Server
sFLD As String, _           <--  Local directory
sUID As String, _           <--  logon user id
sPWD As String) As String   <--  logon password

You can do it from a button on a form that has the does something like


Sub ON_MyButton_Click
 
DownloadFTPFile Me.FilenameBox.Value, Me.FTPSvrBox.Value, Me.LocalDirBox.Value, _
      Me.UIDBox.Value, Me.PWDBox.Value
 
End sub

Open in new window

The sample download.scr file has the following FTP parameters
open ftp.amazecreations.com
DBJUser
start123
cd DBJ
binary
lcd "C:Access DemoDBJ"
get "JustForKicks.txt" "C:Access DemoDBJJustForKicks.txt"
bye

If i were to be using the sample parameters, would the routin look exactly as follow??

DownloadFTPFile(
ByVal sFile As String, "JustForKicks.txt"  <--  file name will change daily, but ok for now
sSVR As String, "ftp.amazecreations.com"
sFLD As String, "C:Access DemoDBJ"
sUID As String, "DBJUser"
sPWD As String) As String, "start123"   <--  is this right?

=====
It would look something like below. You substitute your values for the options.
DownloadFTPFile "JustForKicks.txt" ,"ftp.amazecreations.com",
"C:\Access DemoDBJ", "DBJUser", "start123"

Open in new window

for testing, i ran the routin in a macro with RunCode where enter the custom fuction:

DownloadFTPFile («sFile», «sSVR», «sFLD», «sUID», «sPWD»)  

After I entered the parameters as follow:
DownloadFTPFile («"JustForKicks.txt" ,"ftp.amazecreations.com", "C:\Access DemoDBJ", "DBJUser", "start123")

I then get the following error: "bad file name or number"

=======
going on break...will be back.
=======

BTW, i will provide more points for this solution.  Thx!!
The only time you need the parentheses is if it returning a value.
----------------------------------------------------
VerifyRun = DownloadFTPFile ("blah")
----------------------------------------------------

In this case it doesn't, so that isn't an issue.

Plus you had left the "«" in from the expression builder.

Try it like below.
DownloadFTPFile "JustForKicks.txt" ,"ftp.amazecreations.com", "C:\Access DemoDBJ", "DBJUser", "start123"

Open in new window

hmmm. I'm still getting the "bad file name or number" error.

I'm testing the code using both the form and macro option.  

Any chance you'd be able to check what I have in the attached db so far (Access 2007) and fix where I'm going wrong?

Not sure if it's require with the current code, but I also have the download.scr in the following directory:
C:\Access DemoDBJ\download.scr

Hi jimpen...are you still interested in helping with this problem solving? fyi, I had increased the points to 500.  
My issue is that I don't have access to Access 07. But if you want to post an example here, I'm sure I can get some other experts to take a look as well.
I understand.  Well, I looked up "bad filename or number" under microsoft:
http://msdn.microsoft.com/en-us/library/b9y457ct%28VS.80%29.aspx

======
A statement refers to a file with a file name or number that was not specified in the FileOpen statement or that was specified in a FileOpen statement but was subsequently closed.

A statement refers to a file with a number that is out of the range of file numbers.

A statement refers to a file name or number that is not valid
=======
Not sure where to make adjustments on the vb code

Avatar of Gustav Brock
> I'm still getting the "bad file name or number" error.

That is very precise so you will have to both correct the code and feed the correct parameters to the function:

  sLocalFLD = CurrentProject.Path & "\" & sFLD

where sFLD must be a folder name only. Or:

  sLocalFLD = sFLD

where sFLD should be a full path like C:\Access DemoDBJ

/gustav
thanks gustav.

I'm a bit confused. On the FTP_Function the author notes in his example that  sFLD = "dbj", which is the folder on the "remote" server as follow:
' /////////////////////////////////////////////////////////////
' Sample login to DataFast FTP site
' ------------------------------------------------------------
'    sSVR = "ftp.amazecreations.com"
'    sFLD = "dbj"
'    sUSR = "dbjUser"
'    sPWD = "start123"
' ------------------------------------------------------------
' /////////////////////////////////////////////////////////////

QUESTION 1:
So assuming that my local folder is "C:\Access DemoDBJ, where the "download.scr" resides" and the FTP remote folder is "dbj", how do I exactly edit the following portion of the FTP_Fuctions module:
=======================
   On Error GoTo Err_Handler
   DoCmd.Hourglass True
   sLocalFLD = CurrentProject.Path & "\" & sFLD

   ' will break if empty folder exist so error to pass
   ' must create folder first, so API calls work
   On Error Resume Next
   If Dir(sLocalFLD & "\") = "" Then MkDir (sLocalFLD)
   On Error GoTo Err_Handler

   sScrFile = sLocalFLD & "\download.scr"
   If Dir(sScrFile) <> "" Then Kill sScrFile
   sTarget = q & sLocalFLD & "\" & sFile & q
   sFile = q & sFile & q
===================

QUESTION 2:
The vb code then continues with the following FTP script:
====================
   ' Open a new text file to hold the FTP script and load it with
   ' the appropriate commands.  (Thanks Dev Ashish !!!)
   iFile = FreeFile
   Open sScrFile For Output As iFile
   Print #iFile, "open " & sSVR
   Print #iFile, sUID
   Print #iFile, sPWD
   Print #iFile, "cd " & sFLD
   Print #iFile, "binary"
   Print #iFile, "lcd " & q & sLocalFLD & q
   Print #iFile, "get " & sFile & " " & sTarget
   Print #iFile, "bye"
   Close #iFile
====================
the download.scr file that resides in my local "C:\Access DemoDBJ" folder also has the commands "open", "cd", "binary", "lcd", "get", "bye".  I'm just wondering if this redundancy conflicts with the Print #File commands above and should they be removed from the download.scr file or from the script above.  

The download.scr contains exatly the following:
open ftp.amazecreations.com
DBJUser
start123
cd DBJ
binary
lcd "C:Access DemoDBJ"
get "JustForKicks.txt"
bye
=====

Hope you can help.
This is not that easy as MkDir only creates a folder one level deep.

As for #2, I'm not sure what the problem is, except that this line should read:

lcd "C:\Access DemoDBJ"

/gustav


   On Error GoTo Err_Handler
   DoCmd.Hourglass True
   sLocalFLD = sFLD
 
   ' will break if empty folder exist so error to pass
   ' must create folder first, so API calls work
   On Error Resume Next
   If Dir(sLocalFLD) = "" Then MkDir sLocalFLD
   On Error GoTo Err_Handler
 
   sScrFile = sLocalFLD & "\download.scr"
   If Dir(sScrFile) <> "" Then Kill sScrFile
   sTarget = q & sLocalFLD & "\" & sFile & q
   sFile = q & sFile & q 

Open in new window

ok. So are you suggesting to leave the code as listed above?
ok...i just tried it with the above script and it WORKED!!
gustav, now that you were able to verify that this script works, I have another question:

Is it possible to alter the script to download any file that it sees in the ftp folder? the open "C:\Access DemoDBJ\download.scr is set to get a specific filename:

======
ftp.amazecreations.com
DBJUser
start123
cd DBJ
binary
lcd "C:Access DemoDBJ"
get "JustForKicks.txt"
bye
=========
The reason I require this is becuase the file I need to download will be named different on the daily basis.
Glad you can help!
Yes, use the command mget for get.
However, it prompts for each file found and a y (or yes) has to be passed unless you pass the command prompt first:

binary
prompt
mget "*.txt"
bye

/gustav
Great!!
In addition to the editing the download.scr file with the "mget", would I do the same editing on the vb portion of the script below?

Finally, how exactly can I add the command prompt to (yes) so that it accepts the file automatically?

Really appreciate your help.  This should finalize the project.

   iFile = FreeFile
   Open sScrFile For Output As iFile
   Print #iFile, "open " & sSVR
   Print #iFile, sUID
   Print #iFile, sPWD
   Print #iFile, "cd " & sFLD
   Print #iFile, "binary"
   Print #iFile, "lcd " & q & sLocalFLD & q
   Print #iFile, "get " & sFile & " " & sTarget <--change to mget???
   Print #iFile, "bye"
   Close #iFile

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for your help gustav!
You are welcome!

/gustav