VBA script to pull data into existing document

Formd · #1 05-07-2021, 06:16 AM

I hope I can explain what I need so you can understand and assist me. I am not sure if this should be in mailmerge or vba since I am using a combination of vba, mailmerge and userforms, I posted here. So please forgive me if this is posted in the incorrect thread.

I have a mailmerge document with a userform, and I am using vba script to connect this document to my database so I can pull data from the database and place it into my document using bookmark. This information would be different for each customer that I run the script on. The script inserts the data just like I want it but big problem is it keeps the same information and not change like it should for each customer. I am not sure how to use the mergefield in the script that changes for each customer. Below is the script I am using.

I believe this may be the line in my script that has the issue:
Cmd1.Parameters(1).Value = "0947000"
"000000" identifies the customer number but I think it should be the mergefield name here??

not sure how to place mergefield here.

Code:

Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim Connect As String
Dim FileServiceID As Long
Dim RecordingDate As String


Let Connect = "driver={sql server};server=SERVERNAME;Database=DBNAMEUID=USRID;PWD=PASSWOOD;"
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = Connect
Conn1.Open
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "usp_WordMerge_SelAddlinesByFileNumber"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = "0947000" 
Set Rs1 = Cmd1.Execute()
While Not Rs1.EOF
    Addlines = Rs1.Fields(0).Value
    Selection.GoTo What:=wdGoToBookmark, Name:="Addlines"
    With ActiveDocument.Bookmarks
        .DefaultSorting = wdSortByName
        .ShowHidden = True
    End With
    Selection.TypeText Text:=Addlines
Rs1.MoveNext
Wend
Rs1.Close
Conn1.Close
Set Rs1 = Nothing
Set Conn1 = Nothing

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Applying New Multi-Level List to Existing Document with Manual Numbering and Existing Styles	stanley	Word	4	12-15-2020 10:59 AM
I cant get my Sum(Offset formula to pull appropriate data.	Erickd90	Excel	7	07-28-2016 12:20 PM
pull data using drop down menu	nfsmith	Excel	8	03-23-2016 11:28 PM
Excel VBA: Pull data from web	tinfanide	Excel Programming	0	12-09-2011 02:11 AM
macro to pull data from multiple files	psrs0810	Excel	2	10-25-2010 01:49 PM