Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Using SharePoint list as MailMerge Data Source (Word 2007)

Over the years, one or two people have asked about using SharePoint
lists as mailmerge data sources (or perhaps for inserting DATABASES into
Word). The best I have been able to do in the past was suggest that you
make a link from Access, then either use that linked table as your data
source, or perhaps use the List GUID that Access discovers to make a
connection from Word.

However, I have now looked again, and connecting from VBA using a fairly
simple connection string seems to do the trick. I don't know whether
that is because something has changed in the software, or simply because
I never got the connection info. quite right before. I would guess
creating a suitable .odc and connecting to that would also work, but
here I'm using an empty .odc.

I thought I'd post it here because there may well be interested lurkers.
If you are able to check this out and post back, particularly if you
have a production SharePoint environment, please do. I only have a small
testbed here.

The versions I'm using are...
a. Server: Win 2008 server SP2 with MOSS 2007
b. Client O/S: Win Vista Ultimate SP2
c. Office 2007 SP2

Of those things, I suspect that the key to this is that the ACE
provider, or perhaps the Sharepoint "WSS" IISAM that comes with it seems
to work differently from the Jet provider. That would typically mean
that you would need Office 2007 at least. It's possible that something
changed in one of the Office SPs. I doubt if the client O/S or the
server environments are factors but you never know.

As an example I have a site at http://w8sp:50000 with a number of lists
including the list "mylist"

You will also need an empty text file called empty.odc. Let's say it is
at c:\a\empty.odc

Some test VBA code:

Sub Connect2SharepointList()
' List name
Const strList As String = "mylist"
' Site URL
Const strDatabase As String = "http://w8sp:50000
' Office Database Connection file
Const strODC As String = "c:\a\empty.odc"
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.OpenDataSource _
Name:=strODC, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strDatabase & ";" & _
"Mode=Share Deny None;" & _
"Extended Properties=""WSS;HDR=NO;IMEX=2;" & _
"DATABASE=" & strDatabase & ";" & _
"LIST=" & strList & ";"";", _
sqlstatement:= _
"SELECT * FROM [" & strList & "]"
End Sub

(actually, it does not seem to matter what listname you put in the
sqlStatement -

"SELECT * FROM [x]" seems to work fine
)
--
Peter Jamieson

http://tips.pjmsn.me.uk
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mona-ABE Mona-ABE is offline
external usenet poster
 
Posts: 2
Default Using SharePoint list as MailMerge Data Source (Word 2007)

Hi, Peter, I was thrilled to see that you added this post! I've been working
for days trying to figure out how to create a Word document that merges with
SharePoint list data. However, when I click on the link you provided for
your sample site, it's a broken link. Can you provide more information or an
updated link?
--
Thanks!
Mona-ABE


"Peter Jamieson" wrote:

Over the years, one or two people have asked about using SharePoint
lists as mailmerge data sources (or perhaps for inserting DATABASES into
Word). The best I have been able to do in the past was suggest that you
make a link from Access, then either use that linked table as your data
source, or perhaps use the List GUID that Access discovers to make a
connection from Word.

However, I have now looked again, and connecting from VBA using a fairly
simple connection string seems to do the trick. I don't know whether
that is because something has changed in the software, or simply because
I never got the connection info. quite right before. I would guess
creating a suitable .odc and connecting to that would also work, but
here I'm using an empty .odc.

I thought I'd post it here because there may well be interested lurkers.
If you are able to check this out and post back, particularly if you
have a production SharePoint environment, please do. I only have a small
testbed here.

The versions I'm using are...
a. Server: Win 2008 server SP2 with MOSS 2007
b. Client O/S: Win Vista Ultimate SP2
c. Office 2007 SP2

Of those things, I suspect that the key to this is that the ACE
provider, or perhaps the Sharepoint "WSS" IISAM that comes with it seems
to work differently from the Jet provider. That would typically mean
that you would need Office 2007 at least. It's possible that something
changed in one of the Office SPs. I doubt if the client O/S or the
server environments are factors but you never know.

As an example I have a site at http://w8sp:50000 with a number of lists
including the list "mylist"

You will also need an empty text file called empty.odc. Let's say it is
at c:\a\empty.odc

Some test VBA code:

Sub Connect2SharepointList()
' List name
Const strList As String = "mylist"
' Site URL
Const strDatabase As String = "http://w8sp:50000
' Office Database Connection file
Const strODC As String = "c:\a\empty.odc"
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.OpenDataSource _
Name:=strODC, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strDatabase & ";" & _
"Mode=Share Deny None;" & _
"Extended Properties=""WSS;HDR=NO;IMEX=2;" & _
"DATABASE=" & strDatabase & ";" & _
"LIST=" & strList & ";"";", _
sqlstatement:= _
"SELECT * FROM [" & strList & "]"
End Sub

(actually, it does not seem to matter what listname you put in the
sqlStatement -

"SELECT * FROM [x]" seems to work fine
)
--
Peter Jamieson

http://tips.pjmsn.me.uk
.

  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Using SharePoint list as MailMerge Data Source (Word 2007)

