Word Table to Access

Hi, I was wondering if expert can give me some lite to convert my word table
into access database.

Note: within each cell of my word table(s), some  has multi-line data in it.
In addition, there is one row containing picture(s) as well.

So far, what I did is doing it manually for each word docs I have.

Select Table
Convert Table to Text(I use ^ character for delimiter)
Save it to a text file(with char substitution, and CR/LF)
Then, Load them to Access DB.

Is there an easier way to do this?


0
Ruby
2/18/2004 3:48:56 PM
access.conversion 3038 articles. 0 followers. Follow

27 Replies
1268 Views

Similar Articles

[PageSpeed] 47

I'd love to see it done easily in windows. What you can do is to save your
word doc to text with delimiter and let perl handle the parsing.

Opensource to the rescue!

"Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> Hi, I was wondering if expert can give me some lite to convert my word
table
> into access database.
>
> Note: within each cell of my word table(s), some  has multi-line data in
it.
> In addition, there is one row containing picture(s) as well.
>
> So far, what I did is doing it manually for each word docs I have.
>
> Select Table
> Convert Table to Text(I use ^ character for delimiter)
> Save it to a text file(with char substitution, and CR/LF)
> Then, Load them to Access DB.
>
> Is there an easier way to do this?
>
>


0
Hunne
2/18/2004 4:45:00 PM
Hi Ruby,

The easiest way to do this is to copy the table to Excel, then either
save the Excel workbook and import it into Access or copy/paste from
Excel to Access.

-- 
Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/


"Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> Hi, I was wondering if expert can give me some lite to convert my
word table
> into access database.
>
> Note: within each cell of my word table(s), some  has multi-line
data in it.
> In addition, there is one row containing picture(s) as well.
>
> So far, what I did is doing it manually for each word docs I have.
>
> Select Table
> Convert Table to Text(I use ^ character for delimiter)
> Save it to a text file(with char substitution, and CR/LF)
> Then, Load them to Access DB.
>
> Is there an easier way to do this?
>
>


0
Beth
2/18/2004 6:37:07 PM
I think the only way to do this project is manually.  

www.klh-tech.com
KLH Technology Solutions
0
karahooper
2/18/2004 6:55:35 PM
I tried that venue but no go. The problem is with the multi-line data. Excel
treat each line as a new row. I figure that Excel does not understant CR/LF
character. Perhap you can cheat that but how? Thanks

"Beth Melton" <bmelton@NoSpam4Memvps.org> wrote in message
news:uBHsC5k9DHA.1636@TK2MSFTNGP12.phx.gbl...
> Hi Ruby,
>
> The easiest way to do this is to copy the table to Excel, then either
> save the Excel workbook and import it into Access or copy/paste from
> Excel to Access.
>
> -- 
> Please post all follow-up questions to the newsgroup. Requests for
> assistance by email can not be acknowledged.
>
> ~~~~~~~~~~~~~~~
> Beth Melton
> Microsoft Office MVP
>
> Word FAQ: http://mvps.org/word
> TechTrax eZine: http://mousetrax.com/techtrax/
> MVP FAQ site: http://mvps.org/
>
>
> "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> > Hi, I was wondering if expert can give me some lite to convert my
> word table
> > into access database.
> >
> > Note: within each cell of my word table(s), some  has multi-line
> data in it.
> > In addition, there is one row containing picture(s) as well.
> >
> > So far, what I did is doing it manually for each word docs I have.
> >
> > Select Table
> > Convert Table to Text(I use ^ character for delimiter)
> > Save it to a text file(with char substitution, and CR/LF)
> > Then, Load them to Access DB.
> >
> > Is there an easier way to do this?
> >
> >
>
>


0
Ruby
2/18/2004 7:04:28 PM
"Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> Hi, I was wondering if expert can give me some lite to convert my word
table
> into access database.
>
> Note: within each cell of my word table(s), some  has multi-line data in
it.
> In addition, there is one row containing picture(s) as well.
>
> So far, what I did is doing it manually for each word docs I have.
>
> Select Table
> Convert Table to Text(I use ^ character for delimiter)
> Save it to a text file(with char substitution, and CR/LF)
> Then, Load them to Access DB.
>
> Is there an easier way to do this?


If you want to write code to do it, here is an example that just creates a
message box for each cell in the table showing the value.  This could be
converted to create a record in Access and you could adapt it to do this for
a number of documents, each with 1 or more tables.
Is this a route you might pursue?


Private Sub cmdExtract_Click()

    On Error GoTo Err_Handler

    Dim strPath As String
    Dim strValue As String
    Dim wdApp As Object 'Word.Application
    Dim wdDoc As Object 'Word.Document
    Dim wdTbl As Object 'Word.Table
    Dim wdRow As Object 'Word.Row
    Dim wdCol As Object 'Word.Column
    Dim wdCell As Object 'Word.Cell

    strPath = "C:\Example.doc"

    Set wdApp = CreateObject("Word.Application")

    Set wdDoc = wdApp.Documents.Open(strPath)

    If wdDoc.Tables.Count > 0 Then

        Set wdTbl = wdDoc.Tables(1)

        For Each wdRow In wdTbl.Rows

            For Each wdCol In wdTbl.Columns

                strValue = wdTbl.Cell(wdRow.Index, wdCol.Index).Range.Text

                If Len(strValue) > 2 Then
                    strValue = Left$(strValue, Len(strValue) - 2)
                Else
                    strValue = ""
                End If

                MsgBox strValue

            Next wdCol

        Next wdRow

    End If

    MsgBox "Done", vbInformation

Exit_Handler:

    On Error Resume Next

    If Not wdDoc Is Nothing Then
        wdDoc.Close
        Set wdDoc = Nothing
    End If

    If Not wdApp Is Nothing Then
        wdApp.Quit
        Set wdApp = Nothing
    End If

    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
    Resume Exit_Handler

End Sub






0
Fletcher
2/18/2004 7:34:49 PM
You could use Find/Replace in Word table:

Find: ^p
Replace: @@@ (or a character you are not using)

In Excel use Find/Replace:

Find: @@@
Replace: press <Alt 010> (Use the 10-key pad for this. Note that you
will not see anything)

Note that you may need to restart Excel to clear the search string in
the Replace text box.

-- 
Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/


"Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
news:c10d00$1bs5h1$1@ID-205437.news.uni-berlin.de...
> I tried that venue but no go. The problem is with the multi-line
data. Excel
> treat each line as a new row. I figure that Excel does not
understant CR/LF
> character. Perhap you can cheat that but how? Thanks
>
> "Beth Melton" <bmelton@NoSpam4Memvps.org> wrote in message
> news:uBHsC5k9DHA.1636@TK2MSFTNGP12.phx.gbl...
> > Hi Ruby,
> >
> > The easiest way to do this is to copy the table to Excel, then
either
> > save the Excel workbook and import it into Access or copy/paste
from
> > Excel to Access.
> >
> > -- 
> > Please post all follow-up questions to the newsgroup. Requests for
> > assistance by email can not be acknowledged.
> >
> > ~~~~~~~~~~~~~~~
> > Beth Melton
> > Microsoft Office MVP
> >
> > Word FAQ: http://mvps.org/word
> > TechTrax eZine: http://mousetrax.com/techtrax/
> > MVP FAQ site: http://mvps.org/
> >
> >
> > "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> > news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> > > Hi, I was wondering if expert can give me some lite to convert
my
> > word table
> > > into access database.
> > >
> > > Note: within each cell of my word table(s), some  has multi-line
> > data in it.
> > > In addition, there is one row containing picture(s) as well.
> > >
> > > So far, what I did is doing it manually for each word docs I
have.
> > >
> > > Select Table
> > > Convert Table to Text(I use ^ character for delimiter)
> > > Save it to a text file(with char substitution, and CR/LF)
> > > Then, Load them to Access DB.
> > >
> > > Is there an easier way to do this?
> > >
> > >
> >
> >
>
>


0
Beth
2/18/2004 9:23:29 PM
"Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message news:<c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de>...
> Hi, I was wondering if expert can give me some lite to convert my word table
> into access database.
> 
> Note: within each cell of my word table(s), some  has multi-line data in it.
> In addition, there is one row containing picture(s) as well.
> 
> So far, what I did is doing it manually for each word docs I have.
> 
> Select Table
> Convert Table to Text(I use ^ character for delimiter)
> Save it to a text file(with char substitution, and CR/LF)
> Then, Load them to Access DB.
> 
> Is there an easier way to do this?

Don't think so, unless you use code to do the conversions and then
imports for you.  If they're all in the same directory, you could use
the Dir function to loop through the contents of the directory,
process the Word file to create the importable file, and then after
those are done, import them into Access... but that's all I can think
of.
0
pietlinden
2/18/2004 10:32:34 PM
Can I do it programatically? I have about 100 word files with at least 3
tables inside them.

I don't know if the following is possible. Since we can save those word docs
into an html documents, can we parse the html docs and extract the data?
They are pretty structured <table>... <tr>...<td> ... </td>..</tr>...<table>
and some other formatting tags such as <b></b> etc...
Wish there are tools to do that.

Thanks

