Archive of Mr Excel Message Board


Back to Other Office and Excel archive index
Back to archive home



Mail Merge from Access to Excel...?

Posted by Bill Tanner on April 09, 2000 1:52 PM
I posted this about 3 weeks ago and got no response so I thought I would try again with a twist...

[START]

I have mail merged from an Access Database to a Word Document many times. And I understand
that you can merge from Excel to Word. But can you merge from Access to Excel? I am looking for a
function rather than a vba solution. Likewise, I realize that I can run my query and copy and past the
result on an Excel worksheet and go from there.

But it seems that going from Access would be so simple that Microsoft would have included it as a
built in function. Am I missing it?

[END]

Given the lack of response, I'm guessing that there is no built in function. Anybody have any alternative handlings?

I suppose that I could copy and paste my Access query results into a worksheet and run a macro. I can paste the various values that I need to paste but, not being a programmer, I don't know how to move from row to row. (I think it's called an array?)

In effect what I am trying to do is put a formula in certain cells for the first record, print it and then change the formulae to add the data from the second record. Something like this

IF Test2!A1 <>"", then

Test1!C6=Test2!A1 and

Test1!C8=Test2!A2 and

Test1!C10=Test2!A3

And print it

Then next...

IF Test2!B1 <>"", then

Test1!C6=Test2!B1 and

Test1!C8=Test2!B2 and

Test1!C10=Test2!B3

And print it

Then next...

IF Test2!C1 <>"", then

Test1!C6=Test2!C1 and

Test1!C8=Test2!C2 and

Test1!C10=Test2!C3

And print it

Then next... etc...

Anyone?

Thanks in advance...

Bill


Re: Mail Merge from Access to Excel...?

Posted by Tom Morales on April 11, 2000 4:08 PM
Bill -
Have you tried playing around with Excel's query tools? (See Tools/Get External Data / Create a Query). After setting up the basic query on a worksheet, you can edit the query to get your differing results.

I've recorded the query editing process, and used the recorded SQL strings in a macro that I would build around it. You have to insert your query variables in the long SQL strings that VBE records. It's not always for the faint of heart, but you eventually get the hang of it.

You can have the macro obtain those variables from elsewhere in your workbook, or via an input box.

Have fun,
Tom




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.