Populate word doc with table data 2

[keepingbusy]

Hi

I have been searching this forum and Google to try and create a piece of code that will create a Microsoft Word Document (Version 2003 or 2007 if it will make a difference) and found the following which creates the document but does not populate it from a test table with three fields - forename, surname and add01:

oWord = CREATEOBJECT("Word.Application")
oWord.Visible = .F.
oDoc = oWord.Documents.Add()

LOCAL lcList
lcList=''

* To generate string(line) of the list

DO WHILE !EOF('EMP_LIST')
  lcList= lcList+;
    PADR(ALLT(EMP_LIST.FORENAME)+' '+;
    ALLT(EMP_LIST.SURNAME),20,' ')+;
    ' - '+EMP_LIST.ADD01+;
    CHR(13)
    SKIP IN EMP_LIST
ENDDO

oWord.Quit(.T.) && save changes
RELEASE oWord

There are no errors, just no table data shown in the word document.

Any suggestions please guys? I am using VFP9.

Thank you

Lee

 

[Mike Lewis]

Hi Lee,

Well, it's not surprising that no data is appearing in the document. Your code does a good job of storing the data in a variable, but that's as far as it goes.

There are several ways of getting the contents of the variable into the document. The easiest option is to do this, after you have finished the loop (before you quit Word):

oDoc.content.text = lcList

By the way, I suggest that, in the second line of your code, you set oWord.Visible to .T. rather than .F. - at least while you are testing the program. That way you will see what Word is doing, and it will be easier for you to diagnose any problems. Set it back to .F. later if you wish.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

You are not doing anything with lcList, thus it wouldn't show up there.

Your creation of lcList is very ineffective. Check copy to, textmerge and alike in help.

Here is a sample creating a 'table' in word:

#Define wdSeparateByCommas  2
#Define wdSeparateByDefaultListSeparator  3
#Define wdSeparateByParagraphs  0
#Define wdSeparateByTabs  1

#Define wdTableFormatClassic1  4
#Define wdTableFormatColorful1  8
#Define wdTableFormatColorful2  9
#Define wdTableFormatColorful3  10
#Define wdTableFormatContemporary  35
#Define wdTableFormatElegant  36

LOCAL lcData
lcData = GetMySampleData()

oWordDocument=Createobject("word.application")	&& Create word object
With oWordDocument
  .documents.Add	&& New file
  .Visible = .T.
  _cliptext = m.lcData
  With .ActiveDocument.Range
    .Paste      && Paste VFP data
    .ConvertToTable(wdSeparateByTabs,,,,wdTableFormatColorful2,,,,,,.F.,.F.,.F.) && Convert to table
  Endwith
Endwith

Procedure GetMySampleData
  Local lcTempFile, lcData
  lcTempFile = Forcepath(Sys(2015)+'.tmp',Sys(2023))
  Use (_samples+'data\Customer')
  Copy To (m.lcTempFile) ;
    Fields Company, Contact, Title,Country ;
    Type Delimited With "" With Tab
  lcData = "Company Name"+Chr(9)+;
    "Contact Name"+Chr(9)+;
    "Title"+Chr(9)+;
    "Country"+Chr(13)+Chr(10)+;
    Filetostr(m.lcTempFile)
  Erase (m.lcTempFile)
  Return m.lcData

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Lee,