"Beth Melton" <bmelton@NoSpam4Memvps.org> wrote in message
news:O1$fAWm9DHA.1948@TK2MSFTNGP12.phx.gbl...
> You could use Find/Replace in Word table:
>
> Find: ^p
> Replace: @@@ (or a character you are not using)
>
> In Excel use Find/Replace:
>
> Find: @@@
> Replace: press <Alt 010> (Use the 10-key pad for this. Note that you
> will not see anything)
>
> Note that you may need to restart Excel to clear the search string in
> the Replace text box.
>
> -- 
> Please post all follow-up questions to the newsgroup. Requests for
> assistance by email can not be acknowledged.
>
> ~~~~~~~~~~~~~~~
> Beth Melton
> Microsoft Office MVP
>
> Word FAQ: http://mvps.org/word
> TechTrax eZine: http://mousetrax.com/techtrax/
> MVP FAQ site: http://mvps.org/
>
>
> "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> news:c10d00$1bs5h1$1@ID-205437.news.uni-berlin.de...
> > I tried that venue but no go. The problem is with the multi-line
> data. Excel
> > treat each line as a new row. I figure that Excel does not
> understant CR/LF
> > character. Perhap you can cheat that but how? Thanks
> >
> > "Beth Melton" <bmelton@NoSpam4Memvps.org> wrote in message
> > news:uBHsC5k9DHA.1636@TK2MSFTNGP12.phx.gbl...
> > > Hi Ruby,
> > >
> > > The easiest way to do this is to copy the table to Excel, then
> either
> > > save the Excel workbook and import it into Access or copy/paste
> from
> > > Excel to Access.
> > >
> > > -- 
> > > Please post all follow-up questions to the newsgroup. Requests for
> > > assistance by email can not be acknowledged.
> > >
> > > ~~~~~~~~~~~~~~~
> > > Beth Melton
> > > Microsoft Office MVP
> > >
> > > Word FAQ: http://mvps.org/word
> > > TechTrax eZine: http://mousetrax.com/techtrax/
> > > MVP FAQ site: http://mvps.org/
> > >
> > >
> > > "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> > > news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> > > > Hi, I was wondering if expert can give me some lite to convert
> my
> > > word table
> > > > into access database.
> > > >
> > > > Note: within each cell of my word table(s), some  has multi-line
> > > data in it.
> > > > In addition, there is one row containing picture(s) as well.
> > > >
> > > > So far, what I did is doing it manually for each word docs I
> have.
> > > >
> > > > Select Table
> > > > Convert Table to Text(I use ^ character for delimiter)
> > > > Save it to a text file(with char substitution, and CR/LF)
> > > > Then, Load them to Access DB.
> > > >
> > > > Is there an easier way to do this?
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
Ruby
2/19/2004 12:27:07 AM
Thanks Fletcher.

I might be able to follow your VBscript(is it? or is it a VB program that
need to be compiled?) program but will this program work for multi-line
cells? As I mentioned in my earlier message, I can't directly cut-and-paste
the table into the excel sheet cause excel treat each line of the multi-line
cell as another row of data. Somehow excel do not know how to handle cell
data with CR/LF character in it. Can we escape them?

Thanks again


"Fletcher Arnold" <fletch@home.com> wrote in message
news:c10eop$nsv$1@hercules.btinternet.com...
> "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> > Hi, I was wondering if expert can give me some lite to convert my word
> table
> > into access database.
> >
> > Note: within each cell of my word table(s), some  has multi-line data in
> it.
> > In addition, there is one row containing picture(s) as well.
> >
> > So far, what I did is doing it manually for each word docs I have.
> >
> > Select Table
> > Convert Table to Text(I use ^ character for delimiter)
> > Save it to a text file(with char substitution, and CR/LF)
> > Then, Load them to Access DB.
> >
> > Is there an easier way to do this?
>
>
> If you want to write code to do it, here is an example that just creates a
> message box for each cell in the table showing the value.  This could be
> converted to create a record in Access and you could adapt it to do this
for
> a number of documents, each with 1 or more tables.
> Is this a route you might pursue?
>
>
> Private Sub cmdExtract_Click()
>
>     On Error GoTo Err_Handler
>
>     Dim strPath As String
>     Dim strValue As String
>     Dim wdApp As Object 'Word.Application
>     Dim wdDoc As Object 'Word.Document
>     Dim wdTbl As Object 'Word.Table
>     Dim wdRow As Object 'Word.Row
>     Dim wdCol As Object 'Word.Column
>     Dim wdCell As Object 'Word.Cell
>
>     strPath = "C:\Example.doc"
>
>     Set wdApp = CreateObject("Word.Application")
>
>     Set wdDoc = wdApp.Documents.Open(strPath)
>
>     If wdDoc.Tables.Count > 0 Then
>
>         Set wdTbl = wdDoc.Tables(1)
>
>         For Each wdRow In wdTbl.Rows
>
>             For Each wdCol In wdTbl.Columns
>
>                 strValue = wdTbl.Cell(wdRow.Index, wdCol.Index).Range.Text
>
>                 If Len(strValue) > 2 Then
>                     strValue = Left$(strValue, Len(strValue) - 2)
>                 Else
>                     strValue = ""
>                 End If
>
>                 MsgBox strValue
>
>             Next wdCol
>
>         Next wdRow
>
>     End If
>
>     MsgBox "Done", vbInformation
>
> Exit_Handler:
>
>     On Error Resume Next
>
>     If Not wdDoc Is Nothing Then
>         wdDoc.Close
>         Set wdDoc = Nothing
>     End If
>
>     If Not wdApp Is Nothing Then
>         wdApp.Quit
>         Set wdApp = Nothing
>     End If
>
>     Exit Sub
>
> Err_Handler:
>     MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
>     Resume Exit_Handler
>
> End Sub
>
>
>
>
>
>


0
Ruby
2/19/2004 12:33:23 AM
"Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
news:c1108k$1d6crq$1@ID-205437.news.uni-berlin.de...
> Thanks Fletcher.
>
> I might be able to follow your VBscript(is it? or is it a VB program that
> need to be compiled?) program but will this program work for multi-line
> cells? As I mentioned in my earlier message, I can't directly
cut-and-paste
> the table into the excel sheet cause excel treat each line of the
multi-line
> cell as another row of data. Somehow excel do not know how to handle cell
> data with CR/LF character in it. Can we escape them?
>
> Thanks again


The code was actually written in Access with a form and a button named
"cmdExtract" and you could just cut and paste the code.  With very minor
modifications you could cut and paste this code into a normal text file and
save it as Whatever.vbs (vbs = visual basic script) and it would run.
Perhaps better still would be to re-write the file as an hta (html
application) which gives you a nice browser-based interface to work with but
is still simply plain text files.

I don't know if newgroup participants would want to write a complete html
application for nothing, but it would not take an experienced programmer
long.  You could post some more specific details about the transformation
you need, eg:

Cell A1: "Name"
Cell B1: "Address"
Cell A2: "Peter Smith"
Cell B2: "85 Station Road"
             "Newbury"
             "Berkshire"
             "United Kingdom"


How would this translate into your Access table.  What tables and field
names would you have?  I see elsewhere in the thread you will have up to 3
tables per doc.


Fletcher


0
Fletcher
2/19/2004 10:08:49 PM
Thanks Fletcher.

I have at most 3 tables per word documents. And for sure, I'd love to
understand the process instead of someone else writing the whole thing.

As I mentiond I have the following data in a word table(s)

Field1: Name        text(40)
Field2: <image>    jpeg
Field3: Desc          memo       - contain CR/LF
Field4: Note          memo       - contain CR/LF

e.g:

Field1            Field 2             Field3                         Field4
Exa One         <image1>       A candy in a glass        On top of
refrigerator
                                             jar.
next to the cabinet
Exa Tw0        <image2>        Car with broken          In the garage by
                                             windshield and flat       John
John's garden
                                             tire.
in Sarasota

Visually, in the word table, I have 3 rows, and 4 coloumns(12 cells). The
first row is the label, and the 2nd & 3rd row are the data. If you notice,
in field3 and field 4, the cell contain a multi-line(it has CR/LF at the end
of each line-- it is not the wrap around!).

Therefore, when  I cut and paste them in the excel table, I end up having
6 ROWS, and 4COLS(that is 24 cells). It treat each line of field3&field4 as
another record.

My question is, how do you write a vba in word(or access or excel) to
extract those data so I will represent the correct one. That is it should
result that I have 2 rows, 4colums of data. Do we have to somewhat escaped
CR/LF on each cells?

Thanks again.

"Fletcher Arnold" <fletch@home.com> wrote in message
news:c13c5h$ril$1@sparta.btinternet.com...
> "Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
> news:c1108k$1d6crq$1@ID-205437.news.uni-berlin.de...
> > Thanks Fletcher.
> >
> > I might be able to follow your VBscript(is it? or is it a VB program
that
> > need to be compiled?) program but will this program work for multi-line
> > cells? As I mentioned in my earlier message, I can't directly
> cut-and-paste
> > the table into the excel sheet cause excel treat each line of the
> multi-line
> > cell as another row of data. Somehow excel do not know how to handle
cell
> > data with CR/LF character in it. Can we escape them?
> >
> > Thanks again
>
>
> The code was actually written in Access with a form and a button named
> "cmdExtract" and you could just cut and paste the code.  With very minor
> modifications you could cut and paste this code into a normal text file
and
> save it as Whatever.vbs (vbs = visual basic script) and it would run.
> Perhaps better still would be to re-write the file as an hta (html
> application) which gives you a nice browser-based interface to work with
but
> is still simply plain text files.
>
> I don't know if newgroup participants would want to write a complete html
> application for nothing, but it would not take an experienced programmer
> long.  You could post some more specific details about the transformation
> you need, eg:
>
> Cell A1: "Name"
> Cell B1: "Address"
> Cell A2: "Peter Smith"
> Cell B2: "85 Station Road"
>              "Newbury"
>              "Berkshire"
>              "United Kingdom"
>
>
> How would this translate into your Access table.  What tables and field
> names would you have?  I see elsewhere in the thread you will have up to 3
> tables per doc.
>
>
> Fletcher
>
>


0
Ruby
2/20/2004 6:39:27 PM
Fletcher,

Your code to extract data from Word tables works great.  Do you also
have code to extract data from a table in the Header of a Word document?

Thanks,
   Norman Scheinin
   norman.b.scheinin@boeing.com 

Fletcher Arnold wrote:
> 
> "Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
> news:c1108k$1d6crq$1@ID-205437.news.uni-berlin.de...
> > Thanks Fletcher.
> >
> > I might be able to follow your VBscript(is it? or is it a VB program that
> > need to be compiled?) program but will this program work for multi-line
> > cells? As I mentioned in my earlier message, I can't directly
> cut-and-paste
> > the table into the excel sheet cause excel treat each line of the
> multi-line
> > cell as another row of data. Somehow excel do not know how to handle cell
> > data with CR/LF character in it. Can we escape them?
> >
> > Thanks again
> 
> The code was actually written in Access with a form and a button named
> "cmdExtract" and you could just cut and paste the code.  With very minor
> modifications you could cut and paste this code into a normal text file and
> save it as Whatever.vbs (vbs = visual basic script) and it would run.
> Perhaps better still would be to re-write the file as an hta (html
> application) which gives you a nice browser-based interface to work with but
> is still simply plain text files.
> 
> I don't know if newgroup participants would want to write a complete html
> application for nothing, but it would not take an experienced programmer
> long.  You could post some more specific details about the transformation
> you need, eg:
> 
> Cell A1: "Name"
> Cell B1: "Address"
> Cell A2: "Peter Smith"
> Cell B2: "85 Station Road"
>              "Newbury"
>              "Berkshire"
>              "United Kingdom"
> 
> How would this translate into your Access table.  What tables and field
> names would you have?  I see elsewhere in the thread you will have up to 3
> tables per doc.
> 
> Fletcher
0
Norman
2/25/2004 3:00:23 PM
Fletcher,

Your code to extract data from Word tables works great.  Do you also
have code to extract data from a table in the Header of a Word document?

Thanks,
   Norman Scheinin
   norman.b.scheinin@boeing.com 

Fletcher Arnold wrote:
> 
> "Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
> news:c1108k$1d6crq$1@ID-205437.news.uni-berlin.de...
> > Thanks Fletcher.
> >
> > I might be able to follow your VBscript(is it? or is it a VB program that
> > need to be compiled?) program but will this program work for multi-line
> > cells? As I mentioned in my earlier message, I can't directly
> cut-and-paste
> > the table into the excel sheet cause excel treat each line of the
> multi-line
> > cell as another row of data. Somehow excel do not know how to handle cell
> > data with CR/LF character in it. Can we escape them?
> >
> > Thanks again
> 
> The code was actually written in Access with a form and a button named
> "cmdExtract" and you could just cut and paste the code.  With very minor
> modifications you could cut and paste this code into a normal text file and
> save it as Whatever.vbs (vbs = visual basic script) and it would run.
> Perhaps better still would be to re-write the file as an hta (html
> application) which gives you a nice browser-based interface to work with but
> is still simply plain text files.
> 
> I don't know if newgroup participants would want to write a complete html
> application for nothing, but it would not take an experienced programmer
> long.  You could post some more specific details about the transformation
> you need, eg:
> 
> Cell A1: "Name"
> Cell B1: "Address"
> Cell A2: "Peter Smith"
> Cell B2: "85 Station Road"
>              "Newbury"
>              "Berkshire"
>              "United Kingdom"
> 
> How would this translate into your Access table.  What tables and field
> names would you have?  I see elsewhere in the thread you will have up to 3
> tables per doc.
> 
> Fletcher
0
Norman
2/25/2004 3:00:48 PM
Norman, would you share how you extract the word table data? Perhaps you can
give me some vba samples on how to do it. Thank much.

"Norman Scheinin" <norman.b.scheinin@boeing.com> wrote in message
news:403CB887.E7381347@boeing.com...
> Fletcher,
>
> Your code to extract data from Word tables works great.  Do you also
> have code to extract data from a table in the Header of a Word document?
>
> Thanks,
>    Norman Scheinin
>    norman.b.scheinin@boeing.com
>
> Fletcher Arnold wrote:
> >
> > "Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
> > news:c1108k$1d6crq$1@ID-205437.news.uni-berlin.de...
> > > Thanks Fletcher.
> > >
> > > I might be able to follow your VBscript(is it? or is it a VB program
that
> > > need to be compiled?) program but will this program work for
multi-line
> > > cells? As I mentioned in my earlier message, I can't directly
> > cut-and-paste
> > > the table into the excel sheet cause excel treat each line of the
> > multi-line
> > > cell as another row of data. Somehow excel do not know how to handle
cell
> > > data with CR/LF character in it. Can we escape them?
> > >
> > > Thanks again
> >
> > The code was actually written in Access with a form and a button named
> > "cmdExtract" and you could just cut and paste the code.  With very minor
> > modifications you could cut and paste this code into a normal text file
and
> > save it as Whatever.vbs (vbs = visual basic script) and it would run.
> > Perhaps better still would be to re-write the file as an hta (html
> > application) which gives you a nice browser-based interface to work with
but
> > is still simply plain text files.
> >
> > I don't know if newgroup participants would want to write a complete
html
> > application for nothing, but it would not take an experienced programmer
> > long.  You could post some more specific details about the
transformation
> > you need, eg:
> >
> > Cell A1: "Name"
> > Cell B1: "Address"
> > Cell A2: "Peter Smith"
> > Cell B2: "85 Station Road"
> >              "Newbury"
> >              "Berkshire"
> >              "United Kingdom"
> >
> > How would this translate into your Access table.  What tables and field
> > names would you have?  I see elsewhere in the thread you will have up to
3
> > tables per doc.
> >
> > Fletcher


0
Ruby
2/25/2004 5:48:31 PM
Fletcher, thanks for the code. I test it and it works, but there are a few
things I'd love to know how.

Instead of displaying it on the msg box, how would you insert it to the
database, say, the access or mysql database? Do I have to use ODBC? How?

Also, if one of the colums contain images, how one extract the image? Thanks
"Fletcher Arnold" <fletch@home.com> wrote in message
news:c10eop$nsv$1@hercules.btinternet.com...
> "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> news:c101h9$1cgk5l$1@ID-205437.news.uni-berlin.de...
> > Hi, I was wondering if expert can give me some lite to convert my word
> table
> > into access database.
> >
> > Note: within each cell of my word table(s), some  has multi-line data in
> it.
> > In addition, there is one row containing picture(s) as well.
> >
> > So far, what I did is doing it manually for each word docs I have.
> >
> > Select Table
> > Convert Table to Text(I use ^ character for delimiter)
> > Save it to a text file(with char substitution, and CR/LF)
> > Then, Load them to Access DB.
> >
> > Is there an easier way to do this?
>
>
> If you want to write code to do it, here is an example that just creates a
> message box for each cell in the table showing the value.  This could be
> converted to create a record in Access and you could adapt it to do this
for
> a number of documents, each with 1 or more tables.
> Is this a route you might pursue?
>
>
> Private Sub cmdExtract_Click()
>
>     On Error GoTo Err_Handler
>
>     Dim strPath As String
>     Dim strValue As String
>     Dim wdApp As Object 'Word.Application
>     Dim wdDoc As Object 'Word.Document
>     Dim wdTbl As Object 'Word.Table
>     Dim wdRow As Object 'Word.Row
>     Dim wdCol As Object 'Word.Column
>     Dim wdCell As Object 'Word.Cell
>
>     strPath = "C:\Example.doc"
>
>     Set wdApp = CreateObject("Word.Application")
>
>     Set wdDoc = wdApp.Documents.Open(strPath)
>
>     If wdDoc.Tables.Count > 0 Then
>
>         Set wdTbl = wdDoc.Tables(1)
>
>         For Each wdRow In wdTbl.Rows
>
>             For Each wdCol In wdTbl.Columns
>
>                 strValue = wdTbl.Cell(wdRow.Index, wdCol.Index).Range.Text
>
>                 If Len(strValue) > 2 Then
>                     strValue = Left$(strValue, Len(strValue) - 2)
>                 Else
>                     strValue = ""
>                 End If
>
>                 MsgBox strValue
>
>             Next wdCol
>
>         Next wdRow
>
>     End If
>
>     MsgBox "Done", vbInformation
>
> Exit_Handler:
>
>     On Error Resume Next
>
>     If Not wdDoc Is Nothing Then
>         wdDoc.Close
>         Set wdDoc = Nothing
>     End If
>
>     If Not wdApp Is Nothing Then
>         wdApp.Quit
>         Set wdApp = Nothing
>     End If
>
>     Exit Sub
>
> Err_Handler:
>     MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
>     Resume Exit_Handler
>
> End Sub
>
>
>
>
>
>


0
Ruby
2/25/2004 7:02:57 PM
"Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
news:c1irh5$1go9g6$1@ID-205437.news.uni-berlin.de...
> Fletcher, thanks for the code. I test it and it works, but there are a few
> things I'd love to know how.
>
> Instead of displaying it on the msg box, how would you insert it to the
> database, say, the access or mysql database? Do I have to use ODBC? How?


If you can give me a while, I will post a more complete example.

Fletcher


0
Fletcher
2/25/2004 8:08:42 PM
I tweaked Fletcher's code so you can write the Word table info to your
database...

Option Compare Database

Private Sub cmdExtract_Click()

    On Error GoTo Err_Handler

    Dim strPath As String
    Dim strValue As String
    Dim wdApp As Object 'Word.Application
    Dim wdDoc As Object 'Word.Document
    Dim wdTbl As Object 'Word.Table
    Dim wdRow As Object 'Word.Row
    Dim wdCol As Object 'Word.Column
    Dim wdCell As Object 'Word.Cell
    
    '--NEW STUFF
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("mytable", dbOpenDynamic)     
'"mytable" is the name of the table in your DB you'll be writing to
    strPath = "C:\Example.doc"

    Set wdApp = CreateObject("Word.Application")

    Set wdDoc = wdApp.Documents.Open(strPath)

    If wdDoc.Tables.Count > 0 Then

        Set wdTbl = wdDoc.Tables(1)

        '--Row is analogous to Access Record
        For Each wdRow In wdTbl.Rows
            '--Column is analogous to Access Table Field
            rs.AddNew
            For Each wdCol In wdTbl.Columns

                strValue = wdTbl.Cell(wdRow.Index,
wdCol.Index).Range.Text
                
                If Len(strValue) > 2 Then
                    strValue = Left$(strValue, Len(strValue) - 2)
                    rs.Fields(wdCol.Index) = strValue
                Else
                    strValue = ""
                End If
                
                MsgBox strValue
                
            Next wdCol
            
            'end of the column/record, so save it
            rs.Update
        Next wdRow

    End If
    
    rs.Close
    Set rs = Nothing
    MsgBox "Done", vbInformation

Exit_Handler:

    On Error Resume Next

    If Not wdDoc Is Nothing Then
        wdDoc.Close
        Set wdDoc = Nothing
    End If

    If Not wdApp Is Nothing Then
        wdApp.Quit
        Set wdApp = Nothing
    End If

    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    
    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
    Resume Exit_Handler

