Copying data from webpage and pasting it in an Excel sheet

[brunop]

Hi guys im hoping for a bit of help here. Im still quite new to this so go easy on me.

Im trying to do a script that logs onto my Neteller account (an online payment system like Paypal) and extract my current balance and then place it correctly in an spreadsheet I have made in Excel.

I have made a few simple scripts before but nothing like this so im really hoping for some pointers!

I have no problem getting the script to go to the right page and type in the login details but then the "fun" begins.

In the Neteller interface in the upper right corner it says "balance" followed by the actual balance. So my first real issue would be to copy the amount. Im thinking of getting the script to look for the "balance" text and then somehow get it to copy the amount next to it, but how?

After this it should be quite easy to get the script to open the right speadsheet in Excel but how im going to get the script to paste the amount in the right field is also a bit tricky for me. My initial thought would be to look for my name in the spreadsheet and then just past it x pixels to the right from there but im guessing there is a better way than that.


So a quick recap of my needs for help:

- How do I get the script to copy a specific amount from a webpage?
- How do I get the script to paste the specific amount in the right place in a spreadsheet?
(alternativly i could live with the script just adding the findings to a list in a txt file everytime i run the script)

Thank you in advance guys!

Bruno

PS. i just discovered the "DDEPoke" function but by using that I will have to know in advance what cell in Excel I would want to post my data to right?

[adroege]

These should get you started in the right direction.


Automate Internet Explorer with OLE/ActiveX
http://www.mjtnet.com/usergroup/viewtopic.php?t=1511

Automate web forms with IE
http://www.mjtnet.com/usergroup/viewtopic.php?t=1461

Automate Excel with VBScript
http://www.mjtnet.com/usergroup/viewtopic.php?t=1470

[brunop]

Great im already getting further. Im using webrecorder to go to the correct page and log in. Then im using the tag extraction wizard to mark/get the text that I want to copy - in my scipt it looks like this

//Modify buffer size if required ...
Let>SPAN12_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,12,0,SPAN12,r
MidStr>r_6,1,r,SPAN12

Now my question is, how do I copy the text that the tag editor has found?
I thought it automatically would have copied the selected text so that I could do something like below to past the data to a new notepad document


ExecuteFile>C:\Windows\System32\notepad.exe,
wait>2
Press CTRL
press v
Release CTRL

But nothing happens even if I try to paste myself after the script ends. There has been nothing copied to memory.

So my question is, how do I get the script to actually copy/paste the part of the webpage that the tag extraction wizard has found for me so that I can add it to the notepad



Bruno

[adroege]

MidStr>r_6,1,r,SPAN12

This command puts the text into a variable called SPAN12. Once data is in a variable you can do lots of things with it. You could write it to a file directly with WriteLn> Or you could display it with MessageModal> Or you could simulate typing it into another field with Send> , or when you finish the tutorial on how to control Excel with Macro Scheduler, you can enter the data directly into a cell in a spreadsheet.

Putting the data on the clipboard is easy too, just do something like this:
PutClipBoard>SPAN12

[brunop]

Great just what I needed, thanks Adroege

