Advice using Excel and Word

[Davidprince]

I am trying to take data from an excel sheet and add it to a word document. My first approach was to use Word VBA but having spent a week trying to work out the syntax I'm wondering if doing the program from Excel (which I'm at least familiar with) would achieve the same result. Any advice please.

Thanks
david

 

[fumei]

You can go either way. In one sense, it does not matter which way.

It depends on your requirements.

1. Do you have an existing Word document you are trying to put data in?
2. Are you trying to put data into specific locations in the Word document?
3. Are you trying to get data out of the Word document?

Your question is so general that it is hard to give any good answers.

But the general answer is: yes, you can get the same results...without knowing what those results are...

Gerry

 

[tyhand]

word vba and excel vba are similar in that they are both based on vb, but different in that each is somewhat unique to its own environment.

The object oriented syntax is fundamentally the same, but their methods and properties are different.

for example, to do a 'find' in word, you can use:
yourobject.find.execute find:="whatever"
to do a find in excel, you can use:
yourobject.cell.find what:="whatever"

fumei is right. in general you can accomplish what you want using either word or excel.

If you're having problems with word vba, try using doing a search on its help files (or wizard) for the particular action you wish to accomplish. Do keyword searches for individual actions as opposed to entering entire phrases or asking 'how do i' questions. Then read up on each particular method, function or property to see if it fits in line with the concept of what you wanna do. This has worked very well for me is how i learned word vba.

Of course, I think excel vba is easier, but that's just my opinion and I've been working with it a bit longer.

Peace!
*****Tyhand

 

[Davidprince]

Guys
Whilst I realise the query was very general I am in a similar position to Tyhand, in that I have loads of experience in VB6 and Excel VBA. I thought the initial approach of using Excel VBA, but I had trouble locating similar examples which had used Excel. In fact because the program was a report generator and searching the Microsoft site revealed that using Word was easier I started down that route - but quickly turned back and hence the query.
Thanks again for the input
David

 

[fumei]

Well, each to their own. Me, I live in Word, so I am used to the bizarre and strange twists of the Word Object Model. I am more unfamiliar with Excel and find it even MORE twisted. But there you go.

If you post some specific requirements we can post some specific answers. However, as stated, you posed a general question. Word VBA is very much up to the task of getting Excel data. Excel is very much up to the task of putting data into Word.

In ANY case, you are going to have to work with the object model of them both. If you state what you are trying to do, we can probably help.

If you want further suggestions, start by answering the question posted earlier.



Gerry

 

[Davidprince]

Specifically what I'm doing is taking a Word document (it's a layout for a financial report) and adding Excel ranges to the document. The ranges change in size and I assumed that the best way around this was to use names for each data range. I thought this was a simple process - which from posts to the forums it appears to be - but I've tried to use Word instead of Excel and Word together.
Anyway thanks for previous input on this matter.
Regards
David

 

[macropod]

Hi David,

I know this is a vba forum, but have you considered using a LINK field in Word to gather the data instead? You can use a named Excel range in a LINK field, with the result that the corresponding table or embedded object in Word will grow/shrink as required.

If you experiment with copying the source Excel range and pasting it into Word using Edit|Paste Special and choosing the 'link to file' option, you can explore the benefits/drawbacks of the various linking options. To see the syntax of each, select the linked field and press Shift-F9 to expose the field coding. For embedded objects, you'll need to format them to 'in line with text' first.

Cheers

 

[fumei]

Bingo.

Gerry

 

[Davidprince]

Thanks for the tip - I hadn't forgotten about using a link but I wasn't using it correctly.
David