#1
|
|||
|
|||
VBA script to pull data into existing document
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 Last edited by macropod; 05-09-2021 at 05:47 AM. Reason: Added code tags |
#2
|
||||
|
||||
For starters it looks like the connection string is not right as the DBName appears to run into the UID without a separator.
Let Connect = "driver={sql server};server=SERVERNAME;Database=DBNAME;UID=USRI D;PWD=PASSWOOD;" Have you actually stepped through the code to see what values are being returned as you go. We don't know anything about the Stored Procedure so have no idea on what the Cmd1 command is doing. I would note that it appears you are creating a loop to replace the text in a bookmark. The way it is written I would expect the bookmark to disappear on the first write but if it didn't then your code would continue writing there without pause so the resulting code if run to completion would just show the last entry.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Let Connect = "driver={sql server};server=SERVERNAME;Database=DBNAME;UID=USRI D;PWD=PASSWOOD;"
This is how the above reads. This is a script I met in place and wanted to tweak it. I am trying to connect to a database and pull data from the field (usp_WordMerge_SelAddlinesByFileNumber) in the database. Cmd1.Parameters(1).Value = "0947000" is suppose to represent the customer number but I am not sure how to make that happen. The field name is actually {CustomerID} I am creating a loop to search all customerIDs and insert text in a bookmark until the customerID has no value or data. I hope that make sense |
#4
|
||||
|
||||
We are not able to do much to help you as we don't have access to your database, nor to the procedure named "usp_WordMerge_SelAddlinesByFileNumber" which I assume is in the database.
I've used a SQL string to create the filtered recordset and then traversed that subset of data with the processing. This page shows the basic method Using Excel VBA to Query a SQL Server Database | PTR You will need to know the table (or query) name where the CustomerID field exists Dim lngID as Long lngID = 0947000 'assuming it is stored as a number Set rs = conn.Execute("SELECT * FROM TableName WHERE CustomerID = " & lngID) If your recordset is already filtered to a unique CustomerID then there won't be a need to test the values of that field since you already know what it is.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
||||
|
||||
Based on your description, you're not performing a mailmerge - at least not one that would be recognised as such by MS Word, for example. Mailmerges populate mergefields, not bookmarks, even though they can be automated with VBA.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
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 |