Need help with simple find and copy macro for Word

[Xeeq]

I need to write a macro in Word to find and copy some text. The text that I need to find is a URL in the source code from my company's website. The problem is that the URL changes everyday because it includes a time range. I found that in the source code, just two lines below the URL that I need, is the rigname in a format that should never change. Here is an example of the code that I will be working with.

<TD><IMG border=0 alt="Daily Report" src="images/daily_report.png" width=16 height=16>&nbsp;<A href="GenericTxDReport.aspx?PageMode=Daily&amp;ScenarioId=55&amp;UtcTimeRange=1332133200000-1332219600000"><SMALL>Daily Report</SMALL></A>&nbsp;&nbsp;</TD>

<TD><IMG border=0 alt=Archive src="images/archive.png" width=16 height=16>&nbsp;<A href="Archive.aspx?RigName=Top+Secret&amp;ScenarioIds = 55,53,41,37"><SMALL>Archive</SMALL></A></TD></TR>

This is what I need to extract from the above code.

GenericTxDReport.aspx?PageMode=Daily&ScenarioId=55&UtcTimeRange=1332133200000-1332219600000

Is there anyway to extract just that portion of the code flawlessly? I would like to stick it into an excel cell, because I have an automation program that really works wonders with excel.

One catch is that the URL changes from day to day (like I said up top), and I need to identify the URL by using the rig name. To kill two birds with one stone, I would like to search for this code:

RigName=Top+Secret&ScenarioId

and then, move the cursor up a couple of lines or something, and copy the URL.

I know this would be a peice of cake to someone out there, but I have struggled with it all day with no avail. Thanks in advance.

 

[Xeeq]

Just after I posted this, i found a way to go straight to excel with this code. I am more experienced with excel VB commands, so I should be able to figure this one out. I do not know how to delete the post, so i guess it will just stay up. Although, if anyone has any suggestions, I will take them.

 

[HarleyQuinn]

You could use something like this:

Public Sub GetWebDetails()

Dim sSrcUrl as String
sSrcUrl = yourURL

Dim MyString as String
MyString = GetHTML(sSrcUrl)

GetRegexDetails(MyString)

End Sub

Function GetHTML(sURL) As String
 ' Create an xmlhttp object:
     With CreateObject("Microsoft.XMLHTTP")
       .Open "GET", sURL, False
       .send
      GetHTML = .responseText
    End With
End Function

Sub GetRegexDetails(startText As String)

Dim m As Variant

With CreateObject("VBScript.Regexp")

    .MultiLine = True
    .Global = True
    .IgnoreCase = True
    .Pattern = "GenericTxDReport.*""|RigName=.*&amp"
    For Each m In .Execute(startText)
        MsgBox m.Value
    Next m

End With

End Sub

It gets the web source and then uses regular expressions to parse it for the values you want. It's just a quick thing, so not polished etc but it could give you a few ideas.

Hope this helps

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[HarleyQuinn]

Ooops! Sorry, hadn't seen you'd posted again in this thread before I replied

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[Xeeq]

Actually, I liked that code you posted. I like the way you think. Problem is, that code willl nto work for me, because the website I am getting the source code from requires a login. I am using another program to login and extract the code to excel. I am still having trouble even though I got the source code to go to excel. I am trying to use the find function to find the first part of the URL then get it to copy all of the URL. This is what I need in detail and with the steps that I am trying to take:

1. I want to tell excel to search all of sheet1 for

United+States&scen

and then select the cell that it is in.

The selected cell will look something like this:

<td><img src="images/daily_report.png" border="0" width="16" height="16" alt="Daily Report">&nbsp;<a href="GenericTxDReport.aspx?PageMode=Daily&ScenarioId=48&UtcTimeRange=1332385200000-1332471600000"><small>Daily Report</small></a>&nbsp;&nbsp;</td><td><img src="images/archive.png" border="0" width="16" height="16" alt="Archive"/>&nbsp;<a href="Archive.aspx?RigName=United+States&ScenarioIds = 48,30"><small>Archive</small></a></td>

(It has to only be on that cell before going to the next step because what I need to search for is in many other cells.)

2. Inside that cell is this

 "GenericTxDReport.aspx?PageMode=Daily&ScenarioId=48&UtcTimeRange=1332385200000-1332471600000"

As you can see there is a "scenario id". There are many other phrases like this thorughout the document that have the same exact things in quotations except have a different "scenario ID".

I need to copy everything that is between the quotation marks and paste it into sheet2.

Hopefully this describes what I need a little better than what I originally posted.

 

[macropod]

If you're wanting to populate an Excel cell, why do you want to use Word? That seems to involve some rather unnecessary circumlocution.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Xeeq]

Macropod,

I guess you didn't see the post where I said "Just after I posted this, i found a way to go straight to excel with this code".

Whenever I was exporting the source code into excel, before, some other software i use would not open it using excel. In order for what i am trying to do to work, my "other software" must be able to open it on it's own. I have now found a way to get it to the state that i need it in so we are now talking about an excel macro and no longer has anything to do with word.

Now that you are caught up. Have any ideas?

 

[macropod]

For code to access a website, input a username & password, see:

http://www.mrexcel.com/forum/showthread.php?t=550986

Cheers
Paul Edstein
[MS MVP - Word]

 

[Xeeq]

Macropod,

I took your code and added the "login" code, tweaked on it a bit, and it worked excellently. I had no knowledge of the Regular Expression object, and i am glad you showed that to me. This was exactly what i needed and now my problem is solved. Thanks for your help.