Mining Data From Sharepoint 2010 Wiki Page 1

[AccessHelp123]

Hi,

We are using Sharepoint 2010 Wiki Pages to enter requirements for our product. As you can guess the requirements are spread across multiple (hundreds) wiki pages. What is an effective way to loop through and mine data from these pages into an excel document? I am only looking to extract certain information from these pages. Can that information be tagged in the pages so I know what to extract? Any ideas or suggestion is greatly appreciated.

Thanks,
Dan.

 

[SkipVought]

Hi,

Check out Data > Import External data > WEB Query.

Navigate to your web page and select the appropriate table using a [highlight]->[/highlight] icon.

Once imported, turn on your macro recorder right-click in the resultset, and select Edit Query and then IMPORT. Turn OFF your macro recorder and post your recorded code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ehaze]

You can also query the List/Library from Access.

Short of some sort of tagging in the wiki post, i'm not sure how you would identify what data to extract.

Keep us posted!

http://www.sharepointed.com/

 

[SkipVought]

Yes, you can from Access, but it is more difficult to discover what the connection and other properties are.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AccessHelp123]

Thanks. I was able to use the web query in excel to import data from a .aspx page to excel. I just need to find out how the information on the web page can be tagged so I can filter for that once imported into excel.

 

[SkipVought]

post your recorded code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AccessHelp123]

Below is the code.

Sub Macro1()

ThisWorkbook.Sheets("Sheet1").Cells.Delete

For Each ch In ActiveWorkbook.Connections
ch.Delete
Next ch

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;

http://mlos421/sites/Dan/Dans Wiki/Find and print.aspx"

_
, Destination:=Range("$A$1"))
.Name = "Find%20and%20print"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """layoutsTable"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub

 

[SkipVought]

You actually recorded ADDING your querytable.

I asked you to...

Once imported, turn on your macro recorder right-click in the resultset, and select Edit Query and then IMPORT. Turn OFF your macro recorder and post your recorded code.

Here is the essence of your query...

   With ActiveSheet.QueryTables(1)
        .Connection = "URL;[URL unfurl="true"]http://mlos421/sites/Dan/Dans%20Wiki/Find%20and%20print.aspx"[/URL]
        .WebSelectionType = xlSpecifiedTables
        .WebTables = """layoutsTable"""
        .Refresh BackgroundQuery:=False
    End With

Now its a hunting expedition.

Using the same macro recorder process as outlined above (DO NOT ADD ANY MORE QUERY TABLES) edit your query and navigate to another wiki page. Look at your code and see what's UNIQUE for THAT page. It might be the Connection property. It might be the WebTables property. Get some representative information and see if you can PREDICT how to get to a particular page.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AccessHelp123]

I tried that. The only difference is the URL. I am actually trying to get particular data (name of the product feature) from each page. What is the best way to tag these words. Users create these Sharepoint Wiki pages and I would have to tell them to tag words a certain way so that when it is imported into Excel I can easily separate these words or phrases. I tested changing the font of these words to 'Italics' in the web page and once it is imported to excel I was able to separate these words but probably need a better way to tag them. Any ideas?

 

[ehaze]

You will need to use some sort of HTML tagging or come up with your own system.

HTML code block:
<code> this text needs to be exported </code>

http://en.wikipedia.org/wiki/HTML_element

Create your own:
[ds] this is my design spec [/ds]

Then you could string out the text using code.

http://www.sharepointed.com/