PDA

View Full Version : VBA LogParser and Yahoo RSS feeds


stanl
07-05-2007, 10:33 AM
The attached workbook permits selecting a Yahoo RSS topic, then clicking a Command Button which displays key fields from the feed in the default LogParser dataGrid. Logparser 2.2 is a free download and probably the most versatile product around for the price. In order to run the code, you must set a reference to logparser.dll.

Using the datagrid as output is for purposes of illustration, but a curious thing, it only works once and you have to close and re-open the workbook for it to work again. My gut feeling is that the objects are not actually released. Any insights appreciated. Stan
malik641
07-05-2007, 08:25 PM
It only stopped working after I changed the Topic :think:

I wish I could view the Object's methods, functions, properties, etc in the Object Explorer...

Is there any type of Close method or the like? Where can I find a programming reference for this object?
malik641
07-05-2007, 08:31 PM
Hmmm...even more interesting now. If I close the Parser using the "X" in the top right corner it will fail after changing the "topic" (cURL). But if I use "Close" on the bottom left, it works every time :think:

You're probably right. It's probably not closing correctly when the "X" button is pressed.
malik641
07-05-2007, 08:50 PM
I wish I could view the Object's methods, functions, properties, etc in the Object Explorer...
Scratch that. I just noticed the documentation on COM API for Log Parser 2.2.
stanl
07-06-2007, 03:13 AM
There is also this

LogParser user manual in PDF: http://www.vogtland.ws/Sharepoint%20Reference%20Library/LogParser%202.2%20User%20Guide.pdf (http://www.vogtland.ws/Sharepoint%20Reference%20Library/LogParser%202.2%20User%20Guide.pdf) it is 570 pages, and very thorough

Thanks for looking at it. I realize there are easier ways to obtain RSS. Stan:friends:
stanl
07-06-2007, 06:38 AM
Oh, if you are interested, I changed the output format


Sub lp()
'set reference to logparser [c:\Program Files\LogParse 2.2\logparser.dll]
Dim cURL As String
f = ActiveWorkbook.Path & "\lp.htm"
Set oLog = CreateObject("MSUtil.LogQuery")
cURL = Range("A4").Value
Set oInput = CreateObject("MSUtil.LogQuery.XMLInputFormat.1")
Set oOutput = CreateObject("MSUtil.LogQuery.NativeOutputFormat.1")
oOutput.filemode = 1
oInput.fMode = "Tree"
oInput.fNames = "XPath"
cSQL = "SELECT /rss/channel/item/title AS XX,/rss/channel/item/pubDate AS YY,/rss/channel/item/description AS ZZ "
cSQL = cSQL & "INTO " & f & " FROM " & cURL
oLog.ExecuteBatch cSQL, oInput, oOutput
Set oInput = Nothing
Set oOutput = Nothing
Set oLog = Nothing
End Sub



Then with a few replacements in the output file, it can be displayed in an MSHTML: control (small sample attached)
malik641
07-06-2007, 09:55 AM
Great. I'll have a look at it tonight. :thumb

I keep getting a feeling that there's a problem with Log Parser 2.2 itself, rather than your code. That is, unless you explicitly have to close certain connections that Log Parser 2.2 creates (maybe that's why they have that separate close button).
stanl
07-06-2007, 10:49 AM
Joseph;

this by-passes all that. LogParser has an output component where you can specify a template file to format what is being parsed. Create a file named rss.tpl [in notepad] and copy this in


<LPHEADER>
<HTML>
<BODY>
</LPHEADER>
<LPBODY>
%XX%
%YY%
%ZZ%
</LPBODY>
<LPFOOTER>
</BODY>
</HTML>
</LPFOOTER>


then use this for your sub


Sub lp()
'set reference to logparser [c:\Program Files\LogParse 2.2\logparser.dll]
Dim cURL As String
f = ActiveWorkbook.Path & "\lp.htm"
f1 = ActiveWorkbook.Path & "\rss.tpl"
Set oLog = CreateObject("MSUtil.LogQuery")
cURL = Range("A4").Value
Set oInput = CreateObject("MSUtil.LogQuery.XMLInputFormat.1")
Set oOutput = CreateObject("MSUtil.LogQuery.TemplateOutputFormat.1")
oOutput.tpl = f1
oOutput.filemode = 1
oInput.fMode = "Tree"
oInput.fNames = "XPath"
cSQL = "SELECT /rss/channel/item/title AS XX,/rss/channel/item/pubDate AS YY,/rss/channel/item/description AS ZZ "
cSQL = cSQL & "INTO " & f & " FROM " & cURL
oLog.ExecuteBatch cSQL, oInput, oOutput
Set oInput = Nothing
Set oOutput = Nothing
Set oLog = Nothing
End Sub



After it is run, open lp.htm in your browser. Stan
malik641
07-08-2007, 09:16 AM
Oh, if you are interested, I changed the output format


Sub lp()
'set reference to logparser [c:\Program Files\LogParse 2.2\logparser.dll]
Dim cURL As String
f = ActiveWorkbook.Path & "\lp.htm"
Set oLog = CreateObject("MSUtil.LogQuery")
cURL = Range("A4").Value
Set oInput = CreateObject("MSUtil.LogQuery.XMLInputFormat.1")
Set oOutput = CreateObject("MSUtil.LogQuery.NativeOutputFormat.1")
oOutput.filemode = 1
oInput.fMode = "Tree"
oInput.fNames = "XPath"
cSQL = "SELECT /rss/channel/item/title AS XX,/rss/channel/item/pubDate AS YY,/rss/channel/item/description AS ZZ "
cSQL = cSQL & "INTO " & f & " FROM " & cURL
oLog.ExecuteBatch cSQL, oInput, oOutput
Set oInput = Nothing
Set oOutput = Nothing
Set oLog = Nothing
End Sub


Then with a few replacements in the output file, it can be displayed in an MSHTML: control (small sample attached) I'm getting an error with this one:


Run-time error '-2147023281 (8007064f)'

Error parsing query: Syntax Error: <from-clause>: exprecting FROM keyword instead of token 'Office'[SQL query syntax invalid or unsupported.]
The error highlights:
oLog.ExecuteBatch cSQL, oInput, oOutPut
I believe it's because I don't know what "lp.htm" is supposed to have. I don't know what that file's supposed to hold. I tried just creating a blank htm file, but had no luck.

I'm going to check out post #8 right now.
malik641
07-08-2007, 09:22 AM
Same error with post #8. I'm guessing I'm missing the correct lp.htm file.
stanl
07-09-2007, 03:12 AM
Same error with post #8. I'm guessing I'm missing the correct lp.htm file.

Dunno... the attached works fine for me. The lp.htm is created/overwritten each time you run the code. The rss.tpl is the template LogParser uses to format output [in this case it merely encloses the rss feeds with HTML and BODY tags]. You could add code to open an MSHTML Object and display inside of Excel instead of opening the file in your browser. Just ensure rss.tpl is in the same subdir as the .xls

Stan
stanl
07-09-2007, 06:36 AM
Finally, if the workbook I uploaded does work, this will display the selected topic in a browser as part of the sub


Sub lp()
'set reference to logparser [c:\Program Files\LogParse 2.2\logparser.dll]
'also set references to Microsoft HTML Object, Microsoft Internet Controls
Dim IExp As SHDocVw.InternetExplorer
Dim cURL As String
f = ActiveWorkbook.Path & "\lp.htm"
f1 = ActiveWorkbook.Path & "\rss.tpl"
Set oLog = CreateObject("MSUtil.LogQuery")
cURL = Range("A4").Value
Set oInput = CreateObject("MSUtil.LogQuery.XMLInputFormat.1")
Set oOutput = CreateObject("MSUtil.LogQuery.TemplateOutputFormat.1")
oOutput.tpl = f1
oOutput.filemode = 1
oInput.fMode = "Tree"
oInput.fNames = "XPath"
cSQL = "SELECT /rss/channel/item/title AS XX,/rss/channel/item/pubDate AS YY,/rss/channel/item/description AS ZZ "
cSQL = cSQL & "INTO " & f & " FROM " & cURL
oLog.ExecuteBatch cSQL, oInput, oOutput
Set oInput = Nothing
Set oOutput = Nothing
Set oLog = Nothing
Set IExp = New SHDocVw.InternetExplorer
IExp.Visible = True
IExp.navigate f

Do Until IExp.Busy = False
DoEvents
Loop

End Sub



Stan