Can this be automated?

[yuli1104]

Hello everyone:

I need to manually export .txt data into a word table every day.

Here is the raw text data in a comma separated value format:

Hostname, Percentage, N, Time, event details

Host1, 100.00%,,,
Host2,100.00%,,,
Host3,99.97%,1, 00:03:20 (200/712800), Mon Jan 27 10:02:32 2003 for 00:03:20
Host4,99.91%,2, 00:11:14 (674/712800), - BSOD - no additional info – rebooted. Thu Jan 9 13:19:13 2003 for 00:05:48. Mon Jan 27 12:06:54 2003 for 00:05:26


The data is manually used to produce a TABLE in WORD:

Host1 100.00%
Host2 100.00%
Host3 99.97% 1 00:03:20 (200/712800) View Event Detail
Host4 99.91% 2 00:11:14 (674/712800) View Event Detail

Can a program be written to import the raw data into a WORD TABLE?

The tricky part is the last field. If “event details” are present, then the table contains the words “view event detail” which is a “link” to comments. Manually those are created by highlighting the text “view event details” and pressing INSERT->COMMENT.

So I will need two steps of help:
1. how to put data into a word table
2. how to link the comments.

Thank you for any help

Yuli

 

[jpittawa]

I don't have the time at the moment to answer this in detail, but yes, you can do what you want with VB using what used to be called "OLE Automation" and is now called simply "Automation."

I'll try to get back to you this evening with a better reply. The trick is to turn on the Word Macro recorder when you make the table and turn it off after you make one or two entries. Look at the resultant VBA in the Macro and use it from your VB program to emulate the Word macro steps.

We will use Split() to tokenize the input file lines and then the VBA to put them into an instance of Word.

 

[yuli1104]

Thank you jpittawa.
First of all I am very new to VB. I only know how to load Word with VB.

I need to clarify something. I need to automatically convert raw text data into a word table. The user of the program shouldn't enter any data manually. The only input is the file path of the raw data.

I will be waiting for your reply. Thank you very very much.

Yuli

 

[jpittawa]

This solution is fully automated. I am litterally going out of the door for work. I'll try to get back to this tonight.

In the meantime, you can do some research to familiarize yourself with Automation. Do a search in MSDN on "OLE Automation" and look at the responses from Technical Articles. There is one that show you how to use the SpellCheck from VB. That will show how to make VB drive Word. If memory serves, it will also walk you through the process of trapping VBA code in Word.

 

[jpittawa]

This program works. Creates a word table with the event details present. I have not figured out how to do the linking yet. It saves the output in a document in the same folder as the log file. It appends a timestamp to the file name to prevent overwriting previous documents and to facilitate sorting.

It uses early binding, which means that you will have to set a reference to the Word instance on your machine. It also means that the same level of Word must be on the machine where the program will run. This can be obviated by changing each of the Word peculiar variable types, e.g. Word.Application, to Object and then instantiating them with CreateObject. All of the methods used should work with Office 95, 97, and 2000.

If you want to make this a "lights out" operation, omit the use of the Common Dialog control to find the source file. Also, if you want to speed up the operation, comment out the line the

wrdApp.Visible = True

line.

 Option Explicit

Public strLogNudeFileName As String     'LogFile name w/o extension
Public strPath As String                'Path to log file folder
Public strLogText                       'Log in one long string
Public lngRowCount                      'Number of rows in the log
Public lngColCount                      'Number columns in the log
Public wrdApp As Word.Application
Public wrdDoc As Word.Document

