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!
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
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
' 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
ASKER
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??
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
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
ASKER
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?
=====
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"
ASKER
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!!
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.
--------------------------
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"
ASKER
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
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
ASKER
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.
ASKER
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
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
> 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
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
ASKER
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.
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
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
ASKER
ok. So are you suggesting to leave the code as listed above?
ASKER
ok...i just tried it with the above script and it WORKED!!
ASKER
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!
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
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
ASKER
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help gustav!
You are welcome!
/gustav
/gustav
Open in new window