End Sub


(Apologies for bollixing up your code, Fletcher)

Pieter
0
pietlinden
2/26/2004 1:06:59 AM
Thank you, Fletcher.

"Fletcher Arnold" <fletch@home.com> wrote in message
news:c1ivca$e8t$1@sparta.btinternet.com...
> "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> news:c1irh5$1go9g6$1@ID-205437.news.uni-berlin.de...
> > Fletcher, thanks for the code. I test it and it works, but there are a
few
> > things I'd love to know how.
> >
> > Instead of displaying it on the msg box, how would you insert it to the
> > database, say, the access or mysql database? Do I have to use ODBC? How?
>
>
> If you can give me a while, I will post a more complete example.
>
> Fletcher
>
>


0
Ruby
2/26/2004 1:57:50 PM
"Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
news:c1ku11$1k12ov$1@ID-205437.news.uni-berlin.de...
> Thank you, Fletcher.
>
> "Fletcher Arnold" <fletch@home.com> wrote in message
> news:c1ivca$e8t$1@sparta.btinternet.com...
> > "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> > news:c1irh5$1go9g6$1@ID-205437.news.uni-berlin.de...
> > > Fletcher, thanks for the code. I test it and it works, but there are a
> few
> > > things I'd love to know how.
> > >
> > > Instead of displaying it on the msg box, how would you insert it to
the
> > > database, say, the access or mysql database? Do I have to use ODBC?
How?
> >
> >
> > If you can give me a while, I will post a more complete example.
> >
> > Fletcher


On the basis that the OP wanted to know how to get the code to run, I
thought I would offer a solution which only requires Notepad to create a
working application, provided certain system components are in place.  These
components should be present if you have a reasonably up-to-date Windows
installation - so it shouldn't need any fiddling around with.

To get the data into Access, open up any normal text editor (eg NotePad) and
copy and paste the code into a new file and save the file as "Xtractor.hta"
The .hta extension is for a html application.

The code was really just for a bit of fun - to try out these hta files.  One
plus side is that I can post plain text to the newsgroup, but there are a
number of downsides, including error handling.  If posting attachments were
allowed, I am sure an Access/VBA/DAO solution would be better than the
VBS/ADO code posted here.

Anyway, feel free to try it out and let me know how you get on.

Fletcher



Copy everything below the stars:
' ************************************************

<html>
<head>
<title>Table Extractor</title>
<script language="vbscript">
<!--

Sub DoMain()

 Dim lngMaxCols
 Dim strFolder
 Dim strDbName
 Dim strDbPath
 Dim strMsg

 strFolder = document.all.txtFolder.value

 If Right(strFolder,1) <> "\" Then
  strFolder = strFolder & "\"
 End If

 If Not FolderExists(strFolder) Then
  Msgbox "Non-existant Folder"
  Exit Sub
 End If

 If CountWordDocs(strFolder) < 1 Then
  Msgbox "No Word Docs"
  Exit Sub
 End If

 strDbName = document.all.txtDbName.value

 strDbPath = strFolder & strDbName

 If FileExists(strDbPath) = True Then

  strMsg = "The following file already exists:"
  strMsg = strMsg & vbCrLf
  strMsg = strMsg & "Do you want to overwrite it?"

  If Msgbox(strMsg, vbExclamation OR vbYesNoCancel) <> vbYes Then
   Exit Sub
  End If

  If Not DeleteFile(strDbPath) Then
   strMsg = "Error deleting file"
   strMsg = strMsg & vbCrLf
   strMsg = strMsg & "Check the file is not in use."
   MsgBox strMsg, vbCritical
   Exit Sub
  End If
 End If

 If IsNumeric(document.all.txtMaxColumns.value) Then
  lngMaxCols = Clng(document.all.txtMaxColumns.value)
 Else
  Msgbox "Columns"
  Exit Sub
 End If

 If (lngMaxCols < 1) OR  (lngMaxCols > 200) Then
  Msgbox "Columns"
  Exit sub
 End If

 If CreateDb(strDbPath, lngMaxCols) = False Then
  Msgbox "Error Creating Database", vbCritical
  Exit Sub
 End If

 ImportDocs strFolder, strDbPath, lngMaxCols

 strMsg = "Word tables successfully imported" & vbCrLf
 strMsg = strMsg & "Do you want to open the database?"

 If Msgbox(strMsg, vbInformation OR vbYesNoCancel) = vbYes Then
  StartDb(strDbPath)
 End If

End Sub




Function CleanString(strDirty)

 Dim strClean
 Dim lng

 strClean = Trim(strDirty)

 If Len(strClean) > 0 Then

  strClean = Replace(strClean, Chr(13), vbCrLf)

  For lng = Len(strClean) To 1 Step -1
   If Asc(Mid(strClean, lng, 1)) > 32 Then
    Exit For
   End If
  Next

  strClean = Left(strClean, lng)
 End If

 If Len(strClean) > 255 Then
  strClean = Left(strClean, 250) & "..."
 End If

 CleanString = strClean

End Function




Function FolderExists(strFolder)

 On Error Resume Next

 Dim fso
 Dim fld

 FolderExists = False

 Set fso = CreateObject("Scripting.FileSystemObject")

 Set fld = fso.GetFolder(strFolder)

 If Err.Number = 0 Then FolderExists = True

 Set fld = Nothing

 Set fso = Nothing

End Function




Function FileExists(strPath)

 On Error Resume Next

 Dim fso
 Dim fil

 FileExists = False

 Set fso = CreateObject("Scripting.FileSystemObject")

 Set fil = fso.GetFile(strPath)

 If Err.Number = 0 Then FileExists = True

 Set fil = Nothing

 Set fso = Nothing

End Function




Function DeleteFile(strPath)

 On Error Resume Next

 Dim fso

 DeleteFile = False

 Set fso = CreateObject("Scripting.FileSystemObject")

 fso.DeleteFile strPath, True

 If Err.Number = 0 Then DeleteFile = True

 Set fso = Nothing

End Function




Sub StartDb(strDbPath)

 Dim wshShell
 Dim lng

 Set wshShell = CreateObject("WScript.Shell")

 lng = wshShell.Run(strDbPath, 1)

 Set wshShell = Nothing

End Sub




Function CountWordDocs(strFolder)

 On Error Resume Next

 Dim fil
 Dim lng

 lng = 0

 Set fso = CreateObject("Scripting.FileSystemObject")

 Set fld = fso.GetFolder(strFolder)

 If Err.Number = 0 Then

  For each fil in fld.Files

   If Right(fil.Name, 4) = ".doc" Then
    lng = lng + 1
   End If
  Next

 End If

 Set fil = Nothing

 Set fld = Nothing

 Set fso = Nothing

 CountWordDocs = lng

End Function




