Posts: 14
Threads: 2
Joined: Nov 2013
Hi,
I am a newbie and trying to learn all the coding. I would like to do the following:
1. open excel
2. copy data from one cell
3. open internet explorer
4. navigate to website and enter login and password
5. navigate to form and paste data
6. copy more cells from excel and paste into form
7. close both programs.
Any help will be greatly appreciated!
Jame
Posts: 12,239
Threads: 144
Joined: Dec 2002
Macro Macro2119
;1. Open file in Excel
ExcelSheet es.Init("" 8 "$documents$\Book1.xls")
;2. Get cell
str cell=es.Cell("A1")
;3,4. Open web page in Internet Explorer
int hwndIE
web "http://www..." 1 "" "" 0 hwndIE
;4. Password
AutoPassword "user" "password" 1|4 hwndIE 10
;5. Navigate to form
web "http://www..." 1 hwndIE
;5. Paste data
paste cell ;;before this probably will need to add some code to set focus to the form field
;6. Get/paste more cells
cell=es.Cell("A2")
paste cell ;;before this probably will need to add some code to set focus to the form field
;7. Close programs
clo hwndIE
es.Close
Posts: 14
Threads: 2
Joined: Nov 2013
I tried opening the excel file with this code and it says it cannot open the file.
ExcelSheet es.Init("" 8 "C:\Users\jgarnett\Documents\VBA Test.xls")
Posts: 12,239
Threads: 144
Joined: Dec 2002
Replace the ExcelSheet line to:
run "C:\Users\jgarnett\Documents\VBA Test.xls"
wait 30 WA "VBA Test"
1
ExcelSheet es.Init
Posts: 14
Threads: 2
Joined: Nov 2013
This code says "too many parts"
Posts: 12,239
Threads: 144
Joined: Dec 2002
Posts: 14
Threads: 2
Joined: Nov 2013
When I just try to highlight the code and run it:
"C:\Users\jgarnett\Documents\VBA Test.xls"
wait 30 WA "VBA Test"
1
ExcelSheet es.Init
Posts: 12,239
Threads: 144
Joined: Dec 2002
Here is the first macro with ExcelSheet line replaced:
Macro Macro2121
;1. Open file in Excel
run "C:\Users\jgarnett\Documents\VBA Test.xls"
int hwndExcel=wait(30 WA "VBA Test")
1
ExcelSheet es.Init
;2. Get cell
str cell=es.Cell("A1")
;3,4. Open web page in Internet Explorer
int hwndIE
web "http://www..." 1 "" "" 0 hwndIE
;4. Password
AutoPassword "user" "password" 1|4 hwndIE 10
;5. Navigate to form
web "http://www..." 1 hwndIE
;5. Paste data
paste cell ;;before this probably will need to add some code to set focus to the form field
;6. Get/paste more cells
cell=es.Cell("A2")
paste cell ;;before this probably will need to add some code to set focus to the form field
;7. Close programs
clo hwndIE
clo hwndExcel
Posts: 14
Threads: 2
Joined: Nov 2013
This still says it cannot open the file. I don't know what to do.
Posts: 14
Threads: 2
Joined: Nov 2013
Nevermind, I got the file to open. I'm working on the next part. I might have more questions. I'm sorry again!
Posts: 14
Threads: 2
Joined: Nov 2013
ok, so when I get to internet explorer and login. I then need to use mouse clicks to navigate to the form. Can you help me?
Posts: 14
Threads: 2
Joined: Nov 2013
ok, so when I get to internet explorer and login. I then need to use mouse clicks to navigate to the form. Can you help me?
Posts: 12,239
Threads: 144
Joined: Dec 2002
You can record mouse clicks.
Posts: 14
Threads: 2
Joined: Nov 2013
How do you record mouse clicks? I'm using for ex.
wait 10
mou 1990 990
lef
wait 2
mou 1970 265
lef
wait 5
mou 3640 155
lef
wait 2
I'm using this to navigate to where I want to be and then pasting data but then I need to do this same process about 100 times. Not sure how to duplicate and move to the next row on the excel file. Any help would be great!
Posts: 12,239
Threads: 144
Joined: Dec 2002
example
Macro Macro2191
;connect to Excel
ExcelSheet es.Init
;get columns B, C and D from Excel. Use other string if need other range. For reference, click CellsToArray and press F1.
ARRAY(str) a
es.CellsToArray(a "B:D")
;repeat for each row
int r
for r 0 a.len
,;show message box that allows to continue macro (Yes), or skip current row (No), or end macro (Cancel)
,sel mes(F"Navigate and paste this value?[][]{a[0 r]}" "" "YNC")
,,case 'N' continue
,,case 'C' ret
,
,;navigate
,mou 1990 990
,lef
,wait 2
,mou 1970 265
,lef
,wait 5
,mou 3640 155
,lef
,wait 2
,
,;paste
,paste a[0 r]
,;;...
,;;example pasting more columns of the specified range
,;paste a[1 r]
,;...
,;paste a[2 r]
Posts: 14
Threads: 2
Joined: Nov 2013
I still don't understand how to repeat with that formula. Here is what I want.
1. Open file in Excel
run "C:\Users\jgarnett\Documents\VBA Test.xlsx"
int hwndExcel=wait(30 WA "VBA Test")
1
ExcelSheet es.Init
2. Get cell
str cell=es.Cell("A2")
3,4. Open web page in Internet Explorer
int hwndIE
web "http://www.exi.com" 1 "" "" 0 hwndIE
4. Password
AutoPassword "jgarnett" "12345" 1|4 hwndIE 10
5. Navigate to form
wait 5
mou 1990 990
lef
wait 2
mou 1970 265
lef
wait 5
mou 3640 155
lef
wait 2
paste cell
wait 2
mou 2925 585
lef
wait 2
mou 2825 700
lef
wait 2
mou 2965 560
lef
wait 5
mou 1410 930
lef
wait 2
mou 2102 490
lef
wait 2
mou 3790 160
lef
wait 2
mou 820 540
lef
wait 2
paste cell
cell=es.Cell("d2")
wait 2
mou 865 620
lef
wait 1
paste cell
wait 2
mou 1155 830
lef
wait 2
;6. go back to search and repeat but next row until end of worksheet
mou 1970 265
lef
wait 5
mou 3640 155
lef
wait 2
cell=es.Cell("a3")
paste cell
wait 2
mou 2925 585
lef
wait 2
mou 2825 700
lef
wait 2
mou 2965 560
lef
wait 5
mou 1410 930
lef
wait 2
mou 2102 490
lef
wait 2
mou 3790 160
lef
wait 2
mou 820 540
lef
wait 2
paste cell
cell=es.Cell("d3")
wait 2
mou 865 620
lef
wait 1
paste cell
wait 2
mou 1155 830
lef
wait 2
;7. Close programs
clo hwndIE
clo hwndExcel
Posts: 14
Threads: 2
Joined: Nov 2013
ok so here is the macro I have came up with thanks to you! 
The only thing I'm working on is if the macro stops because of any reason which row did it leave off on.
1. Open file in Excel
run "C:\Users\jgarnett\Documents\December 2013 Returns.xlsb"
int hwndExcel=wait(30 WA "December 2013 Returns")
1
ExcelSheet es.Init
2. Get array
ARRAY(str) a
es.CellsToArray(a "C:T")
3,4. Open web page in Internet Explorer
int hwndIE
web "http://www.exi.com" 1 "" "" 0 hwndIE
4. Password
AutoPassword "jgarnett" "12345" 1|4 hwndIE 10
5. Navigate to form
wait 5
mou 1990 990
lef
wait 2
int r
for r 0 a.len
mou 1970 265
lef
wait 5
search
mou 3640 155
lef
wait 2
paste a[0 r]
key T
key "o"
key Y
wait 2
clo
wait 2
account history
mou 2102 490
lef
lef
wait 5
New Inquiry
mou 3790 160
lef
wait 2
key T
key T
key T
key T
wait 2
paste a[0 r]
wait 1
key T
wait 1
paste a[17 r]
wait 2
key T
key D
wait 1
save inquiry
mou 1155 830
lef
wait 2
clo hwndIE
clo hwndExcel
Posts: 12,239
Threads: 144
Joined: Dec 2002
Replace
es.CellsToArray(a "C:T")
to
mes "In Excel select range of cells to use with this macro, then click OK to continue."
es.CellsToArray(a "sel")
Posts: 14
Threads: 2
Joined: Nov 2013
This only gave me the dialog box and then continued with the macro. It did not tell me which row the macro failed on. I would just like a message saying "Order ID paste[0 r] has failed." Is this possible?
Posts: 14
Threads: 2
Joined: Nov 2013
There is only one error that could happen:
The orderID could be wrong so it would not open a child window before where the clo is below. So I would need an error message to pop up telling me that the orderID is not valid and paste the order ID it just looked up in the error message.
for r 0 a.len
mou 1970 265
lef
wait 5
search
mou 3640 155
lef
wait 2
paste a[0 r]
key T
key "o"
key Y
wait 2
clo
wait 2
account history
mou 2102 490
lef
lef
wait 5
New Inquiry
mou 3790 160
lef
wait 2
key T
key T
key T
key T
wait 2
paste a[0 r]
wait 1
key T
wait 1
paste a[17 r]
wait 2
key T
key D
wait 1
save inquiry
mou 1155 830
lef
wait 2
clo hwndIE
clo hwndExcel
|
