Excel and word macro -

[Ctech]

Hi guys

I have some experience with making macros in excel, however never done it with word. So some guidance would be nice.

What I’m trying to do is... See attached file (picture).

The picture is an example of a reference to a journal. My document contains several of these references.

I want to copy the name of the author in word into in column A in a excel spreadsheet, then the source (column B) and then the document type (column C).

Then go to the next reference in the document and past all the info into the specific columns on a new row in Excel....



I hope my question is understandable,
thanks for all help so far..

[Ctech]

Does this mean that there is noone here which knws how to make macros in Word?

[Helmut Weber]

Hi Ctech,

the link below leads me to a page,
I don't know what to do with.

>|Download: http://www.excelforum.com/attachment.php?postid=4252 |

For WordVBA related questions visit, e.g.:
microsoft.public.word.vba.general

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

[Ctech]

Im not sure I understand what the problem is?
Are you trying to tell me that my attachment doesn't work?

The attachement is a screenprint of the word document which Im trying to pull date from, to excel.

I guess the macro have to be something like this:

Set i = number of "Authors:" in document

For each i in Document

Find "Author".... take text in next cell and copy.
Paste this in excel(row 2, column A)

Find "Source"... take next cell and copy.
Paste this in excel (row 2, column B)

Next i

Hope this helps you guys understand the objective of the macro.

Hi Ctech,

the link below leads me to a page,
I don't know what to do with.

>|Download: http://www.excelforum.com/attachment.php?postid=4252 |

For WordVBA related questions visit, e.g.:
microsoft.public.word.vba.general

[Steve Yandl]

Ctech,

When I selected your link, it took me to a different Excel forum and there
was no screenshot to help me understand how the document is structured.
There are plenty of people here who could advise you on using VBA from Excel
to extract content from a Word document but without knowing details about
that document we would be shooting in the dark.

Steve



"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Im not sure I understand what the problem is?
> Are you trying to tell me that my attachment doesn't work?
>
> The attachement is a screenprint of the word document which Im trying
> to pull date from, to excel.
>
> I guess the macro have to be something like this:
>
> Set i = number of "Authors:" in document
>
> For each i in Document
>
> Find "Author".... take text in next cell and copy.
> Paste this in excel(row 2, column A)
>
> Find "Source"... take next cell and copy.
> Paste this in excel (row 2, column B)
>
> Next i
>
> Hope this helps you guys understand the objective of the macro.
>
>> Hi Ctech,
>>
>> the link below leads me to a page,
>> I don't know what to do with.
>>
>> >|Download: http://www.excelforum.com/attachment.php?postid=4252 |
>>
>> For WordVBA related questions visit, e.g.:
>> microsoft.public.word.vba.general
>>
>
>
> --
> Ctech
>
>
> ------------------------------------------------------------------------
> Ctech's Profile:
> http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=503675
>

[Ctech]

Im sorry the attached photo in my first post works for me... anyway, I have now uploaded the picture to another localtion on the net.

http://www.amedisin.no/crissimo/databse-ie.jpg

The picture is a "print screen" from the word document I'm working with.


I hope this explains it.

[Steve Yandl]

The link to the jpg works now.

You have a picture of a Word table but there is a comment below the table
that references a pdf file. Is the Word document saved as a file with a doc
extension and available on your hard drive or your network or is it
something created in Word but saved as a PDF file?

Steve


"Ctech" <[email protected]> wrote in message
news:[email protected]...
>
> Im sorry the attached photo in my first post works for me... anyway, I
> have now uploaded the picture to another localtion on the net.
>
> http://www.amedisin.no/crissimo/databse-ie.jpg
>
> The picture is a "print screen" from the word document I'm working
> with.
>
>
> I hope this explains it.
>
>
> --
> Ctech
>
>
> ------------------------------------------------------------------------
> Ctech's Profile:
> http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=503675
>

[Ctech]

Forget the "Source: PDF - 9502013412.pdf" its for my own use... so I can know where the file the referene above links to.


What I want exactly is to get the data which you see in word "printscreen / jpg) to be converted into excel format.

"Title" info is pasted into "column A" in excel
"Author" info is pasted into "column B" in excel
and so on..

