Excel and word macro -

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ctech - 21 Jan 2006 18:45 GMT

Hi guys

I have some experience with making macros in excel, however never don
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 documen
contains several of these references.

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

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

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

+-------------------------------------------------------------------

|Filename: databse-ie.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4252

+-------------------------------------------------------------------

--
Ctec

Reply to this Message

Ctech - 21 Jan 2006 23:02 GMT

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

Signature

Ctech

Reply to this Message

Helmut Weber - 21 Jan 2006 23:21 GMT

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

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Ctech - 21 Jan 2006 23:52 GMT

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,
>
[quoted text clipped - 5 lines]
> For WordVBA related questions visit, e.g.:
> microsoft.public.word.vba.general

Signature

Ctech

Reply to this Message

Steve Yandl - 22 Jan 2006 00:16 GMT

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

> Im not sure I understand what the problem is?
> Are you trying to tell me that my attachment doesn't work?
[quoted text clipped - 27 lines]
>> For WordVBA related questions visit, e.g.:
>> microsoft.public.word.vba.general

Reply to this Message

Ctech - 22 Jan 2006 00:24 GMT

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.

Signature

Ctech

Reply to this Message

Steve Yandl - 22 Jan 2006 00:43 GMT

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

> 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.
[quoted text clipped - 5 lines]
>
> I hope this explains it.

Reply to this Message

Ctech - 22 Jan 2006 00:54 GMT

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..

Signature

Ctech

Reply to this Message

Steve Yandl - 22 Jan 2006 19:11 GMT

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

> Forget the "Source: PDF - 9502013412.pdf" its for my own use... so I
> can know where the file the referene above links to.
[quoted text clipped - 5 lines]
> "Author" info is pasted into "column B" in excel
> and so on..

Reply to this Message

Steve Yandl - 22 Jan 2006 20:34 GMT

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

Reply to this Message

Ctech - 22 Jan 2006 23:23 GMT

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....

Signature

Ctech

Reply to this Message

Steve Yandl - 23 Jan 2006 15:09 GMT

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

> 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....

Reply to this Message

Ctech - 22 Jan 2006 13:14 GMT

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

Signature

Ctech

Reply to this Message