none
filtering out parentheses that are sometimes there and sometimes not RRS feed

  • Question

  • I have written a program that pulls data from a word document that contains tables and places the data into an excel spread sheet. I just noticed that I lose any data (returns a zero) that is contained in parentheses (ususally, in my case, used to indicate failed data). The problem is that the parenthesis are not always there (because the data is not always a failure) and not always in the same table locations. I have been using the val() function to pull out just the numbers from the strings I get from the tables (as they sometimes contain other charachters such as Ohmegas or other suffixes) before I stick the values into my array. I have now realized that this zero return happens because the val() function stops when it hits the opening parentheses (I knew the val() function did this when I wrote it but failed to realize that there were values in my datasets that were contained in parentheses). Any suggestions as to how I should deal with this? I thought about running a replace loop but it seems very cumbersome in my situation. Here is my code for this section of the program. I am fairly new to VBA so I won't be offended if you point out other things I have done ineffectively or just wrong. Aside from loosing the values inside the parentheses the code does exactly what I want it to.

    ' Loop through Word documents
        strFile = Dir(strPath & "*.doc*")
        rowVal = 7
        
        Do While strFile <> ""
            ' Open document
            Set doc = Documents.Open(strPath & strFile)
            p = InStrRev(strFile, ".")
            strName = Left(strFile, p - 1)
            
              'Extract table data from doc
                PN = ActiveDocument.Tables(1).Rows(2).Cells(1)
                PN = Left(PN, Len(PN) - 1) 'truncate the last charachter in the string to get rid of bullet
                PN = Left(PN, Len(PN) - 1) 'truncate the last charachter in the string to get rid of blank space
                SN = ActiveDocument.Tables(1).Rows(2).Cells(3)
                SN = Left(SN, Len(SN) - 1) 'truncate the last charachter in the string to get rid of bullet
                SN = Left(SN, Len(SN) - 1) 'truncate the last charachter in the string to get rid of blank space
                U1ARE = ActiveDocument.Tables(3).Rows(3).Cells(1)
                U1ARE = val(U1ARE) 'val(string) used to pull out only numbers (does not identify and stops after non numeric charachters except "." and neg sign)
                U1AJ = ActiveDocument.Tables(3).Rows(3).Cells(2)
                U1AJ = val(U1AJ)
                U1AGAIN = ActiveDocument.Tables(3).Rows(3).Cells(3)
                U1AGAIN = val(U1AGAIN)
                U1AI = ActiveDocument.Tables(3).Rows(3).Cells(4)
                U1AI = val(U1AI)
                U1BRE = ActiveDocument.Tables(3).Rows(3).Cells(5)
                U1BRE = val(U1BRE)
                U1BJ = ActiveDocument.Tables(3).Rows(3).Cells(6)
                U1BJ = val(U1BJ)
                U1BGAIN = ActiveDocument.Tables(3).Rows(3).Cells(7)
                U1BGAIN = val(U1BGAIN)
                U1BI = ActiveDocument.Tables(3).Rows(3).Cells(8)
                U1BI = val(U1BI)
                W1ARE = ActiveDocument.Tables(4).Rows(3).Cells(1)
                W1ARE = val(W1ARE)
                W1AJ = ActiveDocument.Tables(4).Rows(3).Cells(2)
                W1AJ = val(W1AJ)
                W1BRE = ActiveDocument.Tables(4).Rows(3).Cells(3)
                W1BRE = val(W1BRE)
                W1BJ = ActiveDocument.Tables(4).Rows(3).Cells(4)
                W1BJ = val(W1BJ)
                C1ADB = ActiveDocument.Tables(5).Rows(4).Cells(1)
                C1ADB = val(C1ADB)
                C1ACAP = ActiveDocument.Tables(5).Rows(4).Cells(2)
                C1ACAP = val(C1ACAP)
                C1BDB = ActiveDocument.Tables(5).Rows(4).Cells(3)
                C1BDB = val(C1BDB)
                C1BCAP = ActiveDocument.Tables(5).Rows(4).Cells(4)
                C1BCAP = val(C1BCAP)
                C12ADB = ActiveDocument.Tables(6).Rows(4).Cells(1)
                C12ADB = val(C12ADB)
                C12ACAP = ActiveDocument.Tables(6).Rows(4).Cells(2)
                C12ACAP = val(C12ACAP)
                C12BDB = ActiveDocument.Tables(6).Rows(4).Cells(3)
                C12BDB = val(C12BDB)
                C12BCAP = ActiveDocument.Tables(6).Rows(4).Cells(4)
                C12BCAP = val(C12BCAP)
                C11ADB = ActiveDocument.Tables(7).Rows(3).Cells(1)
                C11ADB = val(C11ADB)
                C11ACAP = ActiveDocument.Tables(7).Rows(3).Cells(2)
                C11ACAP = val(C11ACAP)
                C11BDB = ActiveDocument.Tables(7).Rows(3).Cells(3)
                C11BDB = val(C11BDB)
                C11BCAP = ActiveDocument.Tables(7).Rows(3).Cells(4)
                C11BCAP = val(C11BCAP)
                U1ADB = ActiveDocument.Tables(8).Rows(3).Cells(1)
                U1ADB = val(U1ADB)
                U1BDB = ActiveDocument.Tables(8).Rows(3).Cells(2)
                U1BDB = val(U1BDB)
                
                tblData = Array(PN, SN, U1ARE, U1AJ, U1AGAIN, U1AI, U1BRE, U1BJ, U1BGAIN, U1BI, W1ARE, W1AJ, W1BRE, W1BJ, C1ADB, C1ACAP, C1BDB, C1BCAP, C12ADB, C12ACAP, C12BDB, C12BCAP, C11ADB, C11ACAP, C11BDB, C11BCAP, U1ADB, U1BDB)
                
                'Write Data to excel
                Set app = GetObject(Class:="Excel.Application")
                objWorkbook.Worksheets(1).Range("A" & rowVal).Resize(1, 28).Value = tblData
                rowVal = (rowVal + 1)
                
            ' Close document
            doc.Close SaveChanges:=False
            ' On to the next document
            strFile = Dir
        Loop

     

    Friday, January 4, 2013 2:47 PM