[Ctech]

Is there really noone that can help me on this matter?

[Steve Yandl]

Edit the path and file name for the Word document containing all the tables
and see if this does what you want.

Sub ScanWdTables()
Dim strTitle As String
Dim strAuthor As String

' Start Word and open document containing tables
Set oWd = CreateObject("Word.Application")
oWd.Visible = False
Set oDoc = oWd.Documents.Open("C:\Test\TestTables.doc")

' Process through each of the tables
For T = 1 To oDoc.Tables.Count

' Optain text from Word table cells
strTitle = oDoc.Tables(T).Cell(1, 2).Range.Text
strAuthor = oDoc.Tables(T).Cell(2, 2).Range.Text

' Trim End of Cell markers from text extracted
strTitle = Left(strTitle, Len(strTitle) - 2)
strAuthor = Left(strAuthor, Len(strAuthor) - 2)

' Populate Excel Cells with text from Word tables
Cells(T, 1).Value = strTitle
Cells(T, 2).Value = strAuthor
Next T

oDoc.Close
oWd.Quit
Set oWd = Nothing

End Sub



Steve
"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Forget the "Source: PDF - 9502013412.pdf" its for my own use... so I
> can know where the file the referene above links to.
>
>
> What I want exactly is to get the data which you see in word
> "printscreen / jpg) to be converted into excel format.
>
> "Title" info is pasted into "column A" in excel
> "Author" info is pasted into "column B" in excel
> and so on..
>
>
> --
> Ctech
>
>
> ------------------------------------------------------------------------
> Ctech's Profile:
> http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=503675
>

[Steve Yandl]

I looked back at your original post and see you also wanted to extract the
document type. The code below should do the trick. Keep in mind, I've
assumed that the only tables in your Word document are like the picture you
posted and normally I'd take the time to put in error checking.

Here you go.

Sub ScanWdTables()
Dim strTitle As String
Dim strAuthor As String
Dim strType As String

' Start Word and open document containing tables
Set oWd = CreateObject("Word.Application")
oWd.Visible = False
Set oDoc = oWd.Documents.Open("C:\Test\TestTables.doc")

' Process through each of the tables
For T = 1 To oDoc.Tables.Count

' Optain text from Word table cells
strTitle = oDoc.Tables(T).Cell(1, 2).Range.Text
strAuthor = oDoc.Tables(T).Cell(2, 2).Range.Text
strType = oDoc.Tables(T).Cell(4, 2).Range.Text

' Trim End of Cell markers from text extracted
strTitle = Left(strTitle, Len(strTitle) - 2)
strAuthor = Left(strAuthor, Len(strAuthor) - 2)
strType = Left(strType, Len(strType) - 2)

' Populate Excel Cells with text from Word tables
Cells(T, 1).Value = strTitle
Cells(T, 2).Value = strAuthor
Cells(T, 3).Value = strType
Next T

oDoc.Close
oWd.Quit
Set oWd = Nothing

End Sub


Steve


"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Forget the "Source: PDF - 9502013412.pdf" its for my own use... so I
> can know where the file the referene above links to.
>
>
> What I want exactly is to get the data which you see in word
> "printscreen / jpg) to be converted into excel format.
>
> "Title" info is pasted into "column A" in excel
> "Author" info is pasted into "column B" in excel
> and so on..
>
>
> --
> Ctech
>
>
> ------------------------------------------------------------------------
> Ctech's Profile:
> http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=503675
>

[Ctech]

Steve Yandl: Thank you so much.. I have to ask, is this you full time job, helping less knowledgeable guys like me making macros?

Anyway, thank you.. You make life so much easier....

[Steve Yandl]

You're welcome. As to your question, I'm disabled and no longer working a
regular job so I probably have more time than many people for volunteer
work. Most of the time spent in this group is reading and learning but I do
find opportunities to offer help from time to time.

Steve



"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Steve Yandl: Thank you so much.. I have to ask, is this you full time
> job, helping less knowledgeable guys like me making macros?
>
> Anyway, thank you.. You make life so much easier....
>
>
> --
> Ctech
>
>
> ------------------------------------------------------------------------
> Ctech's Profile:
> http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=503675
>