Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-18-2018, 12:12 AM
travb81 travb81 is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 6
travb81 is on a distinguished road
Default 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
Any suggestion on how I can get around this?
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...
Attached Images
File Type: png capture.PNG (5.1 KB, 17 views)
Reply With Quote
  #2  
Old 10-18-2018, 02:26 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,166
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
Reply With Quote
  #3  
Old 10-18-2018, 09:30 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,468
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #4  
Old 10-21-2018, 04:17 PM
travb81 travb81 is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 6
travb81 is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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
Hi Andrew,
Thanks for your reply.
#1 - I tried parameter
Code:
SQLStatement:=";"
, without success
#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.
Reply With Quote
  #5  
Old 10-21-2018, 04:26 PM
travb81 travb81 is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 6
travb81 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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?
Hi Paul,

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.
Reply With Quote
  #6  
Old 10-21-2018, 07:40 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,468
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by travb81 View Post
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.
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
[MS MVP - Word]
Reply With Quote
  #7  
Old 10-26-2018, 03:31 AM
travb81 travb81 is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 6
travb81 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Hi Paul,
Are you sure SQLStatement1 accepts a longer string?

Code:
    
strSQL1 = Left(strSQL, 255)
strSQL2 = Right(strSQL, Len(strSQL) - 255)
My statement is 514 characters long, and the .OpenDataSource statement is throwing up runtime error 9105, 'string is longer than 255 characters'.
If I remove a few spaces or one of the WHERE criteria from the statement, it executes ok...

Reply With Quote
  #8  
Old 10-26-2018, 04:00 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,468
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 11:41 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft