Can I combine data typed in Word with data imported from SQL Serve

[Edward F]

I am programmatically importing data from ADO (SQL SERVER 2K) into Word 2003.

The code works fine but It overwrites all content in the document when the
code is run.

Can I type in data into the document and still import from SQL Server and
have both data in the document?


This is the code am using.

Private Sub Document_Open()


' Declare our variables

Dim oRange As Word.Range
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sTemp As String
Dim sSQL As String
Dim strcon As String


sSQL = "SELECT DISTINCT n.FULL_NAME, n.TITLE,n.WORK_PHONE,n.EMAIL FROM
dbo.Name_Reps_View n, dbo.Activity_View a where n.CO_ID =a.CO_ID and
n.CO_ID = '05240' AND (a.PRODUCT_CODE = 'COMMITTEE/EPC' OR n.CO_ID ='05240')
AND (n.REP_TYPE Like'%WRP%' Or n.REP_TYPE Like '%ORP%' Or n.REP_TYPE Like
'%CNO%' Or n.REP_TYPE Like '%APC%')"

' Get the current document's range object
Set oRange = ActiveDocument.Range

' Create a new ADO connection
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")
' Open our connect







strcon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=IMIS_PRODUCTION;Data Source=CYRUS"


oConn.Open strcon


oRS.Open sSQL, oConn







sTemp = oRS.GetString(adClipString, -1, vbTab)





sTemp = "Name" & vbTab & "Title" & vbTab & " Workphone" & _
vbTab & "Email" & vbCrLf & sTemp



oRange.Text = sTemp

oRange.ConvertToTable vbTab, , , , wdTableFormatColorful2

Set oConn = Nothing
Set oRS = Nothing





End Sub

Pls Help


Tolu10(removethis)@hotmail.com

 

[Jay Freedman]

Hi Edward,

The reason your code overwrites the content is that you're assigning the
range of the entire document to the variable oRange, and then you're
assigning the result of the query to the oRange.Text property. To keep the
existing text and add the query result at the end, you need to collapse
oRange so that it points to just the end of the document. You do this by
adding the line

oRange.Collapse Direction:=wdCollapseEnd

after the first assignment, at the point shown below.

--
Regards,
Jay Freedman
Microsoft Word MVP

I am programmatically importing data from ADO (SQL SERVER 2K) into
Word 2003.

The code works fine but It overwrites all content in the document
when the code is run.

Can I type in data into the document and still import from SQL Server
and have both data in the document?


This is the code am using.

Private Sub Document_Open()


' Declare our variables

Dim oRange As Word.Range
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sTemp As String
Dim sSQL As String
Dim strcon As String

[snip]

' Get the current document's range object
Set oRange = ActiveDocument.Range

' ==> Add this:
oRange.Collapse Direction:=wdCollapseEnd
[snip]

sTemp = oRS.GetString(adClipString, -1, vbTab)

sTemp = "Name" & vbTab & "Title" & vbTab & " Workphone" & _
vbTab & "Email" & vbCrLf & sTemp

oRange.Text = sTemp

[snip]

 

[Edward F]

Jay,


Thanks a bunch, you soluition worked!!!!!!!!!

Hi Edward,

The reason your code overwrites the content is that you're assigning the
range of the entire document to the variable oRange, and then you're
assigning the result of the query to the oRange.Text property. To keep the
existing text and add the query result at the end, you need to collapse
oRange so that it points to just the end of the document. You do this by
adding the line

oRange.Collapse Direction:=wdCollapseEnd

after the first assignment, at the point shown below.

--
Regards,
Jay Freedman
Microsoft Word MVP

I am programmatically importing data from ADO (SQL SERVER 2K) into
Word 2003.

The code works fine but It overwrites all content in the document
when the code is run.

Can I type in data into the document and still import from SQL Server
and have both data in the document?


This is the code am using.

Private Sub Document_Open()


' Declare our variables

Dim oRange As Word.Range
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sTemp As String
Dim sSQL As String
Dim strcon As String

[snip]

' Get the current document's range object
Set oRange = ActiveDocument.Range

' ==> Add this:
oRange.Collapse Direction:=wdCollapseEnd
[snip]

sTemp = oRS.GetString(adClipString, -1, vbTab)

sTemp = "Name" & vbTab & "Title" & vbTab & " Workphone" & _
vbTab & "Email" & vbCrLf & sTemp

oRange.Text = sTemp

[snip]