ExcelBanter - little help

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - little help (https://www.excelbanter.com/excel-programming/444172-little-help.html)

Hi all,
I modified posted code so I can put login and pass into
www site but I have no idea how to click submit button....
If anyone could help I'll be grateful :)

Public Sub test()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "http://www.yg1.co.kr/europe/"
Do Until Not .Busy
DoEvents
Loop
Set ipf = ie.Document.All.Item("id")
ipf.Value = "xxx"
Set ipf = ie.Document.All.Item("pwd")
ipf.Value = "xxx"
Set ipf = ie.document.all.Item("submit") '????
ipf.Value = "submit" '????
ipf.Click
' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop
End With
' ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
' ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
ie.ExecWB 12, 2
ie.ExecWB 12, 0
' ie.Quit

End Sub

On Jan 26, 9:27*am, bluburter wrote:
Hi all,
I modified posted code so I can put login and pass into
www site but I have no idea how to click submit button....
If anyone could help I'll be grateful :)

Public Sub test()
* * Set ie = CreateObject("InternetExplorer.Application")
* * With ie
* * * * .Visible = True
* * * * .Navigate "http://www.yg1.co.kr/europe/"
* * * * *Do Until Not .Busy
* * * * * * *DoEvents
* * * * *Loop
* * * * Set ipf = ie.Document.All.Item("id")
* * * * * * ipf.Value = "xxx"
* * * * Set ipf = ie.Document.All.Item("pwd")
* * * * * * ipf.Value = "xxx"
* * * * Set ipf = ie.document.all.Item("submit") * '????
* * * * * * ipf.Value = "submit" * * * * * * * * * * * * * '????
* * * * * * ipf.Click
' Loop until the page is fully loaded
* * * * Do Until Not .Busy
* * * * * * DoEvents
* * * * Loop
* * End With
* *' ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
* *' ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
* * ie.ExecWB 12, 2
* * ie.ExecWB 12, 0
* *' ie.Quit

End Sub

The "submit" button is an image and it is the only image on the page.
Instead of
Set ipf = ie.document.all.Item("submit") '????
ipf.Value = "submit" '????
ipf.Click

try
ie.document.images(0).Click

....Ron

Thany you very much, Ron, it is working :)

Dear all,
one more question:

I'm putting value as "MATNR" - it's easy, but how to accept
my choice? This time I can't use image as a object to click...

Thanks in advance for any help

Source of the page:
<title::: Y:::</title
<meta http-equiv="Content-Type" content="text/html; charset=euc-kr"
<meta name="robots" content="noindex, nofollow"
<META NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW"
<META NAME="GOOGLEBOT" CONTENT="NOINDEX, NOFOLLOW"
<META NAME="GOOGLEBOT" CONTENT="NOSNIPPET"
<META NAME="ROBOTS" CONTENT="NOARCHIVE"
<META NAME="GOOGLEBOT" CONTENT="NOARCHIVE"
<link rel="stylesheet" href="../include/style.css" type="text/css"
<body topmargin="0" leftmargin="0"
<table border="0" cellspacing="0" cellpadding="0"
<tr
<td<img src="title.gif" align="absmiddle"</td
</tr

<tr
<td height="20"</td
</tr
<tr
<td align="center"
<table width="708" height="39" background="tab_bg.gif" border="0"
cellspacing="0" cellpadding="0"
<form name="frm" method="post"
<tr
<td width="215" style="padding-left:20px;"
<font style="color:178577;font-weight:bold;"* EDP No. : </
font<input type="text" name="MATNR" size="20"

</td
<td style="padding-left:10px;"
<img src="query.gif" onclick="frmSUB()" style="cursor:hand;"
align="absmiddle"
</td
</tr
</form
</table
</td
</tr

<tr
<td height="20"</td
</tr
<tr
<td align="center"
<table width="708" border="0" cellspacing="0" cellpadding="0"
<tr
<td
<iframe src="erp_list.asp" name="mainfrm" width='708' height='600'
border='0' scrolling=yes frameborder='0' id='mainfrm'</iframe
</td

