Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Copy and paste from excel to another program
#1
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
#2
Macro Macro2119
Code:
Copy      Help
;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
#3
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")
#4
Replace the ExcelSheet line to:
Code:
Copy      Help
run "C:\Users\jgarnett\Documents\VBA Test.xls"
wait 30 WA "VBA Test"
1
ExcelSheet es.Init
#5
This code says "too many parts"
#6
In which line?
#7
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
#8
Here is the first macro with ExcelSheet line replaced:
Macro Macro2121
Code:
Copy      Help
;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
#9
This still says it cannot open the file. I don't know what to do.
#10
Nevermind, I got the file to open. I'm working on the next part. I might have more questions. I'm sorry again!
#11
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?
#12
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?
#13
You can record mouse clicks.
#14
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!
#15
example
Macro Macro2191
Code:
Copy      Help
;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]
#16
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
#17
ok so here is the macro I have came up with thanks to you! Smile

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
#18
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")
#19
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?
#20
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


Forum Jump:


Users browsing this thread: 1 Guest(s)