PDA

View Full Version : FTP Upload Advice



j.smith1981
09-14-2009, 06:59 AM
Hello its me again,

I am wanting to make VBA in Excel, upload a CSV file I have generated called:

'UPLOADproducts.csv'

To a web server.

What it then does is use the IE com library or web control sorry to then process the upload, to allow for the updating of our products on our web store.

Now at the moment the below code is a problem:

Public Sub ftpUpload()

Set fs = CreateObject("Scripting.FileSystemObject")
Set FTPScript = fs.CreateTextFile("C:\ftp.txt", True)
With FTPScript
.WRITELINE ("OPEN ftp.***.domain.com")
.WRITELINE ("***") 'user account FTP user!
.WRITELINE ("**") 'user password (ftp account!)
.WRITELINE ("ASCII") 'Sets upload file type to use in FTP console ftp.exe
.WRITELINE ("CD ../files/") 'Remote FTP server !!ONLY COMMENTED WHEN LOCALHOST TEST!!
'REMOVE FILES
.WRITELINE ("delete C:\") 'On remote
' .WRITELINE ("delete C:\UPLOADcategories.csv") 'On remote
'UPLOAD FILES
.WRITELINE ("PUT C:\UPLOADproducts.csv") 'On local computer
' .WRITELINE ("PUT C:\UPLOADcategories.csv") 'On local computer
.WRITELINE ("CLOSE") 'Closes FTP connection
.WRITELINE ("QUIT") 'Quits terminal
End With

Set FTPScript = fs.CreateTextFile("C:\ftp.bat", True)
With FTPScript
.WRITELINE ("ftp -s:C:\ftp.txt")
.WRITELINE ("EXIT")
End With

Shell ("C:\WINDOWS\system32\cmd.exe /k C:\ftp.bat")

'Wait 5 seconds for it to complete!
'Sets up a wait time for application to completely load
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10 'sets app to wait 2 seconds beyond the current time
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Call ProcessWebUpload

End Sub
Sub ProcessWebUpload()

Dim ie As Object

'Sets ie as the object reference
Set ie = CreateObject("internetexplorer.application")

'Makes Internet Explorer visible
ie.Visible = False ' Make sure its worked!

'Sets up a wait time for application to completely load
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2 'sets app to wait 2 seconds beyond the current time
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime



'Navigates to the login page
ie.navigate "http://shopping.****.co.uk/admin/home.php"

'Sets up a wait time for application to completely load
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2 'sets app to wait 2 seconds beyond the current time
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'Wait for page to load completely!
While ie.Busy
DoEvents 'wait until IE is done loading page.
Wend

'Load information into fields
ie.Document.all("username").Value = "****"
ie.Document.all("password").Value = "****"
'Submit login
ie.Document.Links(1).Click


'Wait for page to load completely!
While ie.Busy
DoEvents 'wait until IE is done loading page.
Wend


'Navigate to upload page
ie.navigate "http://shopping.*****.co.uk/admin/import.php"

'Wait for page to load completely!
While ie.Busy
DoEvents 'wait until IE is done loading page.
Wend

'Put another wait in here for it to load definately
'Sets up a wait time
newHour1 = Hour(Now())
newMinute1 = Minute(Now())
newSecond1 = Second(Now()) + 3 'sets app to wait 2 seconds beyond the current time
waitTime1 = TimeSerial(newHour1, newMinute1, newSecond1)
Application.Wait waitTime1

'Enter upload location things
ie.Document.all("delimiter").Value = ","
'Makes sure server is definately select
ie.Document.all("source_upload").Value = "checked"
ie.Document.all("localfile").Value = "/var/www/html/xcart/files/UPLOADproducts.csv"

'Put another wait in here for it to load definately
'Sets up a wait time
newHour1 = Hour(Now())
newMinute1 = Minute(Now())
newSecond1 = Second(Now()) + 1 'sets app to wait 2 seconds beyond the current time
waitTime1 = TimeSerial(newHour1, newMinute1, newSecond1)
Application.Wait waitTime1

'Submit form for processing
ie.Document.all("submit").Click

End Sub

What its doing (the above code), is its looping the command:

'C:\WINDOWS\system32\cmd.exe /k C:\ftp.bat' in the cmd prompt.

Any suggestions as to why this is happening please? Also any advice on a way of stopping this from happening.

It for some reason sometimes works and sometimes it loops and wont update our websites products, obviously this isnt very robust, so any advice is greatly appreciated.

Jeremy.