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