Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Oct 2008
    Location
    London
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reading excell data and using it in word (2003)

    Hi all
    I am trying without success to read some cells in an excel spreadsheet from in word using vba
    So I have a workbook called HHSRS The workbook has two work sheets Relmat and Hazards
    I have a VBA form that has 32 labels on it . I need to read cells in the workbook on the worksheet Relmat the cells are always in Column A but may be a different range each time
    The data is retrieved based on the value of a textbox on the previous form that form is called Hazarddetail
    I have set a reference to excel but I always get a global error on loading the form

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading excell data and using it in word (2003)

    Can you provide more detailed information? What code are you trying to use, and where exactly does it fail?

  3. #3
    Lounger
    Join Date
    Oct 2008
    Location
    London
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading excell data and using it in word (2003)

    Private Sub UserForm_Initialize()

    Dim wb As Workbook



    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:hhsrs.xls", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("Relmat")
    'hazard 1
    If FrmhazardDetail.Txthazard.Value = "Damp and Mould (1)" Then
    Lblmatter1.Visible = True
    Lblmatter1.Caption = wb.Worksheets("Relmat").Range("A3") ' the value of each cell is returned here to this label
    Cmbmatter1.Visible = True
    End If
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating

    But I probably got it all wrong

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading excell data and using it in word (2003)

    You have to create an Excel Application object, and take great care NEVER to use an Excel item that does not refer directly or indirectly to this Application object.

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Open("C:hhsrs.xls", True, True)
    Set xlWsh = xlWbk.Worksheets("Relmat")
    If FrmhazardDetail.Txthazard.Value = "Damp and Mould (1)" Then
    Lblmatter1.Visible = True
    Lblmatter1.Caption = xlWsh.Range("A3")
    Cmbmatter1.Visible = True
    End If
    xlWbk.Close False
    xlApp.Quit
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing

  5. #5
    Lounger
    Join Date
    Oct 2008
    Location
    London
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading excell data and using it in word (2003)

    Thanks will have another go

  6. #6
    Lounger
    Join Date
    Oct 2008
    Location
    London
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading excell data and using it in word (2003)

    So why does it work for the first block but not the second It never gets the data
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Open("C:hhsrs.xls", True, True)
    Set xlWsh = xlWbk.xlWsh
    'Hazard 1

    If FrmhazardDetail.Txthazard.Value = "Damp and Mould (1)" Then

    Lblmatter1.Visible = True
    Lblmatter1.Caption = xlWsh.Range("A3")
    Cmbmatter1.Visible = True
    Lblmatter2.Visible = True
    Lblmatter2.Caption = xlWsh.Range("A4")
    Cmbmatter2.Visible = True
    Lblmatter3.Visible = True
    Lblmatter3.Caption = xlWsh.Range("A5")
    Cmbmatter3.Visible = True
    Lblmatter4.Visible = True
    Lblmatter4.Caption = xlWsh.Range("A6")
    Cmbmatter4.Visible = True
    Lblmatter5.Visible = True
    Lblmatter5.Caption = xlWsh.Range("A7")
    Cmbmatter5.Visible = True
    Lblmatter6.Visible = True
    Lblmatter6.Caption = xlWsh.Range("A8")
    Cmbmatter6.Visible = True
    Lblmatter7.Visible = True
    Lblmatter7.Caption = xlWsh.Range("A9")
    Cmbmatter7.Visible = True
    Lblmatter8.Visible = True
    Lblmatter8.Caption = xlWsh.Range("A10")
    Cmbmatter8.Visible = True
    Lblmatter9.Visible = True
    Lblmatter9.Caption = xlWsh.Range("A11")
    Cmbmatter9.Visible = True
    Lblmatter10.Visible = True
    Lblmatter10.Caption = xlWsh.Range("A12")
    Cmbmatter10.Visible = True
    Lblmatter11.Visible = True
    Lblmatter11.Caption = xlWsh.Range("A13")
    Cmbmatter11.Visible = True
    Lblmatter12.Visible = True
    Lblmatter12.Caption = xlWsh.Range("A14")
    Cmbmatter12.Visible = True

    End If

    'Hazard 2
    If FrmhazardDetail.Txthazard.Value = "Excess Cold (2)" Then
    Lblmatter1.Visible = True
    Lblmatter1.Caption = xlWsh.Range("A22")
    Cmbmatter1.Visible = True
    Lblmatter2.Visible = True
    Lblmatter2.Caption = xlWsh.Range("A23")
    Cmbmatter2.Visible = True
    Lblmatter3.Visible = True
    Lblmatter3.Caption = xlWsh.Range("A24")
    Cmbmatter3.Visible = True
    Lblmatter4.Visible = True
    Lblmatter4.Caption = xlWsh.Range("A25")
    Cmbmatter4.Visible = True
    Lblmatter5.Visible = True
    Lblmatter5.Caption = xlWsh.Range("A26")
    Cmbmatter5.Visible = True
    Lblmatter6.Visible = True
    Lblmatter6.Caption = xlWsh.Range("A27")
    Cmbmatter6.Visible = True
    Lblmatter7.Visible = True
    Lblmatter7.Caption = xlWsh.Range("A28")
    Cmbmatter7.Visible = True
    Lblmatter8.Visible = True
    Lblmatter8.Caption = xlWsh.Range("A29")
    Cmbmatter8.Visible = True
    Lblmatter9.Visible = True
    Lblmatter9.Caption = xlWsh.Range("A30")
    Cmbmatter9.Visible = True
    Lblmatter10.Visible = True
    Lblmatter10.Caption = xlWsh.Range("A31")
    Cmbmatter10.Visible = True
    Lblmatter11.Visible = True
    Lblmatter11.Caption = xlWsh.Range("A32")
    Cmbmatter11.Visible = True
    End If

    xlWbk.Close False
    xlApp.Quit
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading excell data and using it in word (2003)

    Are you sure that the value is exactly "Excess Cold (2)"? Even one character difference (an extra space, an upper case letter instead of a lower case letter, etc.) would cause the comparison to fail.

  8. #8
    Lounger
    Join Date
    Oct 2008
    Location
    London
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading excell data and using it in word (2003)

    Yep its copied from the drop down list to make sure. The problem is this I think Excel never closes it remains open in the computer processes and so the second item ids not picked up
    Strange but I will run again and check

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading excell data and using it in word (2003)

    The line

    Set xlWsh = xlWbk.xlWsh

    doesn't make sense, it should be something like

    Set xlWsh = xlWbk.Worksheets("Relmat")

  10. #10
    Lounger
    Join Date
    Oct 2008
    Location
    London
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading excell data and using it in word (2003)

    OK will change and see what happens Thanks

  11. #11
    Lounger
    Join Date
    Oct 2008
    Location
    London
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading excell data and using it in word (2003)

    That seems to work but will come back once I have added the next 28 of them
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •