Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with simple find and copy macro for Word

Status
Not open for further replies.

Xeeq

Technical User
Oct 12, 2011
27
US
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.


Code:
<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.

Code:
GenericTxDReport.aspx?PageMode=Daily&amp;ScenarioId=55&amp;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:
Code:
RigName=Top+Secret&amp;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.

 
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.
 
You could use something like this:
Code:
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.

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

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

 
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
Code:
United+States&scen
and then select the cell that it is in.

The selected cell will look something like this:
Code:
<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
Code:
 "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.
 
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]
 
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,

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top