</tr
</table
</td
</tr
<script
function frmSUB()
{
if(frm.MATNR.value=="")
{
alert("error");
frm.MATNR.focus();
return false;
}
frm.target = "mainfrm";
frm.action = "erp_list.asp"
Layer_ON();
frm.submit();
}
function Layer_ON()
{
document.getElementById("LoadingDIV").style.displa y = "block";
}

function Layer_OFF()
{
document.getElementById("LoadingDIV").style.displa y = "none";
}
</script
</script
</table
<div id="LoadingDIV" style="position:absolute;top:250;left:
120;display:none"
<table width="500" height="350"
<tr
<td align="center" style="line-height:35px;color: #4C4C4C;font-
size: 26px;font-weight:bold;"Loading...</td

</tr
</table
</div

On Jan 26, 11:43*am, bluburter wrote:
Dear all,
one more question:

I'm putting value as "MATNR" - it's easy, but how to accept
my choice? This time I can't use image as a object to click...

Thanks in advance for any help

Source of the page:
<title::: Y:::</title
<meta http-equiv="Content-Type" content="text/html; charset=euc-kr"
<meta name="robots" content="noindex, nofollow"
<META NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW"
<META NAME="GOOGLEBOT" CONTENT="NOINDEX, NOFOLLOW"
<META NAME="GOOGLEBOT" CONTENT="NOSNIPPET"
<META NAME="ROBOTS" CONTENT="NOARCHIVE"
<META NAME="GOOGLEBOT" CONTENT="NOARCHIVE"
<link rel="stylesheet" href="../include/style.css" type="text/css"
<body topmargin="0" leftmargin="0"
<table border="0" cellspacing="0" cellpadding="0"
<tr
* * * * <td<img src="title.gif" align="absmiddle"</td
</tr

<tr
* * * * <td height="20"</td
</tr
<tr
* * * * <td align="center"
* * * * * * * * <table width="708" height="39" background="tab_bg.gif" border="0"
cellspacing="0" cellpadding="0"
* * * * * * * * <form name="frm" method="post"
* * * * * * * * * * * * <tr
* * * * * * * * * * * * * * * * <td width="215" style="padding-left:20px;"
* * * * * * * * * * * * * * * * * * * * <font style="color:178577;font-weight:bold;"* EDP No. : </
font<input type="text" name="MATNR" size="20"

* * * * * * * * * * * * * * * * </td
* * * * * * * * * * * * * * * * <td style="padding-left:10px;"
* * * * * * * * * * * * * * * * * * * * <img src="query.gif" onclick="frmSUB()" style="cursor:hand;"
align="absmiddle"
* * * * * * * * * * * * * * * * </td
* * * * * * * * * * * * </tr
* * * * * * * * </form
* * * * * * * * </table
* * * * </td
</tr

<tr
* * * * <td height="20"</td
</tr
<tr
* * * * <td align="center"
* * * * * * * * <table width="708" *border="0" cellspacing="0" cellpadding="0"
* * * * * * * * * * * * <tr
* * * * * * * * * * * * * * * * <td
<iframe src="erp_list.asp" name="mainfrm" width='708' height='600'
border='0' scrolling=yes *frameborder='0' id='mainfrm'</iframe
* * * * * * * * * * * * * * * * </td

* * * * * * * * * * * * </tr
* * * * * * * * </table
* * * * </td
</tr
<script
function frmSUB()
{
* * * * if(frm.MATNR.value=="")
* * * * {
* * * * * * * * alert("error");
* * * * * * * * frm.MATNR.focus();
* * * * * * * * return false;
* * * * }
* * * * frm.target = "mainfrm";
* * * * frm.action = "erp_list.asp"
* * * * Layer_ON();
* * * * frm.submit();}

function Layer_ON()
{
* * * * document.getElementById("LoadingDIV").style.displa y = "block";

}

function Layer_OFF()
{
* * * * document.getElementById("LoadingDIV").style.displa y = "none";}

</script
</script
</table
<div id="LoadingDIV" style="position:absolute;top:250;left:
120;display:none"
* * * * <table width="500" height="350"
* * * * * * * * <tr
* * * * * * * * * * * * <td align="center" style="line-height:35px;color: #4C4C4C;font-
size: 26px;font-weight:bold;"Loading...</td

