10 replies to this topic

[j.smith1981]

I have a problem.

I have a macro that I have developed for a customer by where it processes a csv file in Visual Basic for Applications in Excel (VBA).

It then uploads.

The system they use is xcart, which requires the use of a php page to upload the products.

I want to then get a programming language to open a browser window, and then use the import feature of x cart to process the upload of products.

Whats the best language for this thats the quickest?

Already the macro takes about 5-10minutes to process so ideally just being as quick as possible to upload their products would be wonderfull.

Any help is wonderfully appreciated,
Jeremy.

[j.smith1981]

I have the following code at present:

Public Sub ftpUpload()


Set fs = CreateObject("Scripting.FileSystemObject")

Set FTPScript = fs.CreateTextFile("C:\ftp.txt", True)

With FTPScript

    .WRITELINE ("OPEN ftp.shopping.***.co.uk")

    .WRITELINE ("jeremy") '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 "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 = "username" '' replace with username

ie.Document.all("password").Value = "password" 'replace with password

'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 "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

This isnt the most robust of programming routines in VBA, as everytime it calls the 'ProcessWebUpload' routine it keeps looping the FTP Upload, where the cmd comes up and keeps looping the command it parses into it.

Can someone help me in writing some more robust code please?

[WingedPanther]

You have a lot that you haven't said. However, it looks like you will have to use ActiveX.

[j.smith1981]

Yea its a bit of a pain to be honest, but trying to make this as robust as possible.

How would I go about calling an Active X from VBA though?

Also are there (probably going to be loads), but to save me sometime, could you reference some tutorials to allowing me to do this with Active X?

Would be a wonderfull help if you could.

Thanks for the pointer though

[WingedPanther]

I don't know anything about ActiveX, beyond the fact that it's evil and can do what you're talking about. I was envisioning the ActiveX being part of xcart, but I suppose that may not be an option.

[j.smith1981]

Unfortunately no it doesnt have that capability.

Really we shouldnt be using X Cart for what we require out of it but we are making it work for us where I am employed as it where so it has to work really, I know very frustrating eh?

I would have been able to make a reasonable import system myself if it was made by a company, but due to certain restrictions I am stuck with annoying X Cart, plus I now cant stand the templating system Smarty its just too slow really.

[WingedPanther]

Do you have the ability to directly access the back-end database? This sounds like the sort of thing where I would push the data to the database, rather than mess with a web app.

If that's not an option, I'd see about reading the data using something like AutoIt and using that to enter the form data.

[j.smith1981]

Dont take this the wrong way, dont mean any offence by this comment but.

I would end up with the same situation if I was to do it with Autoit, thats another form of a language I originally used but was way too slow for what I wanted, so autoit's not an option really unfortunately.

Why do it in another language when in theory it could work when keeping it all contained within VBA?

[WingedPanther]

Which brings me back to: do you have direct access to the back end database?

[j.smith1981]

Ah yes sorry sincere apologies.

I probably have it sorted.

Will explain the problems we are having doing that, there's a rediculous number of fields we have to select to get this import to even work efficiently, or as efficiently as possible so going by the back end probably wouldnt be the easiest way, its something I am personally going to have a look at.

The likelihood of when I change the settings in the existing import script within X Cart, is way over complex than it needs to be, having multiple steps to import a single CSV, like when they could have just had one, you have my permission of course to slap the people there lol.

But no thanks for your help so far much appreciated, but (there's alwats a but isnt there?) I think I have it sorted, I login, navigate using the Web Browser Controls from within VBA.

That then yea logs in to the store's admin section, navigates to the import processing page, enters some details and changes some, it then kicks off the processing and hangs for about 30minutes, this giving more than enough time to fully process the file, it then reads a bit of text (by div tag id's) and see's their result if anything other than successfully imported comes up, it then emails me, it actually emails me at the moment even when its worked, just so I can keep an eye on it.

The only way I can see this working, of course it will take 30minutes for me to work out if there was an error the last time it attempted an import but I am open to suggestions anyone?

Thanks,
Jeremy.

[debtboy]

By using shell() in VBA, you have no feedback within the program,
hence those ridiculous wait loops.

The preferred method would be to use System.Diagnostic.Process
through VB.NET or C#.NET giving you full control over "your" process.

If your hell bent on using VBA then you still have a few options.
You could break up your program into multiple small programs and
have each shell() out to a batch file which runs the external program
then starts your next program.
This will eliminate the wait loops, but you will probably have to pass
multiple parameters between your programs and the batch files making
this solution a bit of a pain (pita).

A more preferred VBA solution would be to again shell() out to batch file
that runs your local pogram and also creates a new log file based on the
success or failure upon completion. You could even use your own
naming convention (indicating success or failure) for the log file so you
wouldn't need to read the file.
While this is going on your VBA application would be looping the Dir or dhDir
command to detect the existence and name of this log file.
This will basically eliminate excessive predetermined time delays.
Once those delays are eliminated, you can then look at optimizing your code
if necessary.

Hope this helps :)