A little bit more info. not sure whether it will help or simply confuse:
1. When I open a .txt file on a network share as a data source in Word
2007 SP1 (I'm using Vista 32-bit here)
a. even if I specify the "Confirm conversions at open" within Word, I
do not get the "Text converter" option - only OLE DB and ODBC as a
maximum. This is AFAIK standard Word 2007
b. if I open using OLE DB, then use VBA to do this:
With ActiveDocument.MailMerge.DataSource
Debug.Print .Name
Debug.Print .ConnectString
Debug.Print .QueryString
Debug.Print .TableName
End With
..name is the UNC pathname of the file
..connectstring is an ACE OLE DB provider connection string that points
to nmy temp folder
..querystring contains (say) SELECT * FROM `txt48A80.txt`
..tablename contains (say) SELECT * FROM `txt4EC40.txt`
I often wondered why there was a .tablename as well as a .querystring
because in all other circumstances both strings seem to be the same.
When I look at my temp folder, it seems that Word has made a copy of the
file in Name, given it the name in .tablename, and is using that as the
data source.
If I now save and close my mail merge main document and re-open it, I am
given a SQL prompt that references the name in .querystring. If I
accept, Word actually seems to re-download, creates a new temp file, and
puts it in .tablename (i.e. the one in .querystring never actually seems
to be used, but does not change either). Word may also prompt for the
spearator character. It does, however, re-open the file successfully.
If instead I open the file using VBA and try to open it using the text
converter (providing just the name parameter to OpenDataSource may be
enough) then
..name is the UNC pathname of the file
..querystring is SELECT * FROM <the UNC pathname of the file>
..tablename is the same as .querystring.
All works OK until I save/close/reopen the document. Then I get a
message that quotes some sort of temp name for the Mail Merge Main
Document and says that the data source cannot be found (the correct UNC
name for the text file is given. Locating the data source changes
nothing unless I change the connection method to say (OLE DB).
However, if I add the UNC path name, suitably prefaced by file:// or
whatever, to the list of Trusted locations in Internet Explorer, this
problem goes away.
So although this security option does not appear to affect .txt files
opened as data source using OLE DB (the default), it may be worth
checking whether, for some reason, one of your network paths is listed
in the Trusted Locations and another is not.
Since I only run Word 2007 under Vista I have not been able to determine
to what extent these problems are down to Word and to what extent they
are down to Vista. However
a. it seems to me that Word/Office now has at least three separate
layers of so-called "security" when it comes to opening files - Windows
file security; Office trusted locations;IE Trusted Locations. I expect
someone at Microsoft may understand how it's all supposed to fit
together but it's certainly not obvious to me.
b. I get the impression that Vista has a timing problem with file
locking that may also affect these outcomes. For example, Windows
Explorer does not always appear to be able to "see" recently created or
recently closed files, suggesting that Microsoft has turned to the "lazy
write" approach to updating the disk, or something like that.
Peter Jamieson
http://tips.pjmsn.me.uk