Automate Microsoft Word From VB6

[Carter Langley]

Hi guys,girls,others need to automate a report in word using vb6 and the results of a query on an access database. got as far as getting the info from the database now need to put it all in word. problem is the info might contain just one record or it might contain over 200 records, or even more, or nothing. so, never having accessed word from vb6 before and the last time i tried to write code was 8 years ago, i am having some problems here as you can well imagine. help would be greatly appreciated.

[Johan Hakkesteegt]

Hi Carter, Better an imperfect answer than no answer at all: First thing I would consider if I were you is to forget about vb6, and go for vb.net. Its a bit scary and a little frustrating at first but give it two weeks and you'll wonder why you ever considered doing it in vb6. With vb.net it is very easy to retrieve data from any source, stick it into an xml file, which in turn is relatively easy to turn into a word file, or any other MS Office format for that matter. This will also make your code work with pretty much all versions of Word. If that is not an option for you, you should consider starting off with Word and not vb6. In other words, open a word file and play around with Word's own macro functionality, which Visual Basic Editor in itself is roughly the same as vb6 anyway. You could probably just copy most of the code you wrote so far, and you won't have to struggle with getting Word to work and accept input, etc, etc. If that too is of no help to you, I suggest posting a new question on this forum and being way more specific about the problem you are facing. (bits of example code, error messages, specific steps you don't know how to take, etc.) Good luck, Johan

My advice is free, and you may get what you paid for.

[Carter Langley]

Dim strCountry As String Dim strCustomer As String Dim strManufacturer As String Dim strBitMake As String Dim strBitSize As String Dim strMotorType As String Dim strBlockNumber As String Dim strSectionNumber As String Dim strDepthIn As String Dim strDepthOut As String Dim strFootageDrilled As String Dim strFormation As String Dim strFormation1 As String Dim strSearch As String Dim strRst As String Dim strPunctuation As String Dim lngLen As Integer Dim oWord As Word.Application Dim oDoc As Word.Document Dim oTable As Word.Table Dim oPara1 As Word.Paragraph Dim oRng As Word.Range Dim oShape As Word.InlineShape Dim oChart As Object Dim Pos As Double If cboCountry.Text <> "" Then strCountry = cboCountry.Text strRst = "SELECT Countryid FROM Country WHERE Country LIKE " & "'" & strCountry & "'" Rst.Open strRst, Cnn, adOpenDynamic, adLockOptimistic strCountry = "BitRun.CountryID = " & Rst.Fields("Countryid").Value & " AND " Rst.Close strRst = "" Else strCountry = "" End If If cboCustomer.Text <> "" Then strCustomer = cboCustomer.Text strRst = "SELECT Customerid FROM Customer WHERE Customer LIKE " & "'" & strCustomer & "'" Rst.Open strRst, Cnn, adOpenDynamic, adLockOptimistic strCustomer = "BitRun.customerid = " & Rst.Fields("Customerid").Value & " AND " Rst.Close strRst = "" Else strCustomer = "" End If If cboManufacturer.Text <> "" Then strManufacturer = cboManufacturer.Text strRst = "SELECT BitManufacturerid FROM Bit_Manufacturer WHERE Manufacturer LIKE " & "'" & strManufacturer & "'" Rst.Open strRst, Cnn, adOpenDynamic, adLockOptimistic strManufacturer = "BitRun.BitManufacturerid = " & Rst.Fields("BitManufacturerid").Value & " AND " Rst.Close strRst = "" Else strManufacturer = "" End If If cboBitSize.Text <> "" Then strBitSize = "BitRun.Size = '" & cboBitSize.Text & "' AND " Else strBitSize = "" End If If cboMotorType.Text <> "" Then strMotorType = cboMotorType.Text strRst = "SELECT MotorID FROM Motor WHERE MotorType LIKE " & "'" & strMotorType & "'" Rst.Open strRst, Cnn, adOpenDynamic, adLockOptimistic strMotorType = "BitRun.MotorID= " & Rst.Fields("MotorID").Value & " AND " Rst.Close strRst = "" Else strMotorType = "" End

[Johan Hakkesteegt]

Johan wrote:

you won't have to struggle with getting Word to work and accept input, etc, etc.

Carter Langley wrote:

battling is putting it mildly

Which costs more vb.net or your mental health? But seriously, besides the comments I posted earlier, I am afraid I can't help you further. My suggestion is to post only the bottom part of the code you posted in a new question:

Carter Langley wrote:

Set rstSearch = New ADODB.Recordset rstSearch.Open "SELECT * FROM SQLSearchTextString", Cnn, adOpenDynamic, adLockOptimistic If Not rstSearch.EOF Then rstSearch.Delete adAffectCurrent End If rstSearch.AddNew rstSearch.Fields("sqlsearch") = strSearch rstSearch.Update rstSearch.Close 'Start Word and open the document template. Set oWord = CreateObject("Word.Application") oWord.Visible = True Set oDoc = oWord.Documents.Add oDoc.PageSetup.Orientation = wdOrientLandscape oDoc.PageSetup.LeftMargin = 10 oDoc.PageSetup.RightMargin = 10 oDoc.PageSetup.TopMargin = 30 'Insert a paragraph at the beginning of the document. Set oPara1 = oDoc.Content.Paragraphs.Add oPara1.Range.Text = "Diamant Drilling Services Run Report" oPara1.Range.Font.Name = "Arial Rounded MT Bold" oPara1.Range.Font.Color = wdColorBlue oPara1.Range.Font.Size = 28 oPara1.Range.Font.Bold = False oPara1.Range.InsertParagraphAfter oPara1.Range.InsertParagraphAfter Set oPara1 = oDoc.Content.Paragraphs.Add oPara1.Range.Text = "QDC" oPara1.Range.Font.Name = "Arial" oPara1.Range.Font.Color = wdColorBlue oPara1.Range.Font.Size = 8 oPara1.Range.Font.Bold = True It works so far so i must say i am quite pleased with myself. my problem is with the range object i think. i need to have more than one word on a line and i just cant seem to do this. the first line is to have the headers and the following lines are to have the information organised under the relevent headers.

I am sure that someone can help you with this, because it is not at all an uncommon automation project.

Carter Langley wrote:

my probably totally unprofessional attempt at coding

And don't sell yourself short. The question of professional or unprofessional coding is only interesting for the hardcore C++ nerds (they seem to have a real hangup about it :confused:). Good luck with it, Johan

My advic