I have now been playing around a bit with the excel script Marcus have made for entering data into excel (found here http://www.mjtnet.com/forum/viewtopic.php?t=1470) and that works fine but Im a bit lost on how to get the "SPAN12" data put into the excel sheet.
Using the below script as is results in "This is the column A, row 1" being added to the sheet. How do I replace "This is the column A, row 1" with the "SPAN12" data? Obviously I cannot just type in "SPAN12" in the script where it now says "This is the column A, row 1" because all that happens is the script then just types SPAN12 rather than the actual data contained in SPAN12.

So how do I tell the script to use the data it just got called SPAN12?

Code: Select all

VBSTART
Sub ExcelExample
Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
xlSheet.Cells(1,1).Value = "This is the column A, row 1"
'etc
xlSheet.SaveAs "C:\files\mysheet.xls"
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample

My own script ends with:

Code: Select all

//Modify buffer size if required ...
Let>SPAN12_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,12,0,SPAN12,r
MidStr>r_6,1,r,SPAN12

PutClipBoard>SPAN12

[adroege]

One method would be to add parameters to the VBScript subroutine ExcelExample like so:

Code: Select all

  Sub ExcelExample (myString)

Then inside the VBScript
Change
xlSheet.Cells(1,1).Value = "This is the column A, row 1"
To
xlSheet.Cells(1,1).Value = myString

And then change
VBRun>ExcelExample
To
VBRun>ExcelExample,SPAN12

* See the example from the on-line help file for the command VBRun *

Obviously you need to structure the code pieces so that SPAN12 *has* a value by this point.

[brunop]

Iv been playing around a bit with the codes you gave me and I finally got it to work! Im seeing real progress!

I really appreciate you taking the time to help me! This will be a real timesaver when I get it to work!

The script ended up looking like this:

Code: Select all

VBSTART
Sub ExcelExample (mystring)
Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
xlSheet.Cells(1,2).Value = mystring
'etc
xlSheet.SaveAs "C:\mysheet1.xlsx"
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample,SPAN12

There are still a few of the lines in the script I have no clue what is doing, but as long as it works...

After all this success I decided that I would also like to have the script to get the name of the account Im accessing next to the balance the script gets from the "SPAN12" value.

I have used the tag extraction wizard again and found the new value is called SPAN8.

So what I did was first having the script grab the SPAN12 value as usual and then below inserted the code to grab the SPAN8 code as well.

Then I copied and reinserted the above VBScript and replaced the SPAN12 value with SPAN8 and removed the below lines from the SPAN12 VBScript since there is no need to save and close excel just yet.

Code: Select all

xlSheet.SaveAs "C:\mysheet1.xlsx"
xlApp.Quit

My issue is that when I run the script now it opens a new worksheet and inserts the balance via the SPAN12 value as it should but when it comes to inserting the SPAN8 data it opens another instance of excel and inserts the SPAN8 data there instead of adding to the already open sheet that contains the SPAN12 data.
(I of course set the SPAN8 data to be inserted in another cell in Excel)

I tried removing the line below thinking it were the reason the script opens a new version of excel, but that did not go well the line cannot be removed so I guess it has to be changed in some way.

Code: Select all

'Set xlApp = CreateObject("Excel.Application")

I hope you can give me a few more pointers towards solving these issues.


BTW

How do I make the script open a specific file rather than just a new empty template. Im guessing its in the "Set xlSheet = xlBook.Worksheets(1)" line I have to do the changes to make it understand it should open and insert data to eg. c:\testsheet.xlsx rather than the default sheet.


Once again thank you for your patients and great help!

Bruno

[adroege]

Please post the *entire* script -- not just sections here and there. The code needs to work as a cohesive whole.... generally sections can't just be added thinking they are isolated "objects".

[brunop]

Sure here it is

Code: Select all

MouseMove>0,0
Let>delay=1
IE_Create>0,IE[0]

// go to website
IE_Navigate>%IE[0]%,http://neteller.com/,r
IE_Wait>%IE[0]%,r
Wait>delay


// inserts secure id
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:secureId"}
Let>FieldValue={"THIS IS SECRET"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r

// inserts password
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:password"}
Let>FieldValue={"THIS IS SECRET"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r


// push submit
Let>FrameName={""}
Let>FormName={"signInform"}
Let>TagValue={"signInform:submit"}
IE_ClickTag>%IE[0]%,str:FrameName,str:FormName,INPUT,NAME,str:TagValue,r

IE_Wait>%IE[0]%,r
Wait>delay

WaitWindowOpen>money transfer*

wait>3

// retrieve "balance"
//Modify buffer size if required ...
Let>SPAN12_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,12,0,SPAN12,r
MidStr>r_6,1,r,SPAN12


// retrieve "name"
//Modify buffer size if required ...
Let>SPAN8_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,8,0,SPAN8,r
MidStr>r_6,1,r,SPAN8



//inserts SPAN12(balance) in an excelsheet
VBSTART
Sub ExcelExample (mystring)
Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
xlSheet.Cells(1,2).Value = mystring
'etc
'xlSheet.SaveAs "C:\mysheet1.xlsx"
'xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample,SPAN12


//inserts SPAN8(name) in an excelsheet
VBSTART
Sub ExcelExample (mystring)
Dim xlApp
Dim xlBook
Dim xlSheet

'Set xlApp = CreateObject("Excel.Application")
'Set xlBook = xlApp.Workbooks.Add
'Set xlSheet = xlBook.Worksheets(1)

xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
xlSheet.Cells(1,1).Value = mystring
'etc
'xlSheet.SaveAs "C:\mysheet2.xlsx"
'xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample,SPAN8


Label>end_script

[adroege]

Since you want to insert multiple cells into a spreadsheet now instead of just one, I believe you need to add more parameters to the VBScript subroutine Sub ExcelExample (mystring).

There should be only one (1) VBStart/VBEnd block in the script. The subroutine to insert into Excel should be made "general purpose" so that it can be "re-used" for inserting your second cell.

Change
Sub ExcelExample (mystring)
To
Sub ExcelExample (mystring,myrow,mycol)

Change
xlSheet.Cells(1,2).Value = mystring
To
xlSheet.Cells(myrow,mycol).Value = mystring

Change
VBRun>ExcelExample,SPAN12
To
VBRun>ExcelExample,SPAN12,1,2


Change
VBRun>ExcelExample,SPAN8
To
VBRun>ExcelExample,SPAN8,1,1

Remove the duplicated VBSTART/Sub ExcelExample(mystring)/VBEND
block of code. Everything is handled in the ONE (1) subroutine now. Duplicating this code is why you were seeing it open two spreadsheets.

[brunop]

The new script looks like this:

Code: Select all

MouseMove>0,0
Let>delay=1
IE_Create>0,IE[0]

// go to website
IE_Navigate>%IE[0]%,http://neteller.com/,r
IE_Wait>%IE[0]%,r
Wait>delay


// inserts secure id
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:secureId"}
Let>FieldValue={"xxxxx"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r

// inserts password
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:password"}
Let>FieldValue={"xxxx"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r


// push submit
Let>FrameName={""}
Let>FormName={"signInform"}
Let>TagValue={"signInform:submit"}
IE_ClickTag>%IE[0]%,str:FrameName,str:FormName,INPUT,NAME,str:TagValue,r

IE_Wait>%IE[0]%,r
Wait>delay

WaitWindowOpen>money transfer*

wait>3

// retrieve "balance"
//Modify buffer size if required ...
Let>SPAN12_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,12,0,SPAN12,r
MidStr>r_6,1,r,SPAN12


// retrieve name
//Modify buffer size if required ...
Let>SPAN8_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,8,0,SPAN8,r
MidStr>r_6,1,r,SPAN8



//inserts SPAN12(balance) in an excelsheet
VBSTART
Sub ExcelExample (mystring,myrow,mycol)

Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
xlSheet.Cells(myrow,mycol).Value = mystring

'etc
'xlSheet.SaveAs "C:\mysheet1.xlsx"
'xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample,SPAN12,1,2

VBRun>ExcelExample,SPAN8,1,1



Label>end_script

But when im running it i get a VBScript runtime error 1004
unknown runtime error
line 69, column 0

line 69 in the script is empty...

I tried in debug mode and saw it came first when line
"VBRun>ExcelExample,SPAN12,1,2" was run and again when
"VBRun>ExcelExample,SPAN8,1,1" was run

[adroege]

Well it took a little more massaging to get this to function properly when trying to call it more than once. The code sample doesn't include the webstuff.... just insert that back in where appropriate.

Had to Dim the Excel object outside the subroutine so that it was persistent between calls. Also had to provide a way to tell it that we wanted to create a new workbook the first time and not the second, and to only save and quit after the second time.

I'm not really a big Excel guy.... I have to google for help, so not sure exactly why the .Cells property with a row,column wasn't working, so I changed it to a "range" and that worked ok.

[code]
VBSTART
Dim xlApp

Sub CreateExcelObj
Set xlApp = CreateObject("Excel.Application")
End Sub

Sub ExcelExample (mystring,myrange,myAddNew,mySave)

Dim xlBook
Dim xlSheet

If (myAddNew = "True") THEN
Set xlBook = xlApp.Workbooks.Add
Else
Set xlBook = xlApp.ActiveWorkbook
End if

Set xlSheet = xlBook.Worksheets(1)

xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
xlSheet.Range(myrange).Value = mystring

'etc
If (mySave = "True") THEN
xlSheet.SaveAs "c:\mysheet1.xls"
End if
End Sub

Sub QuitExcel
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub

VBEND
VBRun>CreateExcelObj
VBRun>ExcelExample,SPAN12,A1,True,False

VBRun>ExcelExample,SPAN8,A2,False,True
VBRun>QuitExcel


Label>end_script

[/code]

[brunop]

Hi again. I have testet the new script and its working flawlessly

I am really happy that you are taking the time to help me get going. I can see from the new script data you created that I would have been in way over my head on this one.

I do have a few minor questions more

If I want to add more data to the excel document later on, eg. my girlfriends data, how do I get excel to open the same sheet (mysheet2.xls) rather than just a new empty sheet?

Would I be able to copy/paste the existing script and just change my login details with her details and place the data in other cells in the excel sheet and then have the script run both our logins one after another and then have both our data in the same excel sheet, or will there be issues with running 2 VBScrips in one "regular" script - you mentioned earlier (There should be only one (1) VBStart/VBEnd block in the script.)


Thank you !

Bruno

[adroege]

To open an existing workbook, just change the " Set xLBook =" line to this:

Set xLBook = xlApp.Workbooks.Open("c:\my folder\my workbook.xls")

Note: This will be tricky in practice if you want to distinguish between all three options of 1.) Create a new worksheet 2.) Use an existing in-memory worksheet and 3.) Load a worksheet from a disk file


You can run multiple instances of the web stuff - Just don't duplicate the VBScript blocks -- similar to the following:

[code]

//Create Excel Object only ONCE!
VBRun>CreateExcelObj


//Get Web fields for user 1
// code goes here
//
// Now save the fields to spreadsheet
VBRun>ExcelExample,SPAN12,A1,True,False
VBRun>ExcelExample,SPAN8,A2,False,True

//Get Web fields for user 2
// code goes here
//
// Note 3rd parameter is False this time since we don't want to
// create a new worksheet
// Now save the fields to spreadsheet
VBRun>ExcelExample,SPAN12,C1,False,False
VBRun>ExcelExample,SPAN8,C2,False,True

// only quit Excel when you are completely done
VBRun>QuitExcel


Label>end_script

[/code]

You may find this link helpful also in the future:

http://www.vbforums.com/showthread.php?t=391665

[brunop]

Hi again

I have really tried to get this to work but after a few days I must once again ask for help.

I have tried to add your suggestions but with no luck. My script is pasted in below, this is when I try to get info from 2 accounts to insert into Excel.

I have basically just inserted the code to get the web info in the BVScript.
But after I started putting my "get info from web" code into the VBScript I keep getting errors as soon as the script runs. I have tried many variations but the one below gets compilation error 1024 in line 43, line 43 is empty and just after the "end sub" and before "//Get Web fields for user 1"

Code: Select all

VBSTART
Dim xlApp

Sub CreateExcelObj
  Set xlApp = CreateObject("Excel.Application")
End Sub




Sub ExcelExample (mystring,myrange,myAddNew,mySave)

  Dim xlBook
  Dim xlSheet

  If (myAddNew = "True") THEN
    Set xLBook = xlApp.Workbooks.Open("c:\neteller saldo1.xlsx")
  Else
    Set xlBook = xlApp.ActiveWorkbook
  End if

  Set xlSheet = xlBook.Worksheets(1)

  xlApp.visible = True
  'optionally make the sheet visible
  'xlSheet.Application.Visible = True
  xlSheet.Range(myrange).Value = mystring

  'etc
  'If (mySave = "True") THEN
   ' xlSheet.SaveAs "c:\neteller saldo.xlsx"
  'End if
End Sub

//Get Web fields for user 1
//     code goes here
//Move the mouse cursor out of harm's way to avoid causing mouseover events to interrupt
MouseMove>0,0
Let>delay=1
IE_Create>0,IE[0]

// go to Neteller webpage
IE_Navigate>%IE[0]%,http://neteller.com/,r
IE_Wait>%IE[0]%,r
Wait>delay


// enter secure id
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:secureId"}
Let>FieldValue={"xxx"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r

// enter password
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:password"}
Let>FieldValue={"xxx"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r


// press submit
Let>FrameName={""}
Let>FormName={"signInform"}
Let>TagValue={"signInform:submit"}
IE_ClickTag>%IE[0]%,str:FrameName,str:FormName,INPUT,NAME,str:TagValue,r

IE_Wait>%IE[0]%,r
Wait>delay

WaitWindowOpen>money transfer*

wait>3

// get balance
//Modify buffer size if required ...
Let>SPAN12_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,12,0,SPAN12,r
MidStr>r_6,1,r,SPAN12


// get name
//Modify buffer size if required ...
Let>SPAN8_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,8,0,SPAN8,r
MidStr>r_6,1,r,SPAN8

// Closes the Neteller window
CloseWindow>Money Transfer & Online Payment | NETELLER - Client Account - Windows Internet Explorer



//
// Now save the fields to spreadsheet
VBRun>ExcelExample,SPAN12,A1,True,False
VBRun>ExcelExample,SPAN8,A2,False,True




//Get Web fields for user 2
//     code goes here

//Move the mouse cursor out of harm's way to avoid causing mouseover events to interrupt
MouseMove>0,0
Let>delay=1
IE_Create>0,IE[0]

// go to website
IE_Navigate>%IE[0]%,http://neteller.com/,r
IE_Wait>%IE[0]%,r
Wait>delay


// enter secure
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:secureId"}
Let>FieldValue={"xxx"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r

// enter password
Let>FrameName={""}
Let>FormName={"signInform"}
Let>FieldName={"signInform:password"}
Let>FieldValue={"xxx"}
IE_FormFill>%IE[0]%,str:FrameName,str:FormName,str:FieldName,str:FieldValue,0,r


// press submit
Let>FrameName={""}
Let>FormName={"signInform"}
Let>TagValue={"signInform:submit"}
IE_ClickTag>%IE[0]%,str:FrameName,str:FormName,INPUT,NAME,str:TagValue,r

IE_Wait>%IE[0]%,r
Wait>delay

WaitWindowOpen>money transfer*

wait>3

// get balance
//Modify buffer size if required ...
Let>SPAN12_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,12,0,SPAN12,r
MidStr>r_6,1,r,SPAN12


// get name
//Modify buffer size if required ...
Let>SPAN8_SIZE=4098
IE_ExtractTag>%IE[0]%,,SPAN,8,0,SPAN8,r
MidStr>r_6,1,r,SPAN8

// Closes the Neteller window
CloseWindow>Money Transfer & Online Payment | NETELLER - Client Account - Windows Internet Explorer




Sub QuitExcel
  xlApp.Quit

  Set xlSheet = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing
End Sub

VBEND
VBRun>CreateExcelObj
VBRun>ExcelExample,SPAN12,C1,False,False

VBRun>ExcelExample,SPAN8,C2,False,True
//VBRun>QuitExcel


Label>end_script

*I really hope you can give me a few pointers in the right direction as im having difficulties understanding how the VB part of scripting works.

Kindly

Bruno