Sub SetFolder()

 Dim strFolder

 strFolder = BrowseFolder("Choose a folder", &h0007, "c:\")

 If Len(strFolder)>0 Then
  document.all.txtFolder.value = strFolder
 End If

End Sub




Function BrowseFolder(sPrompt, BrowseInfo, root)

 On Error Resume Next

 Dim oShell
 Dim oFolder
 Dim iColonPos
 Dim oWshShell

 Set oShell = CreateObject("Shell.Application")

 Set oWshShell = CreateObject("WScript.Shell")

 Set oFolder = oShell.BrowseForFolder(&h0&, sPrompt, BrowseInfo, root)

 BrowseFolder = oFolder.ParentFolder.ParseName(oFolder.Title).Path

 If Err.Number <> 0 Then

  BrowseFolder = Null

  If oFolder.Title = "Desktop" Then
   BrowseFolder = oWshShell.SpecialFolders("Desktop")
      End If

  iColonPos = InStr(oFolder.Title, ":")

  If iColonPos > 0 Then
   BrowseFolder = Mid(oFolder.Title, iColonPos - 1, 2) & "\"
  End If
 End If

End Function




Function GetFolder()

 Dim objShell
 Dim objFolder

 set objShell = CreateObject("Shell.Application")

 set objFolder = objShell.BrowseForFolder(0, "Example", 0, "" )

                If (not objFolder is nothing) then
  GetFolder = "X" 'objFolder.Path
 Else
  GetFolder = ""
                End if

 set objFolder = Nothing
         set objShell = Nothing

End function




Sub ImportTables(wdDoc, rst, lngMaxCols)

 Dim wdTbl
 Dim lngTblNo
 Dim wdRow
 Dim wdCol
 Dim strValue

 lngTblNo = 0

 For Each wdTbl In wdDoc.Tables

  lngTblNo = lngTblNo + 1

  For Each wdRow In wdTbl.Rows

   rst.AddNew
   rst(1) = wdDoc.path & "\" & wdDoc.Name
   rst(2) = lngTblNo
   rst(3) = wdRow.Index

   For Each wdCol In wdTbl.Columns

    strValue = wdTbl.Cell(wdRow.Index, wdCol.Index).Range.Text

    strValue = CleanString(strValue)

    If Len(strValue) > 0 And (wdCol.Index < (lngMaxCols + 1)) Then
     rst(3 + wdCol.Index) = strValue
    End If

   Next

   rst.Update

  Next

 Next

 Set wdTbl = Nothing

End Sub




Sub ImportDocs(strFolder, strDbPath, lngMaxCols)

 Dim strSQL
 Dim strCnn
 Dim cnn
 Dim rst
 Dim wdApp
 Dim wdDoc
 Dim fso
 Dim fld
 Dim fil

 strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;"
 strCnn = strCnn & "Data Source=" & strDbPath

 Set cnn = CreateObject("ADODB.Connection")

 cnn.ConnectionString = strCnn

 cnn.Open

 strSQL = "SELECT * FROM tblWordTables"

 Set rst = CreateObject("ADODB.Recordset")

 rst.Open strSQL, cnn, 2, 3

 Set wdApp = CreateObject("Word.Application")

 Set fso = CreateObject("Scripting.FileSystemObject")

 Set fld = fso.GetFolder(strFolder)

 For Each fil In fld.Files

  If Right(fil.Name, 4) = ".doc" Then

   Set wdDoc = wdApp.Documents.Open(fil.Path)

   ImportTables wdDoc, rst, lngMaxCols

   wdDoc.Close

   Set wdDoc = Nothing

  End If
 Next

 Set fil = Nothing
 Set fld = Nothing
 Set fso = Nothing
 wdApp.Quit
 Set wdApp = Nothing
 rst.Close
 Set rst = Nothing
 cnn.Close
 Set cnn = Nothing
End Sub




Function CreateTextColumn(catCatalog, tblTable, strColumnName)

 On Error Resume Next

 Const adVarWChar = 202
 Dim col

 Set col = CreateObject("ADOX.Column")
 col.ParentCatalog = catCatalog
 col.Name = strColumnName
 col.Type = adVarWChar
 col.properties("Nullable").Value = True
 col.Properties("Jet OLEDB:Allow Zero Length").Value = False

 tblTable.Columns.Append col

 If Err.Number = 0 Then CreateTextColumn = True

 Set col = Nothing

End Function




Function CreateLongColumn(catCatalog, tblTable, strColumnName)

 On Error Resume Next

 Const adInteger = 3
 Dim col
 Dim idx

 Set col = CreateObject("ADOX.Column")
 col.ParentCatalog = catCatalog
 col.Name = strColumnName
 col.Type = adInteger

 tblTable.Columns.Append col

 If Err.Number = 0 Then

  Set col = Nothing

  Set idx = CreateObject("ADOX.Index")

  idx.Name = strColumnName

  idx.Unique = False

  Set col = CreateObject("ADOX.Column")

  col.Name = strColumnName

  idx.Columns.Append col

  tblTable.Indexes.Append idx

  If Err.Number = 0 Then

   CreateLongColumn = True

  End If

 End If

 Set idx = Nothing

 Set col = Nothing

End Function


Function CreatePrimaryKey(catCatalog, tblTable, strColumnName)

 On Error Resume Next

 Const adInteger = 3
 Dim col

 Set col = CreateObject("ADOX.Column")
 col.ParentCatalog = catCatalog
 col.Name = strColumnName
 col.Type = adInteger
 col.Properties("AutoIncrement").Value = True

 tblTable.Columns.Append col

 If Err.Number = 0 Then

  tblTable.Keys.Append "PrimaryKey", 1, strColumnName

  If Err.Number = 0 Then

   CreatePrimaryKey = True

  End If

 End If

 Set col = Nothing

End Function


Function CreateDb(strPath, lngMaxCols)

 On Error Resume Next
 Dim cat
 Dim tbl
 Dim col
 Dim str
 Dim strColName
 Dim lngColCount

 CreateDb = True

 Set cat = CreateObject("ADOX.Catalog")

 str = "Provider=Microsoft.Jet.OLEDB.4.0;"
 str = str & "Jet OLEDB:Engine Type=5;"
 str = str & "Data Source=" & strPath

 cat.Create str

 If Err.Number <> 0 Then Exit Function

 Set tbl = CreateObject("ADOX.Table")

 tbl.ParentCatalog = cat

 tbl.Name = "tblWordTables"

 If Not CreatePrimaryKey(cat, tbl, "ID") Then Exit Function

 If Not CreateTextColumn(cat, tbl, "DocPath") Then Exit Function

 If Not CreateLongColumn(cat, tbl, "TableNo") Then Exit Function

 If Not CreateLongColumn(cat, tbl, "RowNo") Then Exit Function

 For lngColCount = 1 to lngMaxCols

  strColName = Cstr(1000 + lngColCount)

  strColName = "Column" & Mid(strColName, 2)

  If Not CreateTextColumn(cat, tbl, strColName) Then
   Exit Function
  End If
 Next

 cat.Tables.Append tbl

 If Err.Number = 0 Then
  CreateDb = True
 Else
  Msgbox Err.Description
 End If

 Set tbl = Nothing

 Set cat = Nothing

End Function

-->
</script>

<body bgcolor="#CCCCFF">

<table>
<th colspan="2" align="center">Extract Tables From Microsoft Word</th>

<tr><td>&nbsp;</td><td>&nbsp;</td></tr>

<tr>
<td>Document Folder</td>
<td><input type="text" id="txtFolder" value="C:\"</td>
</tr>

<tr>
<td>Database Name</td>
<td><input type="text" id="txtDbName" value="WordTables.mdb"</td>
</tr>

<tr>
<td>Maximum Columns</td>
<td><input type="text" id="txtMaxColumns" value="10"</td>
</tr>

<tr><td>&nbsp;</td><td>&nbsp;</td></tr>

<tr>
<td><input type="button" name="cmdFolder" value="Change Folder"
onclick="SetFolder()"></td>
<td><input type="button" name="cmdImport" value="Import Tables"
onclick="DoMain()"></td>
</tr>

</table>

</body>
</html>



0
Fletcher
2/26/2004 5:09:15 PM
"Pieter Linden" <pietlinden@hotmail.com> wrote in message
news:bf31e41b.0402251706.3d085d8d@posting.google.com...
> I tweaked Fletcher's code so you can write the Word table info to your
> database...
>
<snip>
> (Apologies for bollixing up your code, Fletcher)
>
> Pieter


Hi Pieter
By all means feel free.  It's an open forum and is sometimes a relief when
others answer your posts.  If you are interested in what I finally did with
it have a look at my post elsewhere in this thread.  This shows an html
application with VBS/ADO which was done to see if I could post a plain text
file with all the necessary code.


Fletcher


0
Fletcher
2/26/2004 5:15:33 PM
"Norman Scheinin" <norman.b.scheinin@boeing.com> wrote in message
news:403CB8A0.B729F19C@boeing.com...
> Fletcher,
>
> Your code to extract data from Word tables works great.  Do you also
> have code to extract data from a table in the Header of a Word document?
>
> Thanks,
>    Norman Scheinin
>    norman.b.scheinin@boeing.com

Hi Norman
It is simply a case of navigating your way through Word's object model to
get at what you want.  I am not an expert on long documents created in Word,
but I tested this little snippet to get some info out of a table in the
header of a simple Word document.
If you have more complicated documents, headers can be different for
odd/even pages, for different sections, etc but this might give you a start
so you can start to have a more thorough investigation of the Word object
model.

Set wdHdr = wdDoc.Sections(1).Headers(wdHeaderFooterPrimary)

If wdHdr.Range.Tables.Count > 0 Then
    Set wdTbl = wdHdr.Range.Tables(1)
    MsgBox wdTbl.Cell(2, 2).Range.Text
    Set wdTbl = Nothing
End If

Set wdHdr = Nothing


Fletcher


0
Fletcher
2/26/2004 10:45:37 PM
Hi Fletcher, thanks for posting the code.
How do you execute Xtractor.hta file?
When use IE6, it prompt me with a form(which I filed in) but without
'submit' button to execute. Perhaps I overlook something ...

Thanks again.

"Fletcher Arnold" <fletch@home.com> wrote in message
news:c1l97r$fe$1@hercules.btinternet.com...
> "Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
> news:c1ku11$1k12ov$1@ID-205437.news.uni-berlin.de...
> > Thank you, Fletcher.
> >
> > "Fletcher Arnold" <fletch@home.com> wrote in message
> > news:c1ivca$e8t$1@sparta.btinternet.com...
> > > "Ruby Tuesday" <rubytuzdayz@yahoo.com> wrote in message
> > > news:c1irh5$1go9g6$1@ID-205437.news.uni-berlin.de...
> > > > Fletcher, thanks for the code. I test it and it works, but there are
a
> > few
> > > > things I'd love to know how.
> > > >
> > > > Instead of displaying it on the msg box, how would you insert it to
> the
> > > > database, say, the access or mysql database? Do I have to use ODBC?
> How?
> > >
> > >
> > > If you can give me a while, I will post a more complete example.
> > >
> > > Fletcher
>
>
> On the basis that the OP wanted to know how to get the code to run, I
> thought I would offer a solution which only requires Notepad to create a
> working application, provided certain system components are in place.
These
> components should be present if you have a reasonably up-to-date Windows
> installation - so it shouldn't need any fiddling around with.
>
> To get the data into Access, open up any normal text editor (eg NotePad)
and
> copy and paste the code into a new file and save the file as
"Xtractor.hta"
> The .hta extension is for a html application.
>
> The code was really just for a bit of fun - to try out these hta files.
One
> plus side is that I can post plain text to the newsgroup, but there are a
> number of downsides, including error handling.  If posting attachments
were
> allowed, I am sure an Access/VBA/DAO solution would be better than the
> VBS/ADO code posted here.
>
> Anyway, feel free to try it out and let me know how you get on.
>
> Fletcher
>
>
>
> Copy everything below the stars:
> ' ************************************************
>
> <html>
> <head>
> <title>Table Extractor</title>
> <script language="vbscript">
> <!--
>
> Sub DoMain()
>
>  Dim lngMaxCols
>  Dim strFolder
>  Dim strDbName
>  Dim strDbPath
>  Dim strMsg
>
>  strFolder = document.all.txtFolder.value
>
>  If Right(strFolder,1) <> "\" Then
>   strFolder = strFolder & "\"
>  End If
>
>  If Not FolderExists(strFolder) Then
>   Msgbox "Non-existant Folder"
>   Exit Sub
>  End If
>
>  If CountWordDocs(strFolder) < 1 Then
>   Msgbox "No Word Docs"
>   Exit Sub
>  End If
>
>  strDbName = document.all.txtDbName.value
>
>  strDbPath = strFolder & strDbName
>
>  If FileExists(strDbPath) = True Then
>
>   strMsg = "The following file already exists:"
>   strMsg = strMsg & vbCrLf
>   strMsg = strMsg & "Do you want to overwrite it?"
>
>   If Msgbox(strMsg, vbExclamation OR vbYesNoCancel) <> vbYes Then
>    Exit Sub
>   End If
>
>   If Not DeleteFile(strDbPath) Then
>    strMsg = "Error deleting file"
>    strMsg = strMsg & vbCrLf
>    strMsg = strMsg & "Check the file is not in use."
>    MsgBox strMsg, vbCritical
>    Exit Sub
>   End If
>  End If
>
>  If IsNumeric(document.all.txtMaxColumns.value) Then
>   lngMaxCols = Clng(document.all.txtMaxColumns.value)
>  Else
>   Msgbox "Columns"
>   Exit Sub
>  End If
>
>  If (lngMaxCols < 1) OR  (lngMaxCols > 200) Then
>   Msgbox "Columns"
>   Exit sub
>  End If
>
>  If CreateDb(strDbPath, lngMaxCols) = False Then
>   Msgbox "Error Creating Database", vbCritical
>   Exit Sub
>  End If
>
>  ImportDocs strFolder, strDbPath, lngMaxCols
>
>  strMsg = "Word tables successfully imported" & vbCrLf
>  strMsg = strMsg & "Do you want to open the database?"
>
>  If Msgbox(strMsg, vbInformation OR vbYesNoCancel) = vbYes Then
>   StartDb(strDbPath)
>  End If
>
> End Sub
>
>
>
>
> Function CleanString(strDirty)
>
>  Dim strClean
>  Dim lng
>
>  strClean = Trim(strDirty)
>
>  If Len(strClean) > 0 Then
>
>   strClean = Replace(strClean, Chr(13), vbCrLf)
>
>   For lng = Len(strClean) To 1 Step -1
>    If Asc(Mid(strClean, lng, 1)) > 32 Then
>     Exit For
>    End If
>   Next
>
>   strClean = Left(strClean, lng)
>  End If
>
>  If Len(strClean) > 255 Then
>   strClean = Left(strClean, 250) & "..."
>  End If
>
>  CleanString = strClean
>
> End Function
>
>
>
>
> Function FolderExists(strFolder)
>
>  On Error Resume Next
>
>  Dim fso
>  Dim fld
>
>  FolderExists = False
>
>  Set fso = CreateObject("Scripting.FileSystemObject")
>
>  Set fld = fso.GetFolder(strFolder)
>
>  If Err.Number = 0 Then FolderExists = True
>
>  Set fld = Nothing
>
>  Set fso = Nothing
>
> End Function
>
>
>
>
> Function FileExists(strPath)
>
>  On Error Resume Next
>
>  Dim fso
>  Dim fil
>
>  FileExists = False
>
>  Set fso = CreateObject("Scripting.FileSystemObject")
>
>  Set fil = fso.GetFile(strPath)
>
>  If Err.Number = 0 Then FileExists = True
>
>  Set fil = Nothing
>
>  Set fso = Nothing
>
> End Function
>
>
>
>
> Function DeleteFile(strPath)
>
>  On Error Resume Next
>
>  Dim fso
>
>  DeleteFile = False
>
>  Set fso = CreateObject("Scripting.FileSystemObject")
>
>  fso.DeleteFile strPath, True
>
>  If Err.Number = 0 Then DeleteFile = True
>
>  Set fso = Nothing
>
> End Function
>
>
>
>
> Sub StartDb(strDbPath)
>
>  Dim wshShell
>  Dim lng
>
>  Set wshShell = CreateObject("WScript.Shell")
>
>  lng = wshShell.Run(strDbPath, 1)
>
>  Set wshShell = Nothing
>
> End Sub
>
>
>
>
> Function CountWordDocs(strFolder)
>
>  On Error Resume Next
>
>  Dim fil
>  Dim lng
>
>  lng = 0
>
>  Set fso = CreateObject("Scripting.FileSystemObject")
>
>  Set fld = fso.GetFolder(strFolder)
>
>  If Err.Number = 0 Then
>
>   For each fil in fld.Files
>
>    If Right(fil.Name, 4) = ".doc" Then
>     lng = lng + 1
>    End If
>   Next
>
>  End If
>
>  Set fil = Nothing
>
>  Set fld = Nothing
>
>  Set fso = Nothing
>
>  CountWordDocs = lng
>
> End Function
>
>
>
>
> Sub SetFolder()
>
>  Dim strFolder
>
>  strFolder = BrowseFolder("Choose a folder", &h0007, "c:\")
>
>  If Len(strFolder)>0 Then
>   document.all.txtFolder.value = strFolder
>  End If
>
> End Sub
>
>
>
>
> Function BrowseFolder(sPrompt, BrowseInfo, root)
>
>  On Error Resume Next
>
>  Dim oShell
>  Dim oFolder
>  Dim iColonPos
>  Dim oWshShell
>
>  Set oShell = CreateObject("Shell.Application")
>
>  Set oWshShell = CreateObject("WScript.Shell")
>
>  Set oFolder = oShell.BrowseForFolder(&h0&, sPrompt, BrowseInfo, root)
>
>  BrowseFolder = oFolder.ParentFolder.ParseName(oFolder.Title).Path
>
>  If Err.Number <> 0 Then
>
>   BrowseFolder = Null
>
>   If oFolder.Title = "Desktop" Then
>    BrowseFolder = oWshShell.SpecialFolders("Desktop")
>       End If
>
>   iColonPos = InStr(oFolder.Title, ":")
>
>   If iColonPos > 0 Then
>    BrowseFolder = Mid(oFolder.Title, iColonPos - 1, 2) & "\"
>   End If
>  End If
>
> End Function
>
>
>
>
> Function GetFolder()
>
>  Dim objShell
>  Dim objFolder
>
>  set objShell = CreateObject("Shell.Application")
>
>  set objFolder = objShell.BrowseForFolder(0, "Example", 0, "" )
>
>                 If (not objFolder is nothing) then
>   GetFolder = "X" 'objFolder.Path
>  Else
>   GetFolder = ""
>                 End if
>
>  set objFolder = Nothing
>          set objShell = Nothing
>
> End function
>
>
>
>
> Sub ImportTables(wdDoc, rst, lngMaxCols)
>
>  Dim wdTbl
>  Dim lngTblNo
>  Dim wdRow
>  Dim wdCol
>  Dim strValue
>
>  lngTblNo = 0
>
>  For Each wdTbl In wdDoc.Tables
>
>   lngTblNo = lngTblNo + 1
>
>   For Each wdRow In wdTbl.Rows
>
>    rst.AddNew
>    rst(1) = wdDoc.path & "\" & wdDoc.Name
>    rst(2) = lngTblNo
>    rst(3) = wdRow.Index
>
>    For Each wdCol In wdTbl.Columns
>
>     strValue = wdTbl.Cell(wdRow.Index, wdCol.Index).Range.Text
>
>     strValue = CleanString(strValue)
>
>     If Len(strValue) > 0 And (wdCol.Index < (lngMaxCols + 1)) Then
>      rst(3 + wdCol.Index) = strValue
>     End If
>
>    Next
>
>    rst.Update
>
>   Next
>
>  Next
>
>  Set wdTbl = Nothing
>
> End Sub
>
>
>
>
> Sub ImportDocs(strFolder, strDbPath, lngMaxCols)
>
>  Dim strSQL
>  Dim strCnn
>  Dim cnn
>  Dim rst
>  Dim wdApp
>  Dim wdDoc
>  Dim fso
>  Dim fld
>  Dim fil
>
>  strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;"
>  strCnn = strCnn & "Data Source=" & strDbPath
>
>  Set cnn = CreateObject("ADODB.Connection")
>
>  cnn.ConnectionString = strCnn
>
>  cnn.Open
>
>  strSQL = "SELECT * FROM tblWordTables"
>
>  Set rst = CreateObject("ADODB.Recordset")
>
>  rst.Open strSQL, cnn, 2, 3
>
>  Set wdApp = CreateObject("Word.Application")
>
>  Set fso = CreateObject("Scripting.FileSystemObject")
>
>  Set fld = fso.GetFolder(strFolder)
>
>  For Each fil In fld.Files
>
>   If Right(fil.Name, 4) = ".doc" Then
>
>    Set wdDoc = wdApp.Documents.Open(fil.Path)
>
>    ImportTables wdDoc, rst, lngMaxCols
>
>    wdDoc.Close
>
>    Set wdDoc = Nothing
>
>   End If
>  Next
>
>  Set fil = Nothing
>  Set fld = Nothing
>  Set fso = Nothing
>  wdApp.Quit
>  Set wdApp = Nothing
>  rst.Close
>  Set rst = Nothing
>  cnn.Close
>  Set cnn = Nothing
> End Sub
>
>
>
>
> Function CreateTextColumn(catCatalog, tblTable, strColumnName)
>
>  On Error Resume Next
>
>  Const adVarWChar = 202
>  Dim col
>
>  Set col = CreateObject("ADOX.Column")
>  col.ParentCatalog = catCatalog
>  col.Name = strColumnName
>  col.Type = adVarWChar
>  col.properties("Nullable").Value = True
>  col.Properties("Jet OLEDB:Allow Zero Length").Value = False
>
>  tblTable.Columns.Append col
>
>  If Err.Number = 0 Then CreateTextColumn = True
>
>  Set col = Nothing
>
> End Function
>
>
>
>
> Function CreateLongColumn(catCatalog, tblTable, strColumnName)
>
>  On Error Resume Next
>
>  Const adInteger = 3
>  Dim col
>  Dim idx
>
>  Set col = CreateObject("ADOX.Column")
>  col.ParentCatalog = catCatalog
>  col.Name = strColumnName
>  col.Type = adInteger
>
>  tblTable.Columns.Append col
>
>  If Err.Number = 0 Then
>
>   Set col = Nothing
>
>   Set idx = CreateObject("ADOX.Index")
>
>   idx.Name = strColumnName
>
>   idx.Unique = False
>
>   Set col = CreateObject("ADOX.Column")
>
>   col.Name = strColumnName
>
>   idx.Columns.Append col
>
>   tblTable.Indexes.Append idx
>
>   If Err.Number = 0 Then
>
>    CreateLongColumn = True
>
>   End If
>
>  End If
>
>  Set idx = Nothing
>
>  Set col = Nothing
>
> End Function
>
>
> Function CreatePrimaryKey(catCatalog, tblTable, strColumnName)
>
>  On Error Resume Next
>
>  Const adInteger = 3
>  Dim col
>
>  Set col = CreateObject("ADOX.Column")
>  col.ParentCatalog = catCatalog
>  col.Name = strColumnName
>  col.Type = adInteger
>  col.Properties("AutoIncrement").Value = True
>
>  tblTable.Columns.Append col
>
>  If Err.Number = 0 Then
>
>   tblTable.Keys.Append "PrimaryKey", 1, strColumnName
>
>   If Err.Number = 0 Then
>
>    CreatePrimaryKey = True
>
>   End If
>
>  End If
>
>  Set col = Nothing
>
> End Function
>
>
> Function CreateDb(strPath, lngMaxCols)
>
>  On Error Resume Next
>  Dim cat
>  Dim tbl
>  Dim col
>  Dim str
>  Dim strColName
>  Dim lngColCount
>
>  CreateDb = True
>
>  Set cat = CreateObject("ADOX.Catalog")
>
>  str = "Provider=Microsoft.Jet.OLEDB.4.0;"
>  str = str & "Jet OLEDB:Engine Type=5;"
>  str = str & "Data Source=" & strPath
>
>  cat.Create str
>
>  If Err.Number <> 0 Then Exit Function
>
>  Set tbl = CreateObject("ADOX.Table")
>
>  tbl.ParentCatalog = cat
>
>  tbl.Name = "tblWordTables"
>
>  If Not CreatePrimaryKey(cat, tbl, "ID") Then Exit Function
>
>  If Not CreateTextColumn(cat, tbl, "DocPath") Then Exit Function
>
>  If Not CreateLongColumn(cat, tbl, "TableNo") Then Exit Function
>
>  If Not CreateLongColumn(cat, tbl, "RowNo") Then Exit Function
>
>  For lngColCount = 1 to lngMaxCols
>
>   strColName = Cstr(1000 + lngColCount)
>
>   strColName = "Column" & Mid(strColName, 2)
>
>   If Not CreateTextColumn(cat, tbl, strColName) Then
>    Exit Function
>   End If
>  Next
>
>  cat.Tables.Append tbl
>
>  If Err.Number = 0 Then
>   CreateDb = True
>  Else
>   Msgbox Err.Description
>  End If
>
>  Set tbl = Nothing
>
>  Set cat = Nothing
>
> End Function
>
> -->
> </script>
>
> <body bgcolor="#CCCCFF">
>
> <table>
> <th colspan="2" align="center">Extract Tables From Microsoft Word</th>
>
> <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
>
> <tr>
> <td>Document Folder</td>
> <td><input type="text" id="txtFolder" value="C:\"</td>
> </tr>
>
> <tr>
> <td>Database Name</td>
> <td><input type="text" id="txtDbName" value="WordTables.mdb"</td>
> </tr>
>
> <tr>
> <td>Maximum Columns</td>
> <td><input type="text" id="txtMaxColumns" value="10"</td>
> </tr>
>
> <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
>
> <tr>
> <td><input type="button" name="cmdFolder" value="Change Folder"
> onclick="SetFolder()"></td>
> <td><input type="button" name="cmdImport" value="Import Tables"
> onclick="DoMain()"></td>
> </tr>
>
> </table>
>
> </body>
> </html>
>
>
>


0
Ruby
2/27/2004 6:44:01 AM
"Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
news:c1movi$1kje1r$1@ID-205437.news.uni-berlin.de...
> Hi Fletcher, thanks for posting the code.
> How do you execute Xtractor.hta file?
> When use IE6, it prompt me with a form(which I filed in) but without
> 'submit' button to execute. Perhaps I overlook something ...
>
> Thanks again.
>

You fill in 3 things:

1:  The folder where your Word documents are stored.  The program imports
all tables from all documents in that folder, so if you only want a few
documents done, you could move them to their own folder first.  This will
also be the folder where the MS Access database is created.  You can type
the folder path e.g C:\MyStuff or use the "change folder" button.

2:  The name of the database - which might as well be left as
"WordTables.mdb"

3:  The maximum number of columns which will be imported from each Word
table into the database.

**The final step is to press the "Import Tables" button which should firstly
create a new Access db for the results (or prompt you to overwrite an
existing one).  It then takes each table in each Word document in the
specified folder and imports the contents into the new database.  When it
has finished, it gives you the chance to open the database immediately.

That is the theory, anyway and it did work perfectly on my machine, although
it does take a while for large numbers of tables.  Or did you already press
the import button and something didn't work?


Fletcher





0
Fletcher
2/27/2004 7:42:47 AM
Hi Fletcher,
    when I ran the Xtractor.hta, it and set the correct folder path and the
access db, it keeps telling me that the folder is 'Non-Existant'.

I also try to hardcoded the path, and came back the same error. Did I
overlook something? Thanks
"Fletcher Arnold" <fletch@home.com> wrote in message
news:c1msdn$khm$1@titan.btinternet.com...
>
> "Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
> news:c1movi$1kje1r$1@ID-205437.news.uni-berlin.de...
> > Hi Fletcher, thanks for posting the code.
> > How do you execute Xtractor.hta file?
> > When use IE6, it prompt me with a form(which I filed in) but without
> > 'submit' button to execute. Perhaps I overlook something ...
> >
> > Thanks again.
> >
>
> You fill in 3 things:
>
> 1:  The folder where your Word documents are stored.  The program imports
> all tables from all documents in that folder, so if you only want a few
> documents done, you could move them to their own folder first.  This will
> also be the folder where the MS Access database is created.  You can type
> the folder path e.g C:\MyStuff or use the "change folder" button.
>
> 2:  The name of the database - which might as well be left as
> "WordTables.mdb"
>
> 3:  The maximum number of columns which will be imported from each Word
> table into the database.
>
> **The final step is to press the "Import Tables" button which should
firstly
> create a new Access db for the results (or prompt you to overwrite an
> existing one).  It then takes each table in each Word document in the
> specified folder and imports the contents into the new database.  When it
> has finished, it gives you the chance to open the database immediately.
>
> That is the theory, anyway and it did work perfectly on my machine,
although
> it does take a while for large numbers of tables.  Or did you already
press
> the import button and something didn't work?
>
>
> Fletcher
>
>
>
>
>


0
Ruby
2/27/2004 3:48:37 PM
"Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
news:c1nosn$1kr92q$1@ID-205437.news.uni-berlin.de...
> Hi Fletcher,
>     when I ran the Xtractor.hta, it and set the correct folder path and
the
> access db, it keeps telling me that the folder is 'Non-Existant'.


Are you giving the name of an existing folder where the Word documents are?
What happens if you place an example Word document in the root folder (C:\)
so you do not touch the default settings of the form?

If you want to speed up communications, let me know an e-mail address and
I'll send you mine.


Fletcher


0
Fletcher
2/27/2004 3:55:43 PM
Nope. No luck. I gives me the same error.

here's my email:
-- please remove the dash(-) and colon(:) in the email address:

    ruby-tuz:daiz@yahoo.com

Thanks



"Fletcher Arnold" <fletch@home.com> wrote in message
news:c1np9v$t6n$1@hercules.btinternet.com...
> "Ruby Tuesday" <rubytuezdayz@yahoo.com> wrote in message
> news:c1nosn$1kr92q$1@ID-205437.news.uni-berlin.de...
> > Hi Fletcher,
> >     when I ran the Xtractor.hta, it and set the correct folder path and
> the
> > access db, it keeps telling me that the folder is 'Non-Existant'.
>
>
> Are you giving the name of an existing folder where the Word documents
are?
> What happens if you place an example Word document in the root folder
(C:\)
> so you do not touch the default settings of the form?
>
> If you want to speed up communications, let me know an e-mail address and
> I'll send you mine.
>
>
> Fletcher
>
>


0
Ruby
2/27/2004 4:45:32 PM
Fletcher,
just a thought... You could use the BrowseFolder API from mvps.org 
and then just let the user browse for the folder... Makes it a bit
more flexible, that's all.
0
pietlinden
2/27/2004 9:01:08 PM
Reply:

Similar Artilces:

problem with BOLD. ITALIC...using Word as editor
I compose a msg using Outlook. Word is the editor. I set a word in bold ot italic. I send msg to myselg, reading in Outlook. The bold or italic is gone. What to do? Thanks, Leo Are you sending the message as text only? If so all formatting will disappear. If you want to retain your formatting you'll need to send your messages as HTML. You can check this in your Outlook options. -- Martin Sketchley | www.msketchley.pwp.blueyonder.co.uk (remove EGGBEANSANDSPAM to reply by e-mail) ...

Printing WORD docs from a form
I have a button on a form and I want it to print a Word mail merge document that contains data from the database that is linked to my application. I'm using the following code but always get a 5922 error - Word can't open the source... All variable names contain valid data, so that's NOT the problem. With appWord .Visible = True .Documents.Open pathdocname With .ActiveDocument.MailMerge .MainDocumentType = wdFormLetters strConnection = "DSN=MS Access Databases;" _ & "DB...

Access 2000 Package and Deployment Wizard problem!
I have created an Access 2000 application that I now wish to distribute to users as an MDE file. The Access 2000 Package and Deployment Wizard does a fine job of storing the relevant installation files on a CD, exploiting A2K Runtime. However, I would prefer to let users download my application from a website. The Package and Deployment Wizard offers a 'Web Publishing' deployment option that appears to address this need. My only problem is that it doesn't work for me. I get the following error message, Unexpected error number 48 has occurred: File not found: WebPost.dll I...

Windows XP: Open file in read-only: Word, Excel, Visio
I'm often sharing files on a network drive with someone. Often, one of us knows that we don't need to open a file for modification, and we only need read-access. Is there a way to open a file in read-only mode so that when someone does need to open it for modification, the read-only user is not blocking that? If there is not a way to do this native to Windows XP, is there a way to do it from the three applications that we use most, namely Word, Excel, Visio? All are 2003 versions. Thx. I used to save files like this in "read only recommended" mode. File|SaveAs|Tools|Ge...

Not enough memory error trying to open a Word X file
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Error message: &quot;There is not enough memory or disk space available to complete the operation&quot; when trying to open a Word X file in Office 2008 in Leopard or Snow Leopard on iMac that works fine in Word X despite resaving a new version. Any tips? Not enough information to answer. Check that OS X and Office 2008 have the latest updates applied. Use Disk Utility to Repair Permissions. Check the access your user ID has to the system Temporary folder (don't worry about this if you ar...

File Access is Denied....Argh!
Hi, I purchased a Sony Vaio laptop. Included is a trial edition of MS Office. I tried to import my .PST file that I copied from my last computer. I received the message: "Access is denied. You do not have permission required to access the file F:outlook.pst." Of course, there was no help file to explain what to do next or solve the problem. So, I went to the MS website and signed up for the MS Passport program, against my wishes; but it was required to get service to solve this problem. And then after all the hoops they made me jump through, they tell me that the pro...

Upsize table to SQL2K
I'm upsizing the BE of an A97 DB to SQL2K. One of the tables which appears to have upsized correctly, i.e., the text field sizes are the same as they were in the original DB, is giving me an error when I open the linked table in Access: "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. (Error 3163)" When I open the table in Design view in Access, all of the text fields are set to a length of 255. But in the original table, *most* of the text fields were set to a length of 50. I've determined the offending field is...

List of CRM tables?
Hi All, I'm just starting out altering some SQL queries in excel to make the reports more relevant and find myself wanting to pull data from two tables. Does anybody know where there is a list of tables for reference of failing that, which table the property opportunity.partneridname sits in? Thanks D For opportunity, you can look into opportunitybase and opportunitybaseextension table. But you can use the view "opportunity". Hope this helps. Darren Liu Crowe Chizek and Company http://www.crowecrm.com On Feb 22, 12:23 pm, "Dempsey J" <xfo...@hotmail.com&g...

ACTIVATE ACCESS from EXCEL?
Can I activate an Access session (database is already open) from an Excel macro? Here some code from MVP John Green that will activate a certain database in Access. Watch the version issue. This should get you started: http://tinyurl.com/66r5x HTH Jason Atlanta, GA >-----Original Message----- >Can I activate an Access session (database is already >open) from an Excel macro? >. > ...

Cannot upgrade to Word 2007 from Word 2002 as word crashes when tr
We have a word add in that programatically inserts an auto text table then adds rows to it. This works ok in Word 2002 but occasionally crashes in Word 2007 - this is not a consistent crash when we carry out the same operation it happens in no specific sequence. The only place we are able to track it down to is the auto text table inserting rows. Word itself is crashing. We are using COM AddIn written in VB6. Does anyone know of any key differences/issues between Word 2002 and Word 2007 that could cause such an error. Or is there a better way to attach a debugger/word debugg...

Word 2007 TOC Not saving settings...
Hi there, When I use Word 2007 TOC, and I want to edit an existing TOC layout, etc. It wont remember my settings. I go to References Table of Contents Insert Table of Contents Then I make my chnages here, such as changing the levels of the various styles and how they show up Then I select OK, the TOC is created.. I then decide I don't like the changes I made, or I need to include another style So I go to References Table of Contents Insert Table of Contents The changes I made are no where to be seen. I have to start over from scratch. What am I doing wrong,...

Encoded Word Documents
We have recently purchased a new pc with windows 7 and have installed the Microsoft Office software however when I got to open a PDF or word document from a webpage a file conversion pop up box comes up and asks for me to select the encoding that makes the document readable but none of them do. even the windows default doesn't work...... You cannot open a pdf in Word. You need Adobe Reader. What you're downloading from a web page probably isn't a Word document, either. On May 27, 4:25=A0pm, Newuser <Newu...@discussions.microsoft.com> wrote: > We have rece...

Bulk E-mail with Non-Word Attachment
I need to be able to send a bulk e-mail (not from a template) with attachments of a number of different document types to a group of contacts (could be from a Saved View or Marketing List). Direct E-mail, Quick Campaigns and Mail Merge do not seem to be able to do this. Is there another way? Thanks. Nope. Take a look at this blog http://groups.google.com/group/microsoft.public.crm/msg/0a84808e99645006?dmode=source&hl=en Cheers, Venkatesh ---- On May 16, 11:57 pm, DStella <DSte...@discussions.microsoft.com> wrote: > I need to be able to send a bulk e-mail (not from a tem...

Word Wrap: Where is it in Word 2007?
I have Googled and Googled and cannot find any page that tells me where Word Wrap is. For such a basic feature you'd think it would be under View. Suggestions? On Mon, 18 Jan 2010 18:55:28 -0600, "Gary Gary" <blah@blah.blah> wrote: >I have Googled and Googled and cannot find any page that tells me where >Word Wrap is. For such a basic feature you'd think it would be under >View. Suggestions? The reason you aren't finding anything is that word wrap is automatic in Word unless you turn it off, or possibly if something is broken. Wha...

maximum rows limitation in an access 97 table
we have an access97 db which has 32405 rows, is there such a limit of 32700 rows in access 97 and if there is, can this be rosolved by upgrading to Access 2000 or higher. > is there such > a limit of 32700 rows in access 97 No, the limit is that the database cannot be larger than 1GB. Check out other specifications in Access Help, using "specifications" as a search criterin in the Search Wizard. -- Cheryl Fischer Law/Sys Associates Houston, TX "GM" <anonymous@discussions.microsoft.com> wrote in message news:07b501c3aadf$4d6d6630$a001280a@phx.gbl... >...

Where are my saved word?
I open the attachment directly, it is .doc and edit it in a loong time, what I ask is when I press save button and it show nothing so I think it succes. And I close it!! I can't find it now, could you help me ????? Thanks! It is gone. When editing an attachment, you /must/ save it to your HDD first, edit it and then reattach it. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "smezsc" <smezsc@discussions.microsoft.com> wrote in message news:3AF0D07A-D490-4819-A649-1C5FC079027F@microsoft.com... >I open the attachment dire...

Access denied using 'ApplicationClass' object in Web
Hi, I have created a web application in which i want to parse a visio diagram but when i do a new ApplicaionClass() i am getting "Access denied" my web application is using windows integrated authentication. I think when we create object of ApplicatioClass it tries to invoke the Visio.exe for which the security credentials are not enough.. How do i go about parsing a visio diagram on the server? Any help is highly appreciated. Regds ...

word 2002 compatibility with Windows 7
My niece is handicapped and her new computer has Office 2007 on it. She would like me to install word 2002 on her Inspiron 546. Is this possible? Yes, if it is licensed for another computer. If it is an OEM version, it cannot be installed on any but the system with which it was sold. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Sharon F" <SharonF@discussions.microsoft.com> wrote in message news:216DC8A6-4282-46A1-876D-B4D76E793F33@microsoft.com... > My niece is handicapped and her new computer ...

Scenario Pivot Table[Urgent]
Hi , will like to ask for help on excel scenario pivot table. I try to use the data from another spreadsheet and using the tools->scenario to create the scenario pivot table. But when i tried to refresh the data , the 'refresh data' is disabled and i'm not too sure why. Any idea?? Does scenario pivot table allows us to refresh data ?? Thanks --- Message posted from http://www.ExcelForum.com/ ...

Batch Conversion from Access 97 to Access XP
Hi, im looking for a tool, which is able to scan directories for Access files and converts them from Access97 to Access XP. Thanks in advance for your help Regards Michael Automating conversions is seldom a good idea: if nothing else, you still need to test each database to ensure that it works in the new version, so that the actual time required to open the database in the new version and have it convert is an insignificant amount of time overall. While I know you say you're using Access 2002, you might still benefit from the Access 2003 Conversion Toolkit (since, by defau...

Word 2007--pesky black dotted line
Hello: I have this moderately thick, black, dotted line running between paragraphs on my page. I have no idea how to get this thing to go away. Please, could you tell me what to do to get rid of it? Thanks, John Saxon See here http://word.mvps.org/faqs/formatting/CantGetRidOfLine.htm -- Mary Sauer MVP http://msauer.mvps.org/ "John" <someone@someplace.invalid> wrote in message news:2B22C75C-4BBB-43E0-8C67-BE19C55B818C@microsoft.com... > Hello: > > I have this moderately thick, black, dotted line running between paragraphs on > my p...

Summing multiple fields in matrix/table (qry)
My form populates a table with Products, Employees, and Emp hours worked on each product. Example: Product Emp1 Emp1hrs Emp2 Emp2hrs Emp3 Emp3hurs PLANES JOE 1 BILL 4 MARY 5 TRAINS JIM 2 JOE 3 BILL 3 CARS John 3 Mark 1 Joe 5 I would like to quickly add any or all employees hours for all products worked for ALL products. For example, Joes total hours = 9 which = 1+3+5. How can I do this with this table/query layout. Steve If that'...

Formula to extract the last word from a cell
Anyone know of a formula that will extract the last word from a cell. For example if cell contains the text: Best available title How could I produce the result: "title" (And preferably be able to fill the formula down the column to get the last word from other cells in the column as well.) Thanks for any suggestions. =MID(A10,FIND(CHAR(1),SUBSTITUTE(A10," ",CHAR(1),B10))+1,99) -- __________________________________ HTH Bob "Dave K" <fred.sheriff@gmail.com> wrote in message news:c4347e85-a3e2-4abe-b632-88643d4d244c@w7g2000hsa.googlegroups.com... &...

How do i remove outliers from a large data table so that i can in.
Hi you may post your question as body of your message -- Regards Frank Kabel Frankfurt, Germany "skyewell" <skyewell@discussions.microsoft.com> schrieb im Newsbeitrag news:07B029C4-3DB0-4206-9661-7A152FA9E2B6@microsoft.com... > ...

SFO on Laptop accessing MSCRM on laptop
Is it possible to configure the SFO laptop client to the MSCRM server installed on a laptop? Hi Niths! Niths wrote: > Is it possible to configure the SFO laptop client to the MSCRM server > installed on a laptop? On the _same_ laptop? No, although Microsoft is preparing a white paper on how to install CRM-Server and Outlook-Client on one box (only for demonstration purposes). Cheers Arne Janning Hi Niths, You could install MS Virtual PC and create a virtual PC which pretends to be either the server or the client. This will work for demo purposes if your laptop's hardw...