Output Word Data to SQL

[ewylam]

I need help!!! I am a ColdFusion and database programmer but am trying to use VBA for this project. I have some VBA experiance but I don't knwo how to do this.

I am trying to output selections from a Word document into various columns and tables in an SQL database. I have no problem pulling from the database, but I can't "Insert Into".

Is this possible? Is it possible to use VBA in word to create tables? Any suggestions would be greatly appreciated.

 

[TonyJollans]

Hi ewylam,

You need to establish a connection to your database (ODBC or whatever, SQL database is not descriptive so I don't know what sort of database you mean - SQL Server perhaps).

Then you build your SQL strings with a combination of literals and variables as you would most anywhere else and send them to your database.

The variables come from selections of text in your Word document. Exactly how you extract the data depends on your document. There is a Selection object (a special type of Range object) representing selected text which you will probably find useful.

I'm not sure what else to say without more detail of the structure of your document.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[ewylam]

Tony,

Thanks for the response. I am accessing an SQL Server 2000 The following is a Sub I was using to test if it was possible. I get an error if I use the "Update" statement in my SQL string.

Sub FindSubjects()

Dim varSubject As String

Selection.Find.ClearFormatting
With Selection.Find
.Text = "A?.?."
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchByte = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchFuzzy = False
.MatchWildcards = True
End With
Selection.Find.Execute
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
varSubject = Selection.Text

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:=Connection, _
Connection:= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=checklistitems;Data Source=is-18wg1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WS-18WGHQ-0076;Use Encryption for Data=False;Tag wi" _
, SQLStatement:="UPDATE ""cklsttestchecklist"" SET Subject = '" & varSubject & "'", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther
End Sub

It does work to pull info using the following:

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\emmett.wylam\My Documents\My Data Sources\is-18wg1 checklistitems.odc" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=checklistitems;Data Source=is-18wg1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WS-18WGHQ-0076;Use Encryption for Data=False;Tag wi" _
, SQLStatement:="SELECT * FROM ""cklsttestchecklist""", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther

Your help is greatly appreciated.

 

[ewylam]

Added the connection info... I still get an error unable to connect to data source.

Sub FindSubjects()

Dim varSubject As String

Selection.Find.ClearFormatting
With Selection.Find
.Text = "A?.?."
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchByte = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchFuzzy = False
.MatchWildcards = True
End With
Selection.Find.Execute
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
varSubject = Selection.Text

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="C:\Documents and Settings\emmett.wylam\My Documents\My Data Sources\is-18wg1 checklistitems.odc" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=checklistitems;Data Source=is-18wg1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WS-18WGHQ-0076;Use Encryption for Data=False;Tag wi" _
, SQLStatement:="UPDATE ""cklsttestchecklist"" SET Subject = '" & varSubject & "'", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther
End Sub