filling a listbox with data from a table in an other word document 
Author Message
 filling a listbox with data from a table in an other word document

I want to display the first column of a table who is located in an other
document in a listbox on my userform. On selecting the entries
(multiselect), i want the text of the second column ( with his original
formatting) inserted in my new document, one behind the other. Could anybody
help me?

Word 97, VBA, WinNt4.0

Greetings,
Romain



Sat, 03 Jul 2004 03:42:17 GMT  
 filling a listbox with data from a table in an other word document
Hi Romain,

The original formatting can't be used in a userform but here's some code
that could be used as an example. It has the complet code that's needed
behind the userform.
It assumes that there's an commandbutton on the form named CmdOk and a
listbox (multiselect) named Listbox1.

The code in the Initialize event of the userform fills the listbox and sets
values to the variables for the document and table (note that it is
important that those are declared at the top of your module, otherwise they
won't be known to the code that's executed in the Click event of the
commandbutton.

You need to replace the text between quotes with path and filename of the
document that's opened with the table
    Set oDoc = Documents("Path and Filename")

If the table in this document isn't the first table in the document, you
also need to change:
  Set oTable = oDoc.Tables(1)
with the correct index for the table

---------------------------------------------------
Option Explicit
'Declare variables at the top of the
'module, so they keep their value
Dim oTable As Table
Dim oDoc As Document

Private Sub CommandButton1_Click()
Dim i As Integer
Dim oRange As Range

  Set oRange = Selection.Range
  oRange.Collapse direction:=wdCollapseEnd

  For i = 0 To Me.ListBox1.ListCount - 1

    If Me.ListBox1.Selected(i) Then

      'Item is selected, get text from second document

      oRange.Text = Left(oTable.Columns(2).Cells(i + 1).Range.Text, _
            Len(oTable.Columns(2).Cells(i + 1).Range.Text) - 2)

      oRange.InsertParagraphAfter
      oRange.Collapse direction:=wdCollapseEnd

    End If

  Next

  Set oRange = Nothing
  Unload UserForm1

End Sub

Private Sub UserForm_Initialize()
Dim oCell As Cell

  'Replace the text in brackets with path
  'and filename from the open file you need
  'for example
  Set oDoc = Documents("Path and Filename")
  Set oTable = oDoc.Tables(1)
  For Each oCell In oTable.Columns(1).Cells
    'Cut the last two characters from the cell
    Me.ListBox1.AddItem Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
  Next

  Set oCell = Nothing

End Sub
---------------------------------------------------

Hope this helps,
regards,
Astrid

So that all can benefit from the discussion, please post all follow-ups to
the newsgroup.
Visit the MVP Word FAQ site at http://www.mvps.org/word/



Quote:
> I want to display the first column of a table who is located in an other
> document in a listbox on my userform. On selecting the entries
> (multiselect), i want the text of the second column ( with his original
> formatting) inserted in my new document, one behind the other. Could
anybody
> help me?

> Word 97, VBA, WinNt4.0

> Greetings,
> Romain



Sat, 03 Jul 2004 09:25:01 GMT  
 filling a listbox with data from a table in an other word document
Thanks a lot, Astrid

But there seems to be a bug : i get a run-time error : error 4160 (bad file
name). The path and Filename of the document i refer to are correct
(c:\test\remarques.doc).

Set oDoc = Documents("c:\test\remarques.doc")

Any ideas?

Romain


Quote:
> Hi Romain,

> The original formatting can't be used in a userform but here's some code
> that could be used as an example. It has the complet code that's needed
> behind the userform.
> It assumes that there's an commandbutton on the form named CmdOk and a
> listbox (multiselect) named Listbox1.

> The code in the Initialize event of the userform fills the listbox and
sets
> values to the variables for the document and table (note that it is
> important that those are declared at the top of your module, otherwise
they
> won't be known to the code that's executed in the Click event of the
> commandbutton.

> You need to replace the text between quotes with path and filename of the
> document that's opened with the table
>     Set oDoc = Documents("Path and Filename")

> If the table in this document isn't the first table in the document, you
> also need to change:
>   Set oTable = oDoc.Tables(1)
> with the correct index for the table

> ---------------------------------------------------
> Option Explicit
> 'Declare variables at the top of the
> 'module, so they keep their value
> Dim oTable As Table
> Dim oDoc As Document

> Private Sub CommandButton1_Click()
> Dim i As Integer
> Dim oRange As Range

>   Set oRange = Selection.Range
>   oRange.Collapse direction:=wdCollapseEnd

>   For i = 0 To Me.ListBox1.ListCount - 1

>     If Me.ListBox1.Selected(i) Then

>       'Item is selected, get text from second document

>       oRange.Text = Left(oTable.Columns(2).Cells(i + 1).Range.Text, _
>             Len(oTable.Columns(2).Cells(i + 1).Range.Text) - 2)

>       oRange.InsertParagraphAfter
>       oRange.Collapse direction:=wdCollapseEnd

>     End If

>   Next

>   Set oRange = Nothing
>   Unload UserForm1

> End Sub

> Private Sub UserForm_Initialize()
> Dim oCell As Cell

>   'Replace the text in brackets with path
>   'and filename from the open file you need
>   'for example
>   Set oDoc = Documents("Path and Filename")
>   Set oTable = oDoc.Tables(1)
>   For Each oCell In oTable.Columns(1).Cells
>     'Cut the last two characters from the cell
>     Me.ListBox1.AddItem Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
>   Next

>   Set oCell = Nothing

> End Sub
> ---------------------------------------------------

> Hope this helps,
> regards,
> Astrid

> So that all can benefit from the discussion, please post all follow-ups to
> the newsgroup.
> Visit the MVP Word FAQ site at http://www.mvps.org/word/



> > I want to display the first column of a table who is located in an other
> > document in a listbox on my userform. On selecting the entries
> > (multiselect), i want the text of the second column ( with his original
> > formatting) inserted in my new document, one behind the other. Could
> anybody
> > help me?

> > Word 97, VBA, WinNt4.0

> > Greetings,
> > Romain



Sat, 03 Jul 2004 20:07:54 GMT  
 filling a listbox with data from a table in an other word document
Hi Romain

Is that document already open when you run that line of code? If not, you
should use:

Set oDoc = Documents.Open("c:\test\remarques.doc")

Regards

Dave



| Thanks a lot, Astrid
|
| But there seems to be a bug : i get a run-time error : error 4160 (bad
file
| name). The path and Filename of the document i refer to are correct
| (c:\test\remarques.doc).
|
| Set oDoc = Documents("c:\test\remarques.doc")
|
| Any ideas?
|
| Romain
|
|
|

| > Hi Romain,
| >
| > The original formatting can't be used in a userform but here's some code
| > that could be used as an example. It has the complet code that's needed
| > behind the userform.
| > It assumes that there's an commandbutton on the form named CmdOk and a
| > listbox (multiselect) named Listbox1.
| >
| > The code in the Initialize event of the userform fills the listbox and
| sets
| > values to the variables for the document and table (note that it is
| > important that those are declared at the top of your module, otherwise
| they
| > won't be known to the code that's executed in the Click event of the
| > commandbutton.
| >
| > You need to replace the text between quotes with path and filename of
the
| > document that's opened with the table
| >     Set oDoc = Documents("Path and Filename")
| >
| > If the table in this document isn't the first table in the document, you
| > also need to change:
| >   Set oTable = oDoc.Tables(1)
| > with the correct index for the table
| >
| > ---------------------------------------------------
| > Option Explicit
| > 'Declare variables at the top of the
| > 'module, so they keep their value
| > Dim oTable As Table
| > Dim oDoc As Document
| >
| > Private Sub CommandButton1_Click()
| > Dim i As Integer
| > Dim oRange As Range
| >
| >   Set oRange = Selection.Range
| >   oRange.Collapse direction:=wdCollapseEnd
| >
| >   For i = 0 To Me.ListBox1.ListCount - 1
| >
| >     If Me.ListBox1.Selected(i) Then
| >
| >       'Item is selected, get text from second document
| >
| >       oRange.Text = Left(oTable.Columns(2).Cells(i + 1).Range.Text, _
| >             Len(oTable.Columns(2).Cells(i + 1).Range.Text) - 2)
| >
| >       oRange.InsertParagraphAfter
| >       oRange.Collapse direction:=wdCollapseEnd
| >
| >     End If
| >
| >   Next
| >
| >   Set oRange = Nothing
| >   Unload UserForm1
| >
| > End Sub
| >
| > Private Sub UserForm_Initialize()
| > Dim oCell As Cell
| >
| >   'Replace the text in brackets with path
| >   'and filename from the open file you need
| >   'for example
| >   Set oDoc = Documents("Path and Filename")
| >   Set oTable = oDoc.Tables(1)
| >   For Each oCell In oTable.Columns(1).Cells
| >     'Cut the last two characters from the cell
| >     Me.ListBox1.AddItem Left(oCell.Range.Text, Len(oCell.Range.Text) -
2)
| >   Next
| >
| >   Set oCell = Nothing
| >
| > End Sub
| > ---------------------------------------------------
| >
| > Hope this helps,
| > regards,
| > Astrid
| >
| > So that all can benefit from the discussion, please post all follow-ups
to
| > the newsgroup.
| > Visit the MVP Word FAQ site at http://www.mvps.org/word/
| >


| > > I want to display the first column of a table who is located in an
other
| > > document in a listbox on my userform. On selecting the entries
| > > (multiselect), i want the text of the second column ( with his
original
| > > formatting) inserted in my new document, one behind the other. Could
| > anybody
| > > help me?
| > >
| > > Word 97, VBA, WinNt4.0
| > >
| > > Greetings,
| > > Romain
| > >
| > >
| >
| >
|
|



Sat, 03 Jul 2004 21:05:01 GMT  
 filling a listbox with data from a table in an other word document
Thanks a lot, it functions without problem.
But i have another problem : can i keep the original text formatting in my
new document? Perhaps with FORMATTEDTEXT ? Could you give me a tip?

Thanks
Romain


Quote:
> Hi Romain

> Is that document already open when you run that line of code? If not, you
> should use:

> Set oDoc = Documents.Open("c:\test\remarques.doc")

> Regards

> Dave



> | Thanks a lot, Astrid
> |
> | But there seems to be a bug : i get a run-time error : error 4160 (bad
> file
> | name). The path and Filename of the document i refer to are correct
> | (c:\test\remarques.doc).
> |
> | Set oDoc = Documents("c:\test\remarques.doc")
> |
> | Any ideas?
> |
> | Romain
> |
> |
> |


> | > Hi Romain,
> | >
> | > The original formatting can't be used in a userform but here's some
code
> | > that could be used as an example. It has the complet code that's
needed
> | > behind the userform.
> | > It assumes that there's an commandbutton on the form named CmdOk and a
> | > listbox (multiselect) named Listbox1.
> | >
> | > The code in the Initialize event of the userform fills the listbox and
> | sets
> | > values to the variables for the document and table (note that it is
> | > important that those are declared at the top of your module, otherwise
> | they
> | > won't be known to the code that's executed in the Click event of the
> | > commandbutton.
> | >
> | > You need to replace the text between quotes with path and filename of
> the
> | > document that's opened with the table
> | >     Set oDoc = Documents("Path and Filename")
> | >
> | > If the table in this document isn't the first table in the document,
you
> | > also need to change:
> | >   Set oTable = oDoc.Tables(1)
> | > with the correct index for the table
> | >
> | > ---------------------------------------------------
> | > Option Explicit
> | > 'Declare variables at the top of the
> | > 'module, so they keep their value
> | > Dim oTable As Table
> | > Dim oDoc As Document
> | >
> | > Private Sub CommandButton1_Click()
> | > Dim i As Integer
> | > Dim oRange As Range
> | >
> | >   Set oRange = Selection.Range
> | >   oRange.Collapse direction:=wdCollapseEnd
> | >
> | >   For i = 0 To Me.ListBox1.ListCount - 1
> | >
> | >     If Me.ListBox1.Selected(i) Then
> | >
> | >       'Item is selected, get text from second document
> | >
> | >       oRange.Text = Left(oTable.Columns(2).Cells(i + 1).Range.Text, _
> | >             Len(oTable.Columns(2).Cells(i + 1).Range.Text) - 2)
> | >
> | >       oRange.InsertParagraphAfter
> | >       oRange.Collapse direction:=wdCollapseEnd
> | >
> | >     End If
> | >
> | >   Next
> | >
> | >   Set oRange = Nothing
> | >   Unload UserForm1
> | >
> | > End Sub
> | >
> | > Private Sub UserForm_Initialize()
> | > Dim oCell As Cell
> | >
> | >   'Replace the text in brackets with path
> | >   'and filename from the open file you need
> | >   'for example
> | >   Set oDoc = Documents("Path and Filename")
> | >   Set oTable = oDoc.Tables(1)
> | >   For Each oCell In oTable.Columns(1).Cells
> | >     'Cut the last two characters from the cell
> | >     Me.ListBox1.AddItem Left(oCell.Range.Text, Len(oCell.Range.Text) -
> 2)
> | >   Next
> | >
> | >   Set oCell = Nothing
> | >
> | > End Sub
> | > ---------------------------------------------------
> | >
> | > Hope this helps,
> | > regards,
> | > Astrid
> | >
> | > So that all can benefit from the discussion, please post all
follow-ups
> to
> | > the newsgroup.
> | > Visit the MVP Word FAQ site at http://www.mvps.org/word/
> | >


> | > > I want to display the first column of a table who is located in an
> other
> | > > document in a listbox on my userform. On selecting the entries
> | > > (multiselect), i want the text of the second column ( with his
> original
> | > > formatting) inserted in my new document, one behind the other. Could
> | > anybody
> | > > help me?
> | > >
> | > > Word 97, VBA, WinNt4.0
> | > >
> | > > Greetings,
> | > > Romain
> | > >
> | > >
> | >
> | >
> |
> |



Tue, 06 Jul 2004 02:51:12 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. how to fill a listbox with the names of table, queries

2. How to Fill a Word UserForm ListBox with Excel Values

3. How can I fill a listbox with the table names from an Access .mdb

4. Filling in a Word Document

5. Fill Ole Object Field with a word document

6. listbox fills only partially when presenting a lot of data

7. Help needed with VBA to take data from a recordset to fill a listbox

8. HELP! Filling unbound listbox w/lg data

9. To fill a cell in a Word table with VBA

10. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

11. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

12. Cutting a full Word document and pasting it in another Word Document

 

 
Powered by phpBB® Forum Software