* * * * * * * * </tr
* * * * </table
</div

I'm not sure I'm following, but it sounds like you are entering the
term "MATNR" into a text box on the web page and now you want to click
some button and submit your choice. It's hard to analyze without
having the actual web page to play with, but based on the source code
you provided perhaps the following will work

For Each i In IE.document.images
If i.src Like "*query.gif" Then i.Click
Next i

....Ron

Login process works fine, also searching process works fine.
My goal is to copy result into excel.
For example (I'm searching for item EM81100)
I get result on page:
NEUTRAL EM811100 X-POWER 4FL SHORT E/M 10X10X22X70 240 PC

YG EM811100 X-POWER 4FL SHORT E/M 10X10X22X70 91 PC

I want to get this data (two text lines) into excel like this:
NEUTRAL EM811100 240 PC
YG EM811100 91 PC

Thanks in advance for help!!! :)

Here is my VBA macro code:

Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Public Sub test()

Dim intCount As Integer
Dim itm As Variant
Dim SHELL_OBJECT
Dim SubmitInput As HTMLInputElement
Const MyUrl As String = "http://www.yg1.co.kr/europe/"

SHELL_OBJECT = "WScript.Shell"

' Prepare to open the web page
Set objShell = CreateObject(SHELL_OBJECT)
Set IE = CreateObject("InternetExplorer.Application")

With IE
.Visible = True
.Navigate MyUrl

Do Until Not .Busy
DoEvents
Loop
Sleep (1000)

Set ipf = IE.document.all.Item("id")
ipf.Value = "ygeurope"
Set ipf = IE.document.all.Item("pwd")
ipf.Value = "eu1980"
IE.document.images(0).Click

Do Until Not .Busy
DoEvents
Loop

Set ipf = IE.document.all.Item("MATNR")
ipf.Value = Range("A1").Value
Sleep (1000)
IE.document.images(1).Click
Sleep (3000)

End With

x = IE.document.DocumentElement.innerHTML
x = Replace(x, Chr(10), Chr(13))
x = Split(x, Chr(13))
Range("A2").Resize(UBound(x)) = Application.Transpose(x)

End Sub

bluburter has brought this to us :
Login process works fine, also searching process works fine.
My goal is to copy result into excel.
For example (I'm searching for item EM81100)
I get result on page:
NEUTRAL EM811100 X-POWER 4FL SHORT E/M 10X10X22X70 240 PC

YG EM811100 X-POWER 4FL SHORT E/M 10X10X22X70 91 PC

I want to get this data (two text lines) into excel like this:
NEUTRAL EM811100 240 PC
YG EM811100 91 PC

Thanks in advance for help!!! :)

Here is my VBA macro code:

Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Public Sub test()

Dim intCount As Integer
Dim itm As Variant
Dim SHELL_OBJECT
Dim SubmitInput As HTMLInputElement
Const MyUrl As String = "http://www.yg1.co.kr/europe/"

SHELL_OBJECT = "WScript.Shell"

' Prepare to open the web page
Set objShell = CreateObject(SHELL_OBJECT)
Set IE = CreateObject("InternetExplorer.Application")

With IE
.Visible = True
.Navigate MyUrl

Do Until Not .Busy
DoEvents
Loop
Sleep (1000)

Set ipf = IE.document.all.Item("id")
ipf.Value = "ygeurope"
Set ipf = IE.document.all.Item("pwd")
ipf.Value = "eu1980"
IE.document.images(0).Click

Do Until Not .Busy
DoEvents
Loop

Set ipf = IE.document.all.Item("MATNR")
ipf.Value = Range("A1").Value
Sleep (1000)
IE.document.images(1).Click
Sleep (3000)

End With

x = IE.document.DocumentElement.innerHTML
x = Replace(x, Chr(10), Chr(13))
x = Split(x, Chr(13))
Range("A2").Resize(UBound(x)) = Application.Transpose(x)

End Sub

Are you missing the last line? Unless you have specified Option Base 1,
the resize for range("a2") should be: .Resize(UBound(x) + 1) if you
want 2 rows of data because the UBound is 1 if the array contains 2
elements <ergo x(0), x(1)!!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Now, as a result I get into excel source code of web site...
I don't know why but there in no "table code" that contains my
two lines of requested result...

Any help will be appreciate.

On Jan 26, 3:33*pm, bluburter wrote:
Now, as a result I get into excel source code of web site...
I don't know why but there in no "table code" that contains my
two lines of requested result...

Any help will be appreciate.

Yes, I don't understand either. When you right-click the web page and
select "view source" you see all of the information in the table, but
when you use constructions like

x = IE.document.DocumentElement.innerHTML

you no longer see all of the code. In any case, using

ie.ExecWB 17, 2
ie.ExecWB 12, 0

would let you select the information of interest and paste it into
Excel. Then you could process it and just retain the info you
want...Ron

bluburter wrote :
Now, as a result I get into excel source code of web site...
I don't know why but there in no "table code" that contains my
two lines of requested result...

Any help will be appreciate.

Sorry but my experience with getting data from a website is limited to
reading data files and NOT web pages.

I was just pointing out that default UBound for VB[A} arrays is 0
(zero) unless you specify otherwise as I suggested. So dumping an array
into cells as attempted requires adding 1 to the UBound to resize the
target range to match the contents of the array.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I tried ie.execwb also tried sendkeys ^a ^c but still
I can't paste content of site into excel.
I'm getting worry if it is possible... with such a specific site.

Kris

On Jan 26, 11:31*pm, bluburter wrote:
I tried ie.execwb also tried sendkeys ^a ^c but still
I can't paste content of site into excel.
I'm getting worry if it is possible... with such a specific site.

Kris

The code I posted above

ie.ExecWB 17, 2
ie.ExecWB 12, 0

works for me on the web page we're discussing...Ron

On 27 Sty, 15:22, ron wrote:
The code I posted above

* * ie.ExecWB 17, 2
* * ie.ExecWB 12, 0

works for me on the web page we're discussing...Ron

Hi Ron,

I tried to use such a code:

ie.ExecWB 17, 2
ie.ExecWB 12, 0
Range("E3").Select
ActiveSheet.PasteSpecial Format:="Tekst", link:=False, DisplayAsIcon:=
_
False

as a result I got in excel only:
"EDP.No:"

"Loading...."

Coud you please let me know what is your excel ver ?
I don't have any idear why it does not work for me....

Kris

On Jan 27, 11:41*am, bluburter wrote:
On 27 Sty, 15:22, ron wrote:

The code I posted above

* * ie.ExecWB 17, 2
* * ie.ExecWB 12, 0

works for me on the web page we're discussing...Ron

Hi Ron,

I tried to use such a code:

ie.ExecWB 17, 2
ie.ExecWB 12, 0
Range("E3").Select
ActiveSheet.PasteSpecial Format:="Tekst", link:=False, DisplayAsIcon:=
_
* * False

as a result I got in excel only:
"EDP.No:"

"Loading...."

Coud you please let me know what is your excel ver ?
I don't have any idear why it does not work for me....

Kris

I looked at the web page again and noticed that the information of
interest is in a frame called "mainfrm". Once you put the focus on
this frame by manually clicking in that area of the webpage, then you
can capture the source code with all of the information that is of
interest by using

zz = ie.document.frames("mainfrm").document.DocumentEle ment.innerhtml

I haven't figured out how to programmatically select the frame...Ron

Hi Ron,
I did the trick with mouse click (mouse event),
that solves my problem.
Thanks for help

Kris

On Jan 28, 9:28*am, bluburter wrote:
Hi Ron,
I did the trick with mouse click (mouse event),
that solves my problem.
Thanks for help

Kris

I played with it a little more and here is what I found. At least for
me, the mouse event wasn't necessary. I just needed to give the page
a bit more time to load. Now the following code

ie.Document.images(1).Click
End With
Do Until Not ie.Busy
DoEvents
Loop

zz = ie.Document.frames("mainfrm").Document.DocumentEle ment.innerhtml
pos_1 = InStr(1, zz, "EM811100", vbTextCompare)

puts the desired source code into zz. Now the items your looking for
can be extracted from zz using the instr and mid functions...Ron

HI Ron,

I did as you suggested- result is super.
It works perfectly.

Thanks a lot for help.

Kris