With Cetin's solution, you're inserting each piece of text (that is, each input record's worth) as you extract it from the table. With my approach, you store the entire text in the variable, and insert it in one go. My approach might not be practical if the data is particularly large, but it should be faster, as there will be fewer COM calls.

Another option would be to put this code inside your loop (after you've filled lcList, but before you do the SKIP):

oDoc.Range.InsertAfter(lcList)

Do that instead of putting oDoc.content.text = lcList at the end.

Another advantage of Cetin's method is that you will end up with a nicely formatted table. If you didn't do that, you would need to format the document with a fixed-pitch font, otherwise the colums would be ragged.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Mike

thank you for your post and I think this will be the way to go.

Cetin

I have seen your example which was posted on a forum somewhere. It creates a page as Mike has suggested and also gives examples how to wrap text around a table. Unless I'm missing something what is the command line in your example that would add data from a table into the generated word document?

Lee

 

[Nifrabar]

For what it's worth:
This code I recently developed (with help of several members of the forum).
I put the data in an array and than populate Word with that info.
The Word table is generated automaticly at the end of the doc.
I know it is not the fastest way but the code is pretty easy to understand and also includes some cell-format-examples like fontname, size and bold.
-Bart

        lnMaxRows = 19 && =aLen(laLev,1)
        oRange = oDocument.Range()
	oRange.Collapse(wdCollapseEnd)

	oWord.ActiveDocument.Tables.Add(oRange, lnMaxRows, 2, 1, 0) && 5 rij, 4 kolom, 1 en 0 tabeleigenschap
	
	lnTable = 1
	oTable2  = oWord.ActiveDocument.Tables(lnTable) && Assign a table object
	WITH oTable2
		.Columns(1).SetWidth(150,0)
		.Columns(2).SetWidth(320,0)
		.Autoformat(0)
		FOR lnRow = 1 TO lnMaxRows
			FOR lnCol = 1 TO 2
				.Cell[lnRow,lnCol].Range.InsertAfter(laLev[lnRow,lnCol])
				.Cell[lnRow,lnCol].Range.InsertAfter(CR)
				.Cell[lnRow,lnCol].Range.Font.Name 	= "Arial" 
				.Cell[lnRow,lnCol].Range.Font.Size 	= 10 
	*			.Cell[lnRow,2].Range.Font.Size 	= 12 
				.Cell[lnRow,1].Range.Font.Bold 	= .T. 
	*			.Cell[lnRow,2].Range.Font.Bold 	= .F. 			

			ENDFOR 
		ENDFOR
		
		.Cell[ 1,2].Range.Font.Size 	= 14 
		.Cell[ 2,2].Range.Font.Bold = .T. 
	*	.Cell[12,2].Range.Font.Size 	= 14 
	 
	ENDWITH

 

[keepingbusy]

Hi Bart

I'll try this out later and thank you for your post

 

[keepingbusy]

Hi Bart

Ok, tried the code and hit an error on the second line:

Object ODOCUMENT is not found

Any ideas?

 

[Mike Lewis]

Lee,

I think Bart only showed the portion of code that was different from yours.

You could try putting this at the top of the code:

oWord = CREATEOBJECT("Word.Application")
oWord.Visible = .F.
oDocument = oWord.Documents.Add()

Bart will no doubt correct me if I'm wrong about that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Hi Mike

Thank you. I have added your additional code and got past the original error. As you mentioned I suspect this is quite unique to Bart as there are other errors now popping up in between the FOR lnCol = 1 TO 2 command line

If Bart could perhaps explain his set up to point me in the right direction and I'm not after the sollution, just some guidance.

You also mentioned,

Bart will no doubt correct me if I'm wrong about that.

That wont be the first time today Mike!!

Many thanks as always.

Lee

 

[Nifrabar]

Lee,
Mike (as usual) was correct.
I just passed piece of code from my app/
As Mike showed next code should be placed in the beginning:

#Define CR CHR(13)

TRY 
	oWord = GetObject(,"Word.Application")
CATCH
	oWord = CREATEOBJECT("Word.Application")
ENDTRY
*oWord.visible = .T.

* open existing file
lcFileName = "C:\myFile.doc"
oDocument =oWord.Documents.Open(lcFileName)

As I used an Array:

local array laLev[19,2]

laLev[1,1] = "Cell 1.1"
laLev[1,2] = "Cell 1.2"

* etc.

That should work
-Bart

 

[cbasoz]

Lee,
Probably I don't understand what you are asking. Check 'GetMySampleData' in code.

Cetin Basoz
MS Foxpro MVP, MCP

 

[keepingbusy]

Cetin

The forum title is, "Populate word doc with table data" so I'm grateful for your post but the example you gave wasn't actually what I was looking for.

I did actually run that when I found it on the Microsoft website and is very good for formatting etc...

Regards


Lee

 

[keepingbusy]

All

Thanks for your posts (and giving me some inspiration) I have now come up with the following that produces the desired effect, albeit I will be working on the formatting etc and fonts etc.

The following example code creates a word document (I'm using Office Version 2007 with VFP9), it populates the document with an open free table (There are just three fields so I could run a test), on completion asks to save the file.

I'm not sure that the last part is necessary as I think it would be more beneficial and easier for the user if the file was named so I'll look into this.

Here is the code:

oWord = CREATEOBJECT("Word.Application")
oWord.Visible = .T.
oDoc = oWord.Documents.Add()
LOCAL lcList
lcList=''

* To generate string(line) of the list

USE EMP_LIST SHARED
GO TOP

DO WHILE !EOF('EMP_LIST')
  lcList= lcList+ ;
  PADR(ALLT(EMP_LIST.FORENAME)+' '+ ;
  ALLT(EMP_LIST.SURNAME),20,' ')+ ;
  ' - '+EMP_LIST.ADD01+CHR(13)
  SKIP IN EMP_LIST
ENDDO

oDoc.content.text = lcList

oWord.Quit(.T.) && save changes

RELEASE oWord

Many thanks

Lee

 

[Mike Lewis]

Lee,

I think it would be more beneficial and easier for the user if the file was named so I'll look into this.

You can use the Save method to save the file. If the file has just been created and not yet saved, the Save method will prompt the user for a filename.

If you want to specify the filename yourself, use SaveAs, and pass the filename as a parameter.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Mike

I have managed to find some associated word commands one of which is the one you have mentioned.

Thanks again

lee

 

[cbasoz]

Cetin

The forum title is, "Populate word doc with table data" so I'm grateful for your post but the example you gave wasn't actually what I was looking for.

I did actually run that when I found it on the Microsoft website and is very good for formatting etc..

Now you lost me there. I don't get why getting something from your 'emp_list' table and writing it to word is considered "Populate word doc with table data" and getting data from VFP's sample table customer is not. Maybe I can't see the tree because of the forest.

Cetin Basoz
MS Foxpro MVP, MCP