excel auto full table, papercut

Question

First let explain my scenario.

I had a windows server with PaperCut free edition. It give as output an "papercut-print-log-all-time.csv". That output is always open by the service that every time anyone print it count and document who did it and many other information.

Now, I'm looking to make some type of excel table that every time I open the excel would "automat add all the new register to the new form to make some graphics about usage.

where does the trick is? I wont want that every time I open had to pull down the formula to manually update the new registers.

I guess will need to make some kind of macro (never do before but know c# and vb) but still don't know if with dynamic table and some tricky formula could make that..

An example of the "papercut-print-log-all-time.csv" format is:

Time    User    Pages   Copies  Printer FileName    Client  PaperFormat Lenguaje    Duplex  GrayScale   Format
11/27/2012 11:29    Mberiguette 2   1   Printer1    Microsoft Office Outlook - Memorando    client-pc1  Letter  PCL6    NOT DUPLEX  GRAYSCALE   120kb
11/27/2012 11:30    mabreu  1   1   Printer1    PDF Print   client-pc2  Letter  PCL6    NOT DUPLEX  GRAYSCALE   58kb
11/27/2012 11:30    mabreu  1   1   Printer1    PrintTest   client-pc2  Letter  PCL6    NOT DUPLEX  GRAYSCALE   21kb
11/27/2012 11:35    mabreu  1   1   Printer2    PrintTest   client-pc2  Letter  PCL6    NOT DUPLEX  GRAYSCALE   21kb
11/27/2012 11:35    Mberiguette 1   2   Printer2    Microsoft Word - Entrega factyras de Proveedor.doc  client-pc1  Letter  PCL6    NOT DUPLEX  GRAYSCALE   51kb

thanks for any support.

Answer 1

You can cause a VBA routine to run when a workbook is opened by adding code to the Workbook_Open() routine in the ThisWorkbook Module. (Press Alt-F11 to open the VBA IDE to add code to Modules.)

The code below:

• opens a Scripting.TextStream object to read the CSV file line by line
• the TextStream is advanced to the first line that hasn't been previously written to the spreadsheet
• each subsequent line is read and stored in a String variable
• the String is split into the fields where delimited by commas and the results stored in an Array
• the Array is then copied to the spreadsheet

Option Explicit

Private Sub Workbook_Open()
    Call UpdateFromCSV("your path here\papercut-print-log-all-time.csv")

End Sub

Private Sub UpdateFromCSV(filePath As String)
    Dim ts As Object    'Scripting.TextStream
    Dim s As String
    Dim v As Variant
    Dim i As Long

    Set ts = CreateObject("Scripting.FileSystemObject").GetFile(filePath).OpenAsTextStream(ForReading, TristateUseDefault)

    For i = 1 To Application.ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
        ts.SkipLine
    Next i

    Do While Not ts.AtEndOfStream
        s = ts.ReadLine
        v = Split(s, ",")
        Application.ActiveSheet.Range(Application.ActiveSheet.Cells(i, 1), Application.ActiveSheet.Cells(i, 12)) = v
        i = i + 1
    Loop
    ts.Close

    Set ts = Nothing

End Sub

However, if you just want a spreadsheet to which you have write-access that contains all the values in the CSV file, you could try something like the following. It simply opens the CSV file as Read-only and saves it as an xlsx file.

Option Explicit

Public Sub OpenSaveCSVasXlsx()
    Dim wb As Workbook

    Set wb = Application.Workbooks.Open("your path here\papercut-print-log-all-time.csv", False, True, 2, , , True, , , , False, , False)
    wb.SaveAs "your path here\papercut-print-log-all-time.xlsx", xlOpenXMLWorkbook, , , , , , xlUserResolution

    Set wb = Nothing

End Sub

You can read more about the Scripting.TextStream object here, the Workbooks.Open method here and the Workbook.SaveAs method here.