Answers

  • You could change the code like this:

                U1ARE = ActiveDocument.Tables(3).Rows(3).Cells(1)
                U1ARE = Replace(U1ARE, "(", "") ' remove (
                U1ARE = val(U1ARE) 'val(string) used to pull out only numbers


    Regards, Hans Vogelaar

    • Marked as answer by dhnobles Monday, January 7, 2013 2:28 PM
    Friday, January 4, 2013 3:20 PM

All replies

  • You could change the code like this:

                U1ARE = ActiveDocument.Tables(3).Rows(3).Cells(1)
                U1ARE = Replace(U1ARE, "(", "") ' remove (
                U1ARE = val(U1ARE) 'val(string) used to pull out only numbers


    Regards, Hans Vogelaar

    • Marked as answer by dhnobles Monday, January 7, 2013 2:28 PM
    Friday, January 4, 2013 3:20 PM
  • Try stuff like:

    U1ARE = ActiveDocument.Tables(3).Rows(3).Cells(1)
    U1ARE = Replace(1,U1ARE, "(", "") 'Start at first position, replace U1ARE opening bracket with nothing
    'U1ARE = Replace(1,U1ARE, ")", "") 'Start at first position, replace U1ARE closing bracket with nothing (you may not care to do this)
    U1ARE = Replace(1,U1ARE, "(", "") 'Start at first position, replace U1ARE opening bracket with nothing
    U1ARE = val(U1ARE)


    Also note that where you do this:

    PN = Left(PN, Len(PN) - 1) 'truncate the last charachter in the string to get rid of bullet
    PN = Left(PN, Len(PN) - 1) 'truncate the last charachter in the string to get rid of blank space


    It would be more resilient to do this, as it will cope with any amount of white space, not just a single space.

    PN = Trim(Left(PN, Len(PN) - 1)) 'truncate the last charachter in the string to get rid of bullet, and Trim$ the results to remove whitespace

    Friday, January 4, 2013 10:32 PM
  • These are going to be nice to hold onto.

    Not sure why but for some reason changing this last bit leaves a few of my cells formated strange and messes up my conditional formatting.

    PN = Trim(Left(PN, Len(PN) - 1)) 'truncate the last charachter in the string to get rid of bullet, and Trim$ the results to remove whitespace

    Monday, January 7, 2013 2:31 PM
  • We'd have to know more about the content of the cell, and about the conditional formatting rules that apply to the cell.

    Regards, Hans Vogelaar

    Monday, January 7, 2013 2:57 PM
  • Its ok, the code is working well and doing exactly what I need it to. For now I'm going to go with the ole "If it ain't broke" approach. At least until I need to add more features.

    Thanks!

    Monday, January 7, 2013 3:06 PM