I've been working
for days trying to figure out how to create a Word document that

merges with
SharePoint list data.


This is an example address taken from my LAN and it's not reachable on
the Internet.

So the idea was that you substituted your own sharepoint http: address
and list name in the code. Sorry for any confusion!

That said, the sharepoint http: address would normally be quite simple,
so I hope it will be easy for you to set up a test.

If this does not work for you, the other way that you can set up such a
merge, as long as you have Access, is to create a database that contains
a table linked to the Sharepoint list, then use that as the data source
for the merge. Arguably that is simpler anyway, but I think some people
dislike the fact that they have to do everything via an Access database
that they shouldn't really need.

Or, you can use Access primarily to retrieve the list's GUID - once you
have created the linked table, you can use the Access Database
Documenter to display the list's location and GUID, then try plugging
that back into Word.

(Originally I tried writing some Word VBA to retrieve that "list of
lists" and translate a list name into a list GUID, but it has to do it
using an http/SOAP request and so far I haven't found a reliable way to
do that with VBA alone).

Let me know if I can help further. I am obviously interested to know if
this method works for you, or of course you may find a better solution.

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/02/2010 13:37, Mona-ABE wrote:
Hi, Peter, I was thrilled to see that you added this post! I've been working
for days trying to figure out how to create a Word document that merges with
SharePoint list data. However, when I click on the link you provided for
your sample site, it's a broken link. Can you provide more information or an
updated link?

  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mona-ABE Mona-ABE is offline
external usenet poster
 
Posts: 2
Default Using SharePoint list as MailMerge Data Source (Word 2007)

Thanks for the quick reply, Peter!

I did try to use your code in my document and I replaced it with the URL of
my sharepoint site and I ran into an error:

Run-time error '9105':
String is longer than 255 characters

This was the section of code the debuger was stopping on and there seems to
be an issue with the SQL statement

ActiveDocument.MailMerge.OpenDataSource _
Name:=strODC, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strDatabase & ";" & _
"Mode=Share Deny None;" & _
"Extended Properties=""WSS;HDR=NO;IMEX=2;" & _
"DATABASE=" & strDatabase & ";" & _
"LIST=" & strList & ";"";", _
sqlstatement:= _
"SELECT * FROM [" & strList & "]"

Any tips/suggestions you send my way are GREATLY appreciated!

I'm trying to avoid the Access DB route and I'm sitting through some Open
Office XML training as we speak to see if I can possibly develop a solution
against that technology.

--
Thanks!
Mona-ABE


"Peter Jamieson" wrote:

I've been working
for days trying to figure out how to create a Word document that

merges with
SharePoint list data.


This is an example address taken from my LAN and it's not reachable on
the Internet.

So the idea was that you substituted your own sharepoint http: address
and list name in the code. Sorry for any confusion!

That said, the sharepoint http: address would normally be quite simple,
so I hope it will be easy for you to set up a test.

If this does not work for you, the other way that you can set up such a
merge, as long as you have Access, is to create a database that contains
a table linked to the Sharepoint list, then use that as the data source
for the merge. Arguably that is simpler anyway, but I think some people
dislike the fact that they have to do everything via an Access database
that they shouldn't really need.

Or, you can use Access primarily to retrieve the list's GUID - once you
have created the linked table, you can use the Access Database
Documenter to display the list's location and GUID, then try plugging
that back into Word.

(Originally I tried writing some Word VBA to retrieve that "list of
lists" and translate a list name into a list GUID, but it has to do it
using an http/SOAP request and so far I haven't found a reliable way to
do that with VBA alone).

Let me know if I can help further. I am obviously interested to know if
this method works for you, or of course you may find a better solution.

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/02/2010 13:37, Mona-ABE wrote:
Hi, Peter, I was thrilled to see that you added this post! I've been working
for days trying to figure out how to create a Word document that merges with
SharePoint list data. However, when I click on the link you provided for
your sample site, it's a broken link. Can you provide more information or an
updated link?

.

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Using SharePoint list as MailMerge Data Source (Word 2007)

Hello Mona-ABE - I probably won't be able to reply for a few days either.

Peter Jamieson

http://tips.pjmsn.me.uk

On 05/02/2010 13:48, Mona-ABE wrote:
Thank you so much, Peter, for such a wonderfully detailed response! I'm off
for the next few days so I won't be able to implement it right away, but as
soon as I get a chance I'm going to try your suggestions. I'll let you know
how it goes.

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help with Word 2007 MailMerge Using Access as data source. Lynda[_2_] Mailmerge 8 September 29th 08 01:31 AM
Mail merge with documents in SharePoint as data source Yorick Mailmerge 3 July 13th 07 03:28 PM
Sharepoint document as data source Tony McNair Mailmerge 1 November 17th 06 01:01 PM
Use Sharepoint List as Merge Database source in Word 2003? funnybroad Mailmerge 1 May 26th 06 01:08 AM
Sharepoint List as Mail Merge Data Source? funnybroad Mailmerge 1 May 26th 06 01:08 AM


All times are GMT +1. The time now is 09:46 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"