Word and Excel from VB

[Caro]

I know how to open and Excel spreadsheet from VB and then save it with a new filename. Also I can open a Word document as a template but does anyone know how I can get information from the Excel spreadsheet(certain cells) into my Word document in a specific position (like a mailmerge)
Is this possible? and if so how please?

[Nitro]

There are many ways to do this.

If the word documents exist already like a template, just link it to the specific cells.

Otherwise, yes mailmerge is the way to go. To do the mail merge, follow the following steps

-In Excel, make it save the data to a text file by using the Open "C:\Test.txt" and so on with "WRITE".

-Make the Word Document run against this txt file. Actually you can even make it run against another Excel spreadsheet.

I will continue more when you can provide which method you would like to work with.

[Caro]

Yes the Word document does exist like a template - thanks

[Nitro]

MICROSOFT WORD
The first thing you want to do is construct a dummy text file. Place your fields and data into the text file. Commas will represents separation of fields. Then you want to do a mail merge on this text file.

Open up your template document and click on Tool-MailMerge-Create-Forms Letters-Active Window

Next, Click on GetData-Open Data Source and locate your text file.

On the upper left hand side, there will be a drop down on the mail merge tool bar. Insert your fields and save the template.


MICROSOFT Excel
Do something like the following code for the coding part in Excel.

  'Reference "Microsoft Excel 9.0 Library"
  Dim xlsWorkBook As Excel.Workbook
  Dim xlsWorkSheet As Excel.Worksheet
  Set xlsWorkBook = GetObject("C:\Your File.xls")
  Set xlsWorkSheet = xlsWorkBook.Worksheets("C")

  Dim lng_FileNumber As Long
  lng_FileNumber = FreeFile
  Open str_Destin & "WhatEver.Txt" For Output As #lng_FileNumber
  Do While Not EOF(lng_FileNumber)   
     Print #lng_FileNumber, xlsWorkSheet.Cells(1, 1).Value
  Loop
  Close #lng_FileNumber

  xlsWorkBook.Close

Once you are done with this step, I will show you how to fire off the mail merge.

This process is quite easy once you understand the concepts, but it is just hard to explain over the net.

Basical Concept, you are just telling Excel to make the data file and telling Word to pull the data into the mail merge.

Is that what you are asking for?

[Caro]

Thanks - thats great!