Why is it always me asking silly questions in here?
Can someone help me? I have and excel sheet with a whole bunch of info in it. I have a Word mail merge document that looks at the Excel document. What I want is for the Word document to ask some questions on opening that will filter the results form the excel sheet.
I’m continuing to hunt the tinterweb but any advice would be appreciated.
Adam
Not sure what you mean. Basic mail-merging lets you insert a word field in a document and it pulls that data from the spreadsheet and merges on print or to a new document. What exactly is your idea?
I have an excel document, colums a-l with currently 450 ish rows of data, I want to be able to mail merge only the rows with a specific piece of data in say colum f, I thought I should be able to do this using some kind of SQL command SELECT * WHERE * in the word document but can’t work out where it should go, I have the folowing code to generate the mail merege query from excel opening the word document, applying the merge info then executing it, but the SQL WHERE is not working and it generates 65K records (not ignoring the blanks).
Private Sub CommandButton1_Click()
Dim wdApp As Word.Application
Dim WordWasNotRunning As Boolean
Dim wdDoc As Word.Document
'Get existing instance of Word if it's open; otherwise create a new one
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = New Word.Application
WordWasNotRunning = True
End If
wdApp.Visible = True
wdApp.Activate
Dim FileToOpen
FileToOpen = "C:\Documents and Settings\plevin\212.xls"
Dim t
t = "C:\Documents and Settings\plevin\Report.doc"
Set wdDoc = wdApp.Documents.Open(t)
Dim sql
sql = "SELECT * FROM 'NamedRange]' WHERE `A` IS NOT NULL"
With wdDoc.MailMerge
.OpenDataSource _
Name:=FileToOpen, _
SqlStatement:=sql
.Destination = wdSendToNewDocument
.Execute
End With
End Sub
I don’t know if I could do something similar in the word document as I’d prefer to keep the code in the Word document as it’ll make life easier in the long run.
Would it be simpler to pick the data from the Excel workbook to a different one using a macro and just merge the new workbook?
Why make it so hard. Make a new book that populates from the main spreadsheet call it temp. It will fill in the selected values into the temp, run faster and make your headache a thing of the past!
We do this all the time in DB’s we call them TEMP tables…makes the system run faster always!
Well I have restricted IT systems here, and a bunch of users thet given .1% of a chance will screw with stuff, so better to make it as simple IT wise and for the end users as possible.
I have sussed getting the code into Word by the way, was just a fiddle to get the sql to build correctly. This also gives me the oportunity to have the word document to reference diferent excel sheets.
OK, so I’m 95% there, but I’ve hit another snag, the original document opens, the form appears, you make your filter selections, hit generate, it creates a new document with the mail merge data, then closes the original document without saving (so it can’t be messed with) there is only one document now open. Save this document and reopen it and the form reappears, how do I stop this?
AAAND
I want to put an image in the document, relative to two merge fields AND the path to the document, any ideas how I’d do it? Have played with INCLUDEPICTURE but I can’t get the document path to be included, I don’t want to put the path in the original excel document as it will change but it will always be the same relative to the open document, AND just to possibly muddy things it has to be a UNC.
Get all that, I hope so 'cos I didn’t 
How are you actually saving it? Is it remembering the links to the form and do you have to break the link before saving it?
OK, so a long weekend of diging etc and I can’t find an elegant solution, so time to resort to VBA (Step2000!!!) my problem now is,
If I create a field, I can populate it via VBA, merging the application.path information with 2 of the mail merge fields to create the path to the picture.
BUT
if the mail merge info I retrieve from the merge changes per record, how to I get the VBA to know this and so change the field data?
OK, I’m about to cry scream and shout,
To someone who’s willing to help, files zipped and attached, please have a look and tell me where I’m going wrong. you should be able to work out what I’m after.
Just a quicky Damski, but is the last text field in the formula in cell S2 downwards supposed to be a double backslash? Might not help, but it does not look right.
droid, yes, when you use path in mail merge it needs to be double backslash. oh and to get it to work properly you need to put 3 digits after the XV on the spreadsheet name (I didn’t spot this before)