(Edited by HansV to make URL clickable – see Help 19)
My Access XP application needs to send and receive files from an FTP server each day. I found this code
on a website (thanks to http://www.sqlservercentral.com/columnists…tomatetasks.asp%5B/url%5D) and
attempted to modify it to meet my needs. However the error message:
“Compile Error: Variable Not Defined” appears when I attempt to run it. The variable in question is Wscript on
the line
Set objShell = Wscript.CreateObject(“WScript.Shell”)
I tried to set a reference to Microsoft WMI Scripting but it did not help.
Any ideas what is going on here?
”””””””””””””””””””””””””””””””””””””””””””””’
Sub GetMyFiles()
Dim objFSO, objMyFile, objShell, strFTPScriptFileName, strFile2Get
Dim strLocalFolderName, strFTPServerName, strLoginID
Dim strPassword, strFTPServerFolder
‘Customize code here to fit your needs
strLocalFolderName = “C:temp”
strFTPServerName = “ftp://somesite.net/”
strLoginID = “abcftp”
strPassword = “abc”
strFTPServerFolder = “some_FTP_Folder”
strFile2Get = “TestDownload.txt”
‘The following code generates the file name on the FTP server you want to get
‘strFile2Get = “Data” & strFile2Get & “.csv”
‘The follow lines of code generate the FTP script file on the fly,
‘because the get file name changes every day
strFTPScriptFileName = strLocalFolderName & “FTPScript.txt”
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
If (objFSO.FileExists(strFTPScriptFileName)) Then
objFSO.DeleteFile (strFTPScriptFileName)
End If
Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
objMyFile.WriteLine (“open ” & strFTPServerName)
objMyFile.WriteLine (strLoginID)
objMyFile.WriteLine (strPassword)
objMyFile.WriteLine (“cd ” & strFTPServerFolder)
objMyFile.WriteLine (“ascii”)
objMyFile.WriteLine (“lcd ” & strLocalFolderName)
objMyFile.WriteLine (“get ” & strFile2Get)
objMyFile.WriteLine (“bye”)
objMyFile.Close
Set objFSO = Nothing
Set objMyFile = Nothing
‘The following code executes the FTP script. It creates a Shell
‘object and run FTP program on top of it.
Set objShell = Wscript.CreateObject(“WScript.Shell”) ‘THIS IS THE LINE THAT ALWAYS BOMBS!!!’
objShell.Run (“ftp -s:” & Chr(34) & strFTPScriptFileName & Chr(34))
Set objShell = Nothing
Thanks,
