[Peter Jamieson]

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

[Mona-ABE]

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
.

[Peter Jamieson]

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?

[Mona-ABE]

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?
.

[Peter Jamieson]

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.