VB Code to connect document to data source named in user form text

[Bev]

Can someone please help, I'm trying to automate a merge process with a User
Form where the user supplies the drivepath of the data source.
Since the data source is a variable my code looks like:
Dim SecDataSource as Object
Set SecDataSource = UserForm1.SecDrivepath.Text
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:="SecDataSource", .......
But VB tells me there's a compile error "Type Mismatch" where ".Text" is
highlighted on the code line where the Set command is given. Have I declared
the variable wrongly?

 

[Dave Lett]

Hi Bev,

..Text is a string. You'll probably have problems in the fourth line of the
example portion of your code, too. Remove the quotation marks from the
string variable SecDataSource in the fourth line:

Dim SecDataSource as String
SecDataSource = UserForm1.SecDrivepath.Text
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:=SecDataSource, .......

However, you could take out the variable altogether, probably, with
something like the following:

ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource
Name:=UserForm1.SecDrivepath.Text, .......

HTH,
Dave

 

[Bev]

Thanks Dave, your first suggestion worked well. I tried the second
suggestion as well but VB came back with "Compile error: Syntax error".