Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-07-2021, 06:16 AM
Formd Formd is offline VBA script to pull data into existing document Windows 7 32bit VBA script to pull data into existing document Office 2007
Advanced Beginner
VBA script to pull data into existing document
 
Join Date: Feb 2015
Location: TX
Posts: 30
Formd is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-09-2021, 01:43 AM
Guessed's Avatar
Guessed Guessed is offline VBA script to pull data into existing document Windows 10 VBA script to pull data into existing document Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 2,324
Guessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to behold
Default

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
Reply With Quote
  #3  
Old 05-10-2021, 05:59 PM
Formd Formd is offline VBA script to pull data into existing document Windows 7 32bit VBA script to pull data into existing document Office 2007
Advanced Beginner
VBA script to pull data into existing document
 
Join Date: Feb 2015
Location: TX
Posts: 30
Formd is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 05-10-2021, 10:18 PM
Guessed's Avatar
Guessed Guessed is offline VBA script to pull data into existing document Windows 10 VBA script to pull data into existing document Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 2,324
Guessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 05-11-2021, 03:47 AM
macropod's Avatar
macropod macropod is offline VBA script to pull data into existing document Windows 10 VBA script to pull data into existing document Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,200
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by Formd View Post
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.
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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA script to pull data into existing document Applying New Multi-Level List to Existing Document with Manual Numbering and Existing Styles stanley Word 4 12-15-2020 10:59 AM
VBA script to pull data into existing document I cant get my Sum(Offset formula to pull appropriate data. Erickd90 Excel 7 07-28-2016 12:20 PM
VBA script to pull data into existing document 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
VBA script to pull data into existing document macro to pull data from multiple files psrs0810 Excel 2 10-25-2010 01:49 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft