2 more for the Road

[stanl]

Since xlGibbs just posted that this forum was excellent {and I agree} and could be Extended <---- keyword

I have additional LogParser Snippets to (1) create Access Table via ADOX/LogParser to capture directory files (2) Using LogParser to turn CSV into Gif (with optional windows display).

The current Logparser forum is offline {I think they will be coming out with a Vista/64bit version} but it remains a great SQL tool.

One of my first interest in XML was to create structures to contain structures and having examined LogParsers COM extensibility {from the .wsc standpoint, not .NET} it appears you can output .wsc as Ascii files and re-employ as distinct COM Objects to perform a variety of Logpar' sing ["if you catch my meaning... if you get my drift"]

....sorry... couldn't resist the FireSign Theatre reference

Stan

[stanl]

Let's look at csv2gif first... Logparser integrates the chart object (whether Excel is installed or just the free OWC) so you can easily create/display them based on .csv files. The code is more adaptable to an external script, but below are basics in a sub you can place in Excel (csv is in the zip).

Note To self: at first the "*.*" in the sql query string asked if my glass was half empty or half full... these LogParser guys!!!!!

 
Sub csv2gif()
    cCSV = ActiveWorkbook.Path & "\carinv.csv"
    cGIF = ActiveWorkbook.Path & "\carinv.gif"
    If Dir(cCSV) = "" Then Exit Sub
    If Dir(cGIF) <> "" Then Kill (cGIF)
    cSQL = "SELECT Make, count(Make) AS Makes into " & cGIF & " FROM " & cCSV & "*.* group by Make order by Make ASC"
    MsgBox cSQL
    Set oLog = CreateObject("MSUtil.LogQuery")
    Set oInput = CreateObject("MSUtil.LogQuery.CSVInputFormat")
    oInput.headerRow = 1
    Set oOut = CreateObject("MSUtil.LogQuery.ChartOutputFormat.1")
    oOut.ChartTitle = "Break out By Car Make"
    oOut.ChartType = "ColumnStacked3D" 'see end of sub for available Chart Types
    oOut.View = 1 'this displays chart in window after creation
    oLog.ExecuteBatch cSQL, oInput, oOut
    Set oOut = Nothing
    Set oInput = Nothing
    Set oLog = Nothing
End Sub

'chart types
'Line [default]
'LineMarkers
'LineStacked
'LineStackedMarkers
'LineStacked100
'LineStacked100Markers
'Line3D
'LineOverlapped3D
'LineStacked3D
'LineStacked1003D
'SmoothLine
'SmoothLineMarkers
'SmoothLineStacked
'SmoothLineStackedMarkers
'SmoothLineStacked100
'SmoothLineStacked100Markers
'BarClustered
'BarStacked
'BarStacked100
'Bar3D
'BarClustered3D
'BarStacked3D
'BarStacked1003D
'ColumnClustered
'ColumnStacked
'ColumnStacked100
'Column3D
'ColumnClustered3D
'ColumnStacked3D
'ColumnStacked1003D
'Pie
'PieExploded
'PieStacked
'Pie3D
'PieExploded3D
'ScatterMarkers
'ScatterSmoothLine
'ScatterSmoothLineMarkers
'ScatterLine
'ScatterLineMarkers
'ScatterLineFilled
'Bubble
'BubbleLine
'Area
'AreaStacked
'AreaStacked100
'Area3D
'AreaOverlapped3D
'AreaStacked3D
'AreaStacked1003D
'Doughnut
'DoughnutExploded
'RadarLine
'RadarLineMarkers
'RadarLineFilled
'RadarSmoothLine
'RadarSmoothLineMarkers
'StockHLC
'StockOHLC
'PolarMarkers
'PolarLine
'PolarLineMarkers
'PolarSmoothLine
'PolarSmoothLineMarkers

[stanl]

I know what you must be thinking by now... dumb old stanl... same 10-14 lines, just does something different each time

So, here [using about the same 10-14 lines] is an unusual use of Logparser - performing a recursive directory search and placing the data into an Access Table.

For demonstration purposes I also used ADOX to create a new Access DB, as logparser with create the table. The SQL looks for all .xls files on C:, and took a few minutes on my machine; you can change that to *.ppt, *.doc... whatever... Stan

 
Sub createtable()
    'set additional reference to ADOX
    cTable = "ExcelFiles"
    cSQL = "SELECT Path, Size, CreationTime INTO " & cTable & " FROM C:\*.xls ORDER BY CreationTime DESC"
    cMDB = ActiveWorkbook.Path & "\xl.mdb"
    If Dir(cMDB) <> "" Then Kill (cMDB)
    'quickie to create Access database
    cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=" & cMDB
    Set oCat = CreateObject("ADOX.Catalog")
    oCat.Create cConn
    Set oCat = Nothing
    Set oLog = CreateObject("MSUtil.LogQuery")
    Set oInput = CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
    oInput.recurse = -1 'total recursion to all subdirectories
    Set oOut = CreateObject("MSUtil.LogQuery.SQLOutputFormat.1")
    'NOTE: the Jet 4.0 Provider was used to create the MDB, but use the ODBC driver to create the table
    oOut.oConnString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & cMDB
    oOut.createtable = 1 'documentation says values are ON|OFF, but use 1|0
    oLog.ExecuteBatch cSQL, oInput, oOut
    Set oOut = Nothing
    Set oInput = Nothing
    Set oLog = Nothing
End Sub