Public Sub LoadLogText()
    Dim fn As Long
    Dim ls As String
    Dim sa() As String
    Dim saCols() As String
    Dim ix As Integer
    Dim iy As Integer
    'Preset globals:
    lngRowCount = 0:    lngColCount = 0
    
    'Read the log into a local string:
    fn = FreeFile
    Open Me.lblLogFile For Input As #FreeFile
    ls = Input(LOF(fn), fn)
    
    'Break log file into lines:
    sa = Split(ls, vbCrLf)
    
    'Set log's globals:
    For ix = 0 To UBound(sa)
        'Ignore blank lines
        If Trim(sa(ix)) <> &quot;&quot; Then
            'Break log file line into it's cells:
            saCols = Split(sa(ix), &quot;,&quot;)
            If lngRowCount = 0 Then lngColCount = UBound(saCols) + 1
            For iy = 0 To UBound(saCols)
                strLogText = strLogText & IIf(saCols(iy) = &quot;&quot;, &quot;  &quot;, saCols(iy)) & &quot;,&quot;
            Next iy
            strLogText = Left(strLogText, Len(strLogText) - 1)
            strLogText = strLogText & vbCrLf
            lngRowCount = lngRowCount + 1
        End If
    Next
End Sub

Private Sub cmdExit_Click()

    Unload Me
    
End Sub

Private Sub cmdLocateLog_Click()
    'Common Dialog used to get the file name:
    With Me.CommonDialog1
        .FileName = &quot;*.log&quot;
        .ShowOpen
        Me.lblLogFile = .FileName
        Me.strLogNudeFileName = Left(.FileTitle, InStr(.FileTitle, &quot;.&quot;) - 1)
        Me.strPath = Left(.FileName, InStrRev(.FileName, &quot;\&quot;) - 1)
    End With
End Sub

Private Sub cmdWordTable_Click()
    Dim wrdSelection As Word.Selection
    Dim ls As String
    
    'Read the log into one long string:
    LoadLogText
    
    'Create a new instance of Word and show it:
    Set Me.wrdApp = New Word.Application
    wrdApp.Visible = True
    
    'Load the table:
    Set wrdDoc = wrdApp.Documents.Add
    wrdDoc.Select
    Set wrdSelection = wrdApp.Selection
    wrdSelection.Range.InsertAfter strLogText
    wrdSelection.Range.ConvertToTable Separator:=wdSeparateByCommas, _
                                      NumRows:=lngRowCount, NumColumns:=lngColCount
    wrdDoc.Tables(1).Rows(1).Cells.Shading.BackgroundPatternColorIndex = wdGray25
        
    'Save the file
    ls = Me.strPath & &quot;\&quot; & Me.strLogNudeFileName & &quot;_log_&quot; & Format(Now, &quot;yyyymmddhhnnss&quot;) & &quot;.doc&quot;
    wrdDoc.SaveAs FileName:=ls
    wrdDoc.Close
    wrdApp.Quit
End Sub

 

[yuli1104]

Thank you very much. It works very well on my computer. I will do some research to see if I can do the linking. Thank you !!!

Yuli

 

[jpittawa]

>Thank you very much.

You are welcome. I enjoyed figuring this out. A couple of things have come to mind since I posted this code.

First, If Event Details portion were to contain a comma as part of the text, that would throw the Split() off. I would recommend a tilde "~" for the delimiter. If that is not possible, then check the assignment to saCols in LoadLogText(). If it is greater then MaxColumns -1, 4 in this case, you know that there is comma gumming up the works. The first 3 saCols entries are correct. Put exception code in to recombine saCols(4) -> saCols(x).

This might work:

Dim iz As Integer
Dim strX As String

For iz = 4 To lngColCount
  strX = strX & saCols(iz)
Next iz
saCols(4) = strX

I have not tested this code. It should work.

Second, if you were to convert the log file text into an HTML table and save it with the DOC extension, Word would open it as a table. Save it with an HTM extension and Word or Excel would open it as a table.

Good luck.

 

[CCLINT]

>Thank you very much

yuli1104:

Ever consider using the option below the post, in order to mark the post as a helpful one?
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Hypetia]

The Selection and Range objects of Word library expose a method named ConvertToTable. This method is linked with the command: Table>Convert>Text to Table.

You can use this method to convert a CSV file into a table using a single call.

However, you have to do some more for inserting comments and creating hyperlinks, after the table is created.

 

[jpittawa]

Hypatia, your suggestion works if there are no blank lines in the targeted information in the input file.