none
Fix an Error RRS feed

  • Question

  •  

     

    Hi everybody,

    I created the following code with the help of Doug Robbins - Word MVP.

    Now, i'm having a little problem when there is no data present on .FormFields("DOB2"). How do i fix that bug?

    Also, if you can see my code you can tell that is pulling the infomation needed from a Excel Sheet. What do i need to do to change it into a DATA BASE created with ACCESS?

    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim myarray As Variant
    Dim i As Long, j As Long
    On Error Resume Next
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
    bstartApp = True
    Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlbook = xlapp.Workbooks.Open("\\Path\LifeExpectancy.xls")
    Set xlsheet = xlbook.Worksheets(1)
    With ActiveDocument
    i = Int(DateDiff("yyyy", .FormFields("DOB2").Result, Now()) + Int(Format(Now(), "mmdd") < Format(.FormFields("DOB2").Result, "mmdd"))) + 1
    j = .FormFields("Category2").DropDown.Value
    .FormFields("LifeExpectancy2").Result = xlsheet.Range("A1").Offset(i, j).Value
    End With
    xlbook.Close
    If bstartApp = True Then
    xlapp.Quit
    End If
    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlsheet = Nothing
    End If
    End Sub

     


    Thanks for everything, all your help is well appreciated
    Sunday, April 3, 2011 11:40 PM

Answers

  • Hi scorpdevil,

    You could add a test of the 'DOB2' formfield. For example between your 'On Error Resume Next' and 'Set xlapp = GetObject(, "Excel.Application")' lines:

    On Error Resume Next
    If Trim(ActiveDocument.FormFields("DOB2").Result) = vbNullString Then Exit Sub
    Set xlapp = GetObject(, "Excel.Application")

    Or, if you wanted to output something when the 'DOB2' is empty, you could do something like:

    With ActiveDocument
      If Trim(.FormFields("DOB2").Result) = vbNullString Then
        i = 1
      Else
        i = Int(DateDiff("yyyy", .FormFields("DOB2").Result, Now()) + _
          Int(Format(Now(), "mmdd") < Format(.FormFields("DOB2").Result, "mmdd"))) + 1
      End If
    ... etc

    BTW, the 'myarray' variable is unued in the code you posted.

    As for Access, that's a whole different issue. Your present code automates Excel and extracts a value from a specific worksheet. In Access, you'd need a table you could query for the data.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    • Marked as answer by scorpdevil Monday, April 4, 2011 11:06 PM
    Monday, April 4, 2011 6:30 AM

All replies

  • Hi scorpdevil,

    You could add a test of the 'DOB2' formfield. For example between your 'On Error Resume Next' and 'Set xlapp = GetObject(, "Excel.Application")' lines:

    On Error Resume Next
    If Trim(ActiveDocument.FormFields("DOB2").Result) = vbNullString Then Exit Sub
    Set xlapp = GetObject(, "Excel.Application")

    Or, if you wanted to output something when the 'DOB2' is empty, you could do something like:

    With ActiveDocument
      If Trim(.FormFields("DOB2").Result) = vbNullString Then
        i = 1
      Else
        i = Int(DateDiff("yyyy", .FormFields("DOB2").Result, Now()) + _
          Int(Format(Now(), "mmdd") < Format(.FormFields("DOB2").Result, "mmdd"))) + 1
      End If
    ... etc

    BTW, the 'myarray' variable is unued in the code you posted.

    As for Access, that's a whole different issue. Your present code automates Excel and extracts a value from a specific worksheet. In Access, you'd need a table you could query for the data.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    • Marked as answer by scorpdevil Monday, April 4, 2011 11:06 PM
    Monday, April 4, 2011 6:30 AM
  • One way to get the data from Access is to make a reference (Tols->References) to the Microsoft ActiveX Data Objects library, then start with code along the following lines.

    This assumes that your Access table

     a. has fields called myfield1, myfield2, myfield3... etc. and that you can construc the field name using "myfield" and the value of i in Doug's code

     b. Has a primary key ("ID") field called ID that you can find directly using the value of j

    (perhaps i & j need to be the other way around, but I hope you understand what I am saying)

     

    Const strDBPathName As String = "c:\mydbs\myaccessdb.accdb"

    Const strDBProvider As String = "Microsoft.ACE.OLEDB.12.0"

    Dim cn As ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim strField As String

    strField = "myfield" & trim(cstr(i))

    Set cn = New ADODB.Connection

    Set rs = New ADODB.Recordset

    cn.Open "Provider=" & strDBProvider & ";User ID=Admin;Data Source=" & strDBPathName

    rs.Open "SELECT [" & strField & "] FROM [Customers] WHERE [ID] = " & cstr(j), cn

    if rs.EOF Then

      Debug.Print "record not found"

    Else

      .FormFields("LifeExpectancy2").Result = rs("strField").Value

    End If

    rs.Close

    cn.Close

    Set rs = Nothing

    Set cn = Nothing

    There are various other possible conditions you need to deal with, e.g. if the .Value is null. Googling for code along these general lines might find you something more complete. This also assumes that the ACE provider is present. If the users only have Jet, you have to use Microsoft.OLEDB.Jet.4.0 instead, and so on.
    In theory you might be able to use very similar code to get the data from the Excel sheet instead, but I would stick with what you have because
     a. it depends on the sheet layout (e.g. either it has a row of column names at the top or you have to do something slightly different)
     b. there can be problems with ADO (and DAO) returning unexpected values if a column contains mixed data types (text, numeric)


    Peter Jamieson
    Monday, April 4, 2011 11:26 AM
  • Paul - You are beast!! i did the

    If Trim(ActiveDocument.FormFields("DOB2").Result) = vbNullString Then Exit Sub

    And work just fine!!! Very Nice Thanks A Lot


    Thanks for everything, all your help is well appreciated
    Monday, April 4, 2011 11:06 PM
  • Paul,

    I'm going to create the DATA BASE and use you code. I'll get back with on this.

    Thanks so very much for helping me out in all this issues!


    Thanks for everything, all your help is well appreciated
    Monday, April 4, 2011 11:07 PM