Word MergeField with Excel

[ROGERIORIOS]

I have a VFP 9 system. It generates a spreadsheet with data coming from the database. I have a docx file that is associated with this worksheet. In the docx I have some cells that I use the {if{margefield ....}....} to be able to show or not some fields in certain positions.

The problem: when I run the VFP code I want the docx to be opened associated with that worksheet, but that doesn't happen. The docx opens and is not associated with the worksheet. If I open this docx through windows explorer it opens correctly associated with the worksheet.



Copy To Sys(5)+Sys(2003)+'\planilhabasehistorico.xls' Type Xl5
Local oExcel As Excel.Application
try
oExcel = Createobject('Excel.application')
oExcel.Visible = .T. && Deixa o excel visível
oExcel.WindowState= -4137 && Janela maximizada
*oExcel.Workbooks.Open(Sys(5)+Sys(2003)+'\'+ALLTRIM(str(thisform.text1.value,3))+thisform.text2.value+'_'+Thisform.txtsemestre.value+'.xls') && abre teste.xls
oExcel.Workbooks.Open(Sys(5)+Sys(2003)+'\planilhabasehistorico.xls')
oExcel.Selection.Locked = .F. && "desbloqueio" todas (vem bloqueadas por default)
oExcel.Columns("A:A").Select() && Seleciono a coluna A (código)
oExcel.Selection.Locked = .T. && Bloqueio somente a coluna A
oExcel.Columns("B:B").AutoFit() && Autofit na coluna B (nome)
try
*oExcel.Workbooks.SAVE(Sys(5)+Sys(2003)+'\planilhabasehistorico.xls')
*oExcel.Workbooks.Close
oTeste.save
oTeste.close
oExcel.Quit
oExcel=.NULL.
RELEASE oExcel
*oExcel.Workbooks.Close()
CATCH TO oException
Aerror(laError)
Messagebox("Erro: "+" - "+ laError[1,2],0,"Aviso do Sistema")
IF oException.ErrorNo = 1
STRTOFILE("Error occurred at: " + TRANSFORM(DATETIME());
+ CHR(13),"C:\Errors.log",.T.)
ENDIF
ENDTRY
TRY
m.nameword = Sys(5)+Sys(2003)+'\'+ALLTRIM(tbcontrole.cnomearqhistorico)
*If FILE(m.nameword)
*try
Local oWord As Word.Application
oWord = Createobject("word.application")
*?oword.Documents.Count && 0
*oword.Documents.add()
*?oword.Documents.Count && 1
*?oword.Documents.Item(1).Name
oWord.Documents.open(m.nameword)
oWord.Visible=.T.
*catch
* MESSAGEBOX('O arquivo '+m.nameword+' não existe Verifique !',48,'Aviso do Sistema')
*endtry
CATCH
MESSAGEBOX('Não foi possivel CRIAR o arquivo '+Sys(5)+Sys(2003)+'\'+m.nameword+' Verifique !',48,'Aviso do Sistema')
endtry
CATCH
MESSAGEBOX('Excel / Office não pode ser aberto, Verifique !',48,'Aviso do Sistema')
endtry


Then excel opens, closes the worksheet and the word document is opened but is not associated with the worksheet

 

[Mike Lewis]

Welcome to the forum.

What do you mean when you say that the document is not "aasociated" with the worksheet? In what sense do you expect them to be "associated"?

In general, a given type of file is associated with an application, and this is determined by its extension. So a DOCX is associated with Word, XLSX with Excel, and so on. (I'm sure you already knew that.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ROGERIORIOS]

Hi MIKE !
SORRY....
I will try to explain better.

In the Microsoft Word word processor, it is possible to create documents linked or associated with a database (in this example, the base is a spreadsheet). OK ?
So I, using this feature, created a linked word document associated with an excel spreadsheet. OK ?

Using the Merge field feature in word, I can show or not show columns from that worksheet in the docx document. When I open the docx through a double click on Windows Explorer, the docx file opens and the Excel spreadsheet fields are shown according to the MERGEFIELD condition. But by VFP this is not happening. The docx is opened without linking to the excel spreadsheet. The spreadsheet contains 10, 15, 20, 25 lines (student's information) so it should (mail merge) show them.

 

[Chris Miller]

Well, the part of the code that opens a Word document is this:

Local oWord As Word.Application
oWord = Createobject("word.application")
oWord.Documents.open(m.nameword)
oWord.Visible=.T.

If you get no errors from that but the fields in the docx are not evaluated, are you sure you open the correct docx?
You can see which document the VFP code opens by displaying m.nameword in a Messagebox with

Messagebox(m.nameword)

Edit: Also: Are you sure the Excel file and its Sheet is only linked and not embedded into the Word document, so an update of the data actually changes what Word displays?

I don't know about anything that stops Word using the field expressions and linked Excel sheets etc., just because you use OLE automation to start Word and open the docx programmatically instead of doing so manually.

Chriss

 

[ROGERIORIOS]

Hi Chris !
Thanks for the answer.
Yes, it is opening the correct .docx file, I had already done this test showing a wait window with the file name. Now I did a test and commented all the code that works with the Excel.Application object.
Nothing has changed, the docx file continues to open without being linked to the spreadsheet, watch the video.

https://youtu.be/xS0uPHt1hQE

 

[ROGERIORIOS]

Hi Chris :
" Also: Are you sure the Excel file and its Sheet is only linked and not embedded into the Word document, so an update of the data actually changes what Word displays?"

I ran the routine inside my vfp system with different classes of students:
The result is the same: when opening it through explorer, the docx opens asking if it is to execute the Select * from "myexcelsheet" and the data presented are different for each class that I execute within the VFP exe.

But when running in exe VFP

Local oWord As Word.Application
oWord = Createobject("word.application")
oWord.Documents.open(m.nameword)
oWord.Visible=.T.

opens the document not associated with the sheet or asks the question about executing the SQL Select * from "myexcelsheet" command.

 

[Chris Miller]

So there's code in the docx, macros? What about security settings? If you are very sure that you open the same document, but get different behaviours, that's the only reason, opening by automation might cause this code within the docx not to work.

This describes that this behaviour is by design:

https://learn.microsoft.com/en-us/office/troubleshoot/word/autoexec-autoopen-macros-word

If you want this to work, you havve to "lower your shields" and allow macro execution also to work in the automation scenario.

Chriss

 

[ROGERIORIOS]

Hi Chris. 15:50 Brazil
Thanks for the research and the suggestion.
What exists in the .docx file is the command ( I don't really know if it's a MACRO ):
{if{mergefield myfield}="1" {mergefield myfield2} "-----"},
that I insert the braces { } by pressing ctrl +f9.
And the fields myfield and myfield2 come from excel spreadsheet, generated by the routine in the VFP exe.
I will test and post the result.

15:58
I can't test it now because in my notebook there is no MS-OFFICE installed, only a similar OFFICE - created in BRAZIL and it doesn't have this security feature. Tomorrow in my office at work I will test your suggestion.
Thanks

 

[Chris Miller]

This just describes your scenario, or a very similar one. It hows that it's a possible reason that a document works directly but not when opened by automation code.

For a solution you should ask in an Office forum, this isn't a VFP problem, this is a security configuration. And lowering the security may solve it, but also may not be worth risking.

Chriss

 

[ROGERIORIOS]

Hi Chris.08:35 am Brazil
Theoretically, this command was missing.

oWord.ActiveDocument.MailMerge.OpenDataSource(Sys(5)+Sys(2003)+'\planilhabasehistorico.xls')

Watch :

https://youtu.be/ijNZvle-XIw

So :
TRY
m.nameword = Sys(5)+Sys(2003)+'\'+ALLTRIM(tbcontrole.cnomearqhistorico)
Local oWord As Word.Application
oWord = Createobject("word.application")
oWord.Documents.open(m.nameword)
oWord.Visible=.T.
oWord.ActiveDocument.MailMerge.OpenDataSource(Sys(5)+Sys(2003)+'\planilhabasehistorico.xls')
CATCH
MESSAGEBOX('Não foi possivel CRIAR o arquivo '+m.nameword+' Verifique ',48,'Aviso do Sistema')
endtry

 

[Chris Miller]

Good, you found a solution.

It's still due to Word security not doing everything when you open a document by automation.
The merge mail happens, when you open the document manually, so it's automatic at start. It's surely also done, when you manually open the document, as it has to refresh from the linked excel sheet.

If you automate this, this autostart meachnaism is suppressed. If explicitly calling the mail merge operation helps, that's fine. You were only talking fields, not mal merge.

Anyway, good that you found a solution.



Chriss

 

[ROGERIORIOS]

Hi Chris as I posted above , I always said I was using MERGEFIELD not Merge Mail.

Best regards

 

[Chris Miller]

The process of merging data in is called mail merge, as the method you call. No matter if it's really for a mail or not.

Chriss

 

[ROGERIORIOS]

Hi. Yes, I know that.

 

[Chris Miller]

Then you realize that for the mergefield to work, the document has to go through the mailmerge step.
It does so automatically, if you manually open the docx, it doesn't if you OLE automate the opening of the docx.

So that's still the overall reason, the automatic execution is only a feature of manually using the docx and not of automation. And the reason for that is security about such things.

You didn't point out that the excel file embedded in the word document had a sql source defined in it which must be triggered, that's why I couldn't even get the idea that you need to call that. So thanks for pointing out your solution and if you ever need to know something, especially when it's more VFP-related, come back, of course.

Chriss