|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MailMerge from txt file - failing when the file path contains a reserved SQL word
Hiya,
I've got some VBA code that pulls some records from a database, throws them into a tab separated .txt file, and then sets up a mailmerge to that text file. Works fantastically! Except.... I've just been hunting for a glitch that occurred today. I've worked out that the file path contained the word 'where' in it. eg "C:\Jobs\Timber where missing\" Code:
strPath = ActiveDocument.Path & "\MailMerge.txt" ActiveDocument.MailMerge.OpenDataSource strPath, , , True, False ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False I'm guessing this is because WHERE is an SQL reserved word... If I manually do the mail merge, i get an SQL error. See image below. I've tried to put ' or " at the start and finish of the strPath variable. No good. The only work around is to rename the folder. This isn't ideal - as if i go down this line, there are probably other SQL keywords that will cause problems. Any suggestions for this unique situation?.... It is kind of a Microsoft Glitch / Bug... |
#2
|
||||
|
||||
Looking at https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue it appears that you have two options to explore.
1. Include a SQLStatement variable to see if Word doesn't try to interpret the Name as a SQL statement instead. 2. Create a .qry file and point at that instead
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
||||
|
||||
Why not simply have the mailmerge main document connect directly to the database and apply whatever filtering you require within the mailmerge main document itself?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Quote:
Thanks for your reply. #1 - I tried parameter Code:
SQLStatement:=";" #2 - Not too sure what you mean, can you offer any further assistance? I'm not trying to query an SQL source, but set a txt / csv type file as the datasource for the mail merge. |
#5
|
|||
|
|||
Quote:
I did try this initially. The issue I ran into, was the sql statement was very long and complex. Using the OpenDataSource function, the SQLStatement and SQLStatement1 parameters seemed to not be large enough for this query. Working with Access regularly, it was easy to establish a connection and recordset, and throw the data I need into a file. It doesn't change very often, so this disconnected data works very well. |
#6
|
||||
|
||||
SQLStatement is suitable for query strings up to 255 characters. Where the query string is longer than 255 characters, you'd use SQLStatement1 for the remainder; there is no specified character limit for SQLStatement1.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Quote:
Are you sure SQLStatement1 accepts a longer string? Code:
strSQL1 = Left(strSQL, 255) strSQL2 = Right(strSQL, Len(strSQL) - 255) If I remove a few spaces or one of the WHERE criteria from the statement, it executes ok... |
#8
|
||||
|
||||
I have no idea what the character limit for SQLStatement1 might be; all I said is that no limit is specified. Assuming both are limited to 255 characters, you might explore the possibility of optimising the SQL code or even using field coding within the mailmerge main document to achieve the same outcome.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Putting path and file name into all Word documents | Kevin Beach | Word | 3 | 11-26-2016 06:14 PM |
Determining file path for linked image in Word 2007 | samhdc | Word | 7 | 01-26-2015 04:21 PM |
Display Full File Path Name of Document in Title bar MS-Word 2010 | Carlos06x | Word | 1 | 10-12-2011 10:39 AM |
Programmatically get File Path for currently opened WORD document | franferns | Word | 1 | 11-26-2009 12:36 PM |
get file name without Path | Ziggy1 | Word VBA | 1 | 09-29-2006 07:55 PM |