What's wrong with this script?

[H Kromberg]

Here's a challenging one:
I have obtained the script below from a previous forum post. The object is to automatically save a mail merged word file in a spefified directory. The procedure works fine if I enter an absolute dir path. HOWEVER, I am using a 'variable' directory path. The operative directory name had been automatically created from a prior procedure (PEOPLE-"LAST_NAME) dependent on the active record. I have adapted the script as I thought best to include the reference to that 'variable' sub-folder but without success (phew, I hope this makes sense). Any idea what that 'variable' reference should look like or what is required to achieve this?

The error I get is: "OLE automation error. This is not a valid file name." (Just a reminder again that the procedure works fine with a predefined file path - i.e. the valid file name error is not neccesarily a clue).

app = ole.GetObject("","word.application")
app.visible = .t. 'make sure Word is visible
app.activate()
Dim shared ws_mydoc as C
'ws_id below is a unique document number that I sorted out earlier in unseen code
ws_mydoc="C:\Contacts\Correspondence" + chr(92) + alltrim(PEOPLE-"LAST_NAME) + chr(92) + dmy(date())+ ".doc"
app.ActiveDocument.SaveAs(ws_mydoc)
app.ActiveDocument.close()

Thanks
Hendrik

[Stan Mathews]

RE: What's wrong with this script?

Not sure where you got your code but the documentation says
the syntax is

Pointer "OLE" = OLE.GETOBJECT( ProgId as C )

which would lead to


app = ole.GetObject("word.application")

with only one parameter.

[H Kromberg]

RE: What's wrong with this script?

Stan, I am too new to Xbasic to know how to apply what you're suggesting but I'm not sure that you're addressing the question. Perhaps I did not state the problem clearly. I should have also mentioned that Word is already OPEN when this procedure takes place... The challenge is not having to save the mail-merged document - that works fine as per my post. All I am trying to do is to DEFINE a specific directory path, part of which refers to a field name. One can refer to field names using the syntax: "table-"field_name" in creating a new directory or saving the file. But using this syntax for part of the DIRECTORY PATH reference does not work.
What is the correct syntax?

[Finian Lennon]

RE: What's wrong with this script?

The way you are doing this, your directory name literally includes "table-"fieldname" which is an illegal name.

You need to populate a variable with the field name and include the variable in the string. Strictly speaking, table-"fieldname is a pointer reference or dot.variable.
In the earlier part of the script you need pointers to the table and field. The online help has clear examples relating to these methods.

dim tbl as p;dim fh as p
tbl = table.open(tablename)
fh = tbl.field_get("field_name")
fieldname = fh.name_get()

dir.create("C:\fieldnames"+Chr(92)+fieldname)

would do the trick.

Finian

[H Kromberg]

RE: What's wrong with this script?

Hi Finian
Thanks for the advice. I have incorporated your suggested variable script as below. I was convinced this should work but I still get the 'check directory path' error.

***PLS NOTE: I obtained the original (SaveAs) script from a previous post on this forum and it works perfectly if I substitute the variable with an actual directory name... it's just the reference to the variable / field_name that is the issue - i.e. alltrim(fielname).

app = ole.GetObject("","word.application")
app.visible = .t. 'make sure Word is visible
app.activate()
Dim shared ws_mydoc as C
dim tbl as p;dim fh as p
tbl = table.open("people")
fh = tbl.field_get("last_name")
fieldname = fh.name_get()
ws_mydoc="C:\Contacts\Correspondence"+chr(92)+alltrim(fieldname)+chr(92)+"test.doc"
app.ActiveDocument.SaveAs(ws_mydoc)
End

[Finian Lennon]

RE: What's wrong with this script?

Actually, since you know the fieldname you don't need the pointer or anything else. Duh!

You need to check each step
'---
ws_mydoc_path="C:\Contacts\Correspondence"+chr(92)+"Last_Name"
pathok = A5_is_Path_Valid(ws_mydoc_path)
if pathok then
ws_mydoc = ws_mydoc_path+Chr(92)+"test.doc"
else
dir_recurse_create(ws_mydoc_path)
ws_mydoc = ws_mydoc_path+Chr(92)+"test.doc"
end if
'---
If it gets through here OK then the problem is in your OLE App. pointer syntax as Stan suggested before.

Finian

[H Kromberg]

RE: What's wrong with this script?

Finian,
I just can't crack it. Your script seems sensible but it does not work. And I do not know enough Xbasic to know what tweaking it needs. Remember the directory had already been created by the time this procedure runs. I guess I would expect to use app.ActiveDocument.SaveAs somewhere rather than dir_recurse_create. So I have adapted your script as follows, including the app.ActiveDocument.SaveAs command - but it still does not recognize the 'intelligent' path.

app = ole.GetObject("","word.application")
app.visible = .t. 'make sure Word is visible
app.activate()
ws_mydoc_path="C:\Contacts\Correspondence"+chr(92)+people-"Last_Name
pathok = A5_is_Path_Valid(ws_mydoc_path)
if pathok then
app.ActiveDocument.SaveAs(ws_mydoc_path+chr(92)+"TEST.doc")
End if

I would have thought that the ws_mydoc_path would work, referring to the relevant path, but it doesn't. Again, if I use this syntax to check the valid path with a fixed (unintelligent) folder name, no problem.

Any more help would be greatly appreciated!
Thanks for your efforts so far.
Hendrik

[H Kromberg]

RE: What's wrong with this script?

Just to clarify, checking the valid path with the people-"last_name reference works fine - i.e. the error is not in line 4 but in line 7!

[H Kromberg]

RE: What's wrong with this script?

It's getting a bit quiet out there and at the risk of appearing persitant, I again include the script below in the hope for a solution (problem seems to be in line 7 and line 4 works for me despite some earlier misgivings):

app = ole.GetObject("","word.application")
app.visible = .t. 'make sure Word is visible
app.activate()
ws_mydoc_path="C:\Contacts\Correspondence"+chr(92)+people-"Last_Name
pathok = A5_is_Path_Valid(ws_mydoc_path)
if pathok then
app.ActiveDocument.SaveAs(ws_mydoc_path+chr(92)+"TEST.doc")
End if

In hopeful anticipation,
Hendrik.

[Marcel Kollenaar]

Out of the Blue..

Hendrik,

After my message last week I found the solution for the instability of the documentnames. It has everything to do with the position of the word.activate() command. I put it in the wrong place. Even the 'strategies' aren't needed anymore.

I created a very simple A5v7 solution with 1 table called People with fields: Last_Name, Sur_Name, Department, Pathtosave. It is attached as a zip file.

There is one line of code you have to change:

template_name = "\Documents and Settings\Marcel\Application Data\Microsoft\Sjablonen\BT\A5.dot"

Change this line in to the Word templatepath in your situation. The A5.dot I used is also a very simple one and is enclosed in the zip file. Copy this template to your Word template folder.

Open People and go to the form tab. View People and push on the orange button. This will open the script where you can change the variable template_name. Save the script and push the button Merge to Word. If Word isn't active it will take some seconds to see some reaction. If Word is active it's more speedy.

Warning!
This application creates folders like C:\testcase\A5\BC on your C: drive. In these folder word documents with date and Last_Name are created. Actually the Last_Name value of the active record at the moment you pushed the butoon.

'Date Created: 04-Apr-2006 09:28:16 PM
'Last Updated: 08-Apr-2006 01:36:30 PM
'Created By  : Marcel Kollenaar
'Updated By  : Marcel Kollenaar

'// Thanks to Stan Mathews, Finian Lennon, Steve Wood
'// who's forum posts helped me a lot.

option strict

constant wdFormatDocument = 0 'FileFormat = wdFormatDocument

dim wdFileName as C
dim wdPathName as C
dim docnames as C
dim docname as C
dim i as N
dim WordDocCount as N
dim WordDocCountBefore as N
dim word as P ' Pointer for Word Object.

'// Creating the file path and name from the fields.
wdPathName = alltrim(people->Pathtosave) + "\\" + \
			 alltrim(People->Department) + "\\"
wdFileName = alltrim(dmy(date())) + alltrim(people->Last_Name) + ".doc"

'// Get a Word object before starting the merge.
'// The OLE.GETOBJECT() method uses an existing OLE automation object, 
'// if available. Otherwise, it creates a new one.

word = ole.GetObject("","word.application")

'// Make the application run in visible mode if not.
if word.visible = .F. then
	word.visible = .T.
end if

'// This part of code has been created with Action Scripting.
'// Merge data into a Microsoft word template.

dim template_name as c 

'// Type here your own full template path and template file name or
'// create/select with the Word merge button a new template and fill the alias
'// or full path and filename in the next line.
'// Your table and fields must be used in the template you reference.

template_name = "\Documents and Settings\Marcel\Application Data\Microsoft\Sjablonen\BT\A5.dot"

dim merge_type as n 
merge_type = 2    '1 = current record, 2 = all selected records, 3 = all highlighted records

'// Use or the above full template_name or the next alias from Alpha.
'// I use the A5.dot Decode the template name (it may have been encoded
'// to use a file alias (which is defined in the View/Settings dialog box)

template_name = filename_decode(template_name)

'// export the data from the current Form or Browse view to a temporary data
'// file that Word will use as its data source for the merge.

dim temp_Dataname as c 
temp_Dataname = a5_exportdatamailmerge(merge_type)

'//Now merge the data....
if temp_dataname <> "" then
	a5_word_merge("template",temp_dataname,template_name)
end if 

'// End of Action Scripted Code.

xbasic_wait_for_idle()

'// Activate the merged document.
'// The last document has always index number 1.
word.documents(1).activate()

'// Count how many doucuments are opened in Word.
'// This works only for one Word session. A second session
'// creates the same numbers and files will be overwritten.
'// This is for testing purpose only.
'WordDocCount = word.documents.count

'// Create a docname.
'docname = "mydummy" + alltrim(str(WordDocCount)) + ".doc"
'ui_msg_box("Name of document",docname)

'debug(1)

'// Create the folder up to the leafnodes. If the folder can't
'// be created a messagebox will appear.
if file.dir_create_recurse(wdPathName) = .F. then
    ui_msg_box("Warning","Could not create path.")
else

	xbasic_wait_for_idle()
	
	'// 
	if file.exists(wdPathName + wdFileName) = .F. then

		'// Now save the document with a name distilled from the table fields.
		'// See VBA Word Help file for explanation of parameters.
		word.documents(1).saveas(wdPathName + wdFileName,\
								   wdFormatDocument,.F.,"",.T.,\
								   "",.F.,.F.,.F.,.F.,.F.)
	else
	    ui_msg_box("Warning","File: "+ wdPathName + wdFileName + " already exists." )
	end if

	'// Activate the Word application.
	word.activate()

	'// Now put some text on top of the document.
	word.selection.typetext("Document: " + wdPathName + wdFileName)
	word.selection.TypeParagraph()
	word.selection.TypeParagraph()

end if
delete word

end

I hope this example will contribute to keep you on board of the Alpha Five community ;)

If you need help to integrate this code into your application just ask.

[Marcel Kollenaar]

Out of the Blue.

Some additional information.

The code above was created in A5v7. I've tested it in A5v6 and it will run in version 6 without change.

I noticed an annoyance not related to A5 but to Word. If you have Windows XP SP2 and Office XP or Office 2003 you receive the “Opening this will run the following SQL command” message when you open a Word mail merge main document that is linked to a data source. This can also happen if you create a template. When you place merge fields in a main document or template a data source is connected. For a template this is unwanted because A5 creates a temporary ascii-delimited data source when it does a merge. The name of the data source is different every new merge. The database fields you want to merge must match with the fields you used in the merge template.

The SQL warning helped to warn that a data source is still connected. In our case this is unwanted because every merge A5 starts is stopped by this warning and you have to answer "yes" or "No". "No" disconnects also the data source but then you are already in a merge session. You can solve this by changing the document type right after you put the merge fields in the document or template.

This can be done as follows:



The template must be open for editing. Activate the merge button toolbar, click on the first button from the left. A menu appears. Choose "Normal Word document", click OK and save the template. Now it is a template with plain fields not currently related to a data source.

If you want to get rid of the SQL-warning completely see the Microsoft Knowledge Base. But then you have no warning that a data source is still conneced.

BTW. If you place Word merge fields by hand Ctrl+F9 for {} and type the code between it as in {MERGEFIELD Name \* MERGEFORMAT} you don't need a data source connected and the document or template stays a 'normal' type document.

I hope this will help.