Word doc

[mudfish]

From VB I shell a word doc, for user entry! When they close the doc, is there a way to capture what the user adds to the doc for the database?

[Buzby]

Silly question buy why are you using a Word Doc for user entry? Wouldn't it be easier to create the form in VB and link it to a database?

[mudfish]

Yes it would but the boss would like me to do it like this! I can put data in it, but out I do not know how I would do it! I have also lost control once I shell it!

[RobDog888]

Try this for starters.

Option Explicit
'Add a reference to MS Word xx.0 Object Library
Private moApp As Word.Application
Private moDoc As Word.Document
 
Private Sub cmdOpenDoc_Click()
    Set moDoc = moApp.Documents.Open("D:\Development\MyDoc.doc")
End Sub
 
Private Sub Form_Load()
    Set moApp = New Word.Application
    moApp.Visible = True
End Sub

[mudfish]

That all most the same code I use to start the doc!

    Call WordMain
    LoadDocument "FillText40", strPct_of_Time_Overflow                         '' pg3 Pct of Time_Overflow
    objWord.ActiveDocument.Protect wdAllowOnlyFormFields
    objWord.Visible = True

So how can I tell when they close the doc?

[RobDog888]

You need to create either a WithEvents event to intercept the
Document_Close event or if you are using the same document all
the time you can add a module and a class module to take full
control of Words events. I have to go to client site right now, but
if no one can help by the time I get back, I will help you more.

Oh ya, search in code bank for my Excel Event demo. Its the basis
for what you want but in Excel.

Later.

[mudfish]

Thank I found it! I think I will have more ??? so I am not checking the thread off!

[mudfish]

RobDog888

This code would go in excel?

"
Option Explicit

Private RD As clsMyEvents
"

I am using a .dot for this project!

[RobDog888]

No, the logic is what you want to replicate in Word. I just got
back from the Clients site and I am going to lunch. Be back with
an example after lunch.

[mudfish]

I am east coast!
It is near quitting time here.

Will a macro have to go into my system?

Thank for the help!

[RobDog888]

I got the demo attached for you, but because of the immediatness
of the request, I only had time to do it in Word. If you need to
use VB to simulate the same thing let me know.

'ThisDocument
Option Explicit
 
Private RD As clsMyEvents
 
Private Sub Document_Open()
    Set RD = New clsMyEvents
    MsgBox "My Events Created!", vbOKOnly + vbInformation, "My Word Events"
End Sub
 
 
'Name: clsMyEvents
'Instancing: Private
Option Explicit
 
Public goWord As Word.Application
 
Public WithEvents MyEventsDoc As Word.Document
Public WithEvents MyEventsApp  As Word.Application
 
Private Sub Class_Initialize()
    Set goWord = GetObject(, "Word.Application")
    Set MyEventsDoc = ThisDocument
    Set MyEventsApp = goWord
End Sub
 
Private Sub Class_Terminate()
    Set goWord = Nothing
End Sub
 
Private Sub MyEventsApp_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
    Dim iResp As Integer
    iResp = MsgBox("RobDog888: Do you want to save your document?", vbYesNoCancel + vbQuestion, "My Word Events")
    If iResp = vbYes Then
        goWord.ActiveDocument.Save
        If goWord.ActiveDocument.Saved = True Then
            MsgBox "Saved!", vbOKOnly + vbInformation, "My Word Events"
        Else
            MsgBox "Error Saving Document!" & vbNewLine & "Sorry!", vbOKOnly + vbCritical, "My Word Events"
        End If
    ElseIf iResp = vbCancel Then
        Cancel = True
    End If
End Sub

VB/Outlook Guru!

[mudfish]

At home, had to see the code!
Take some time!
My work day was over.
I am not set up at home to run what I was working on.
Thank for the help!

PS We run preloaded machines and push code down our lines!
That why I ask for about a macro!

[mudfish]

Fire wall will not let me download!
I am working in VB 6.5!

This code is on a form?
"
'ThisDocument
Option Explicit

Private RD As clsMyEvents

Private Sub Document_Open()
Set RD = New clsMyEvents
MsgBox "My Events Created!", vbOKOnly + vbInformation, "My Word Events"
End Sub

"
Should have got the download from the house!




Compile error
User-defined type not defined

On

Private RD As clsMyEvents

I have the word reference in the project

[RobDog888]

Its behind the word document (VBA).

[mudfish]

Can I get this to work out of VB to a .dot?

[RobDog888]

What is the way you hope to have this working?
[list=1][*]From VB only[*]From Word only[*]From VB and Word Template[/list=1]

[mudfish]

Originally posted by RobDog888
What is the way you hope to have this working?
From VB and Word Template

I have a VB form that I am filling a listbox with name from the database. User picks a name and I fill the dot with data from the database and shell the dot. The user can fill in some fields in the dot and print it. They want me to be able to shell it blank, let user fill in and save it back to the database. I am working on doing it in VBA. We have a large area network that our machines are preloaded with the VB package, so we can just transmit code down our lines. So if I add code to the dot I can just transmit it down the line with the code.

This should work; the code in VBA is attached to the dot, right?

I have your word doc got to the code change it some, but I do not see the changes on the word doc. How do I compile in VBA?

[RobDog888]

I think the only version of Office VBA that had a compiler was
Office XP Developer. I always had problems creating and trapping
events in Word from VB, but I can give it another try later today.
One of the problems was when the user had more than one
instance of wrod running.

[mudfish]

Yes I can see that been a problem.
They have pull me off for right now, but there is always tomorrow!

Thanks for the input!
I got in your code on the word doc and change the message, but I do not see it changing.
I see a huge script in it also.

[RobDog888]

Did you try the document attachment in my post that shows the code?

[mudfish]

Yes I got in your code on the word doc and change the closing message, but I do not see the message changing when I would close the doc?

I also see a huge VS script in the word doc also.

Thanks again

[RobDog888]

You changed the message in this line?

iResp = MsgBox("RobDog888: Do you want to save your document?", vbYesNoCancel + vbQuestion, "My Word Events")

You shouldnt see any more code in the word doc module (ThisDocument)
and class file (clsMyEvents) than what I posted earlier.

[mudfish]

Yes

[RobDog888]

You close the document and save the changes? Then when you
open the document, the welcome message is still the same or
when you close the document at this point still should the original message?

[mudfish]

I was looking for it at close.

When I try and run it in the VBA like I would in VB it wants me to name and create a macro, I think! It is not in front of me, at home!
I have 6.5 VB, C++ 6 XP office Pro and .Net studio.

[RobDog888]

Does the code reflect the change the next time you open the doc?
Maybe it was stuck in memory or the vba code was in run mode?

What is the cange you want in the close message?

[mudfish]

I add some number to "45456RobDog888: Do you want to save your document".
But I am only getting "Do you want to save your document".

I wish I had it here, I also have SQL enterprise manager, XP network and Multi-edit.

[RobDog888]

Ah! I think its not running the custome msgbox. If the title of the
close msgbox does not say "My Word Events" then it is the Word
msgbox and not ours firing.

[mudfish]

It like this "MyWordEvents", like the doc name!

[RobDog888]

Oh ya, check to make sure that you have Macros enabled when
word starts. Set the marco security to medium so you can choose
the setting each time Word starts. I get the feeling that its not
runing.

Edit: the save msgbox title should be exactly - "My Word Events"
if its running. Not the document name.

[mudfish]

It late here 5:30 come fast.
I try at work.
Thanks.

[RobDog888]

Ok, see you tomorrow. Its 7:00 pm here.

Later

[mudfish]

7:30 am at work change the marco security and it works.
I still get the word save message.


Can I use SQL in the word project like ADO just like in VB?

Thanks again.

PS this is out of script editor
"
<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:w="urn:schemas-microsoft-comffice:word"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Word.Document>
<meta name=Generator content="Microsoft Word 10">
<meta name=Originator content="Microsoft Word 10">
<link rel=File-List href="MyWordEvents_files/filelist.xml">
<link rel=Edit-Time-Data href="MyWordEvents_files/editdata.mso">
<title>My Word Events Demo</title>
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Subject>Word Automation</o:Subject>
<o:Author>RobDog888</o:Author>
<o:LastAuthor>ckupfer</o:LastAuthor>
<o:Revision>9</o:Revision>
<o:TotalTime>6</o:TotalTime>
<o:Created>2004-09-30T12:04:00Z</o:Created>
<o:LastSaved>2004-09-30T12:16:00Z</o:LastSaved>
<o:Pages>1</o:Pages>
<o:Words>1</o:Words>
<o:Characters>6</o:Characters>
<o:Company>Advanced Program Solutions</o:Company>
<o:Lines>1</o:Lines>
<o:Paragraphs>1</o:Paragraphs>
<o:CharactersWithSpaces>6</o:CharactersWithSpaces>
<o:Version>10.3501</o:Version>
</oocumentProperties>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Print</w:View>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:UseFELayout/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
</w:WordDocument>
</xml><![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:PMingLiU;
panose-1:2 1 6 1 0 1 1 1 1 1;
mso-font-alt:\65B0\7D30\660E\9AD4;
mso-font-charset:136;
mso-generic-font-family:auto;
mso-font-formatther;
mso-font-pitch:variable;
mso-font-signature:1 134742016 16 0 1048576 0;}
@font-face
{font-family:"\@PMingLiU";
panose-1:0 0 0 0 0 0 0 0 0 0;
mso-font-charset:136;
mso-generic-font-family:auto;
mso-font-formatther;
mso-font-pitch:variable;
mso-font-signature:1 134742016 16 0 1048576 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:"";
margin:0in;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:PMingLiU;
mso-fareast-language:ZH-TW;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
mso-paper-source:0;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";}
</style>
<![endif]-->
</head>

<body lang=EN-US style='tab-interval:.5in'>

<div class=Section1>

<p class=MsoNormal>456est</p>

</div>

</body>

</html>
"

[RobDog888]

You say you still get the Word save message meaning that
you are not getting the custom save message?

You mean that you want the project to be in Words VBA and you
want to know if you can use SQL in there then the answer is yes,
I think so. I havent tried it but it should be possible.

Let me know.

Its 9:00 am over here now. I just got in.

[mudfish]

Originally posted by RobDog888
You say you still get the Word save message meaning that
you are not getting the custom save message?

I am get 2 custom and 1 word message! If I do not save.
If I save I get 3 custom.

So you think ADO will work?

[RobDog888]

I just had to see if I could make you a example using ADO and SQL.
So here it is.

Note this is separate from the other document and not inclusive
of what we have done earlier.

Option Explicit
'RobDog888 10/01/2004
'ThisDocument
'Add reference to MS ActiveX Data Objects 2.x Library
'Tools > References ...
Private Sub Document_Open()
    'Initialize SQL connection and objects
    Main
    Stop
    'Command1_Click
End Sub
 
Private Sub Command1_Click()
 
    On Error GoTo No_Bugs
    
    Dim oRs As ADODB.Recordset
 
    Set oRs = New ADODB.Recordset
    Set oRs = goEnv.SQLTie.QrySQLTable("FindMe")
    If oRs.BOF = False And oRs.EOF = False Then
        'Do something with the recordset
        Do While oRs.EOF = False
            '...
            '...
            '...
            oRs.MoveNext
        Loop
    Else
        MsgBox "No Records Returned!", vbOKOnly + vbExclamation, "RobDog888 - Command1_Click"
    End If
    If oRs.State = adStateOpen Then oRs.Close
    Set oRs = Nothing
    Exit Sub
    
No_Bugs:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation, "RobDog999 - Command1_Click"
End Sub
 
'********************************************************************************************************
 
Option Explicit
'RobDog888 10/01/2004
'Module: modMain
Public goEnv                     As clsEnv
 
Public Sub Main()
 
    Set goEnv = New clsEnv
    'Change the server and database names to yours
    If goEnv.DBConnect("\\ServerName\DatabaseName") = False Then
        MsgBox "Failed to connect to database!" & vbNewLine & _
        "Please contact an Administrator to check if you have the appropriate permissions.", _
        vbExclamation + vbOKOnly, "RobDog888 - Main"
        Set goEnv = Nothing
    Else
        MsgBox "Connected to SQL Database!", vbOKOnly + vbInformation, "RobDog888 - Main"
    End If
 
End Sub
 
'********************************************************************************************************
 
Option Explicit
'RobDog888 10/01/2004
'Class: clsEnv
'Instancing: Private
Private moCnn As ADODB.Connection
Private moSQLTie As clsSQL
 
Public Property Get Cnn() As ADODB.Connection
    Set Cnn = moCnn
End Property
 
Public Property Get SQLTie() As clsSQL
    Set SQLTie = moSQLTie
End Property
 
Public Function DBConnect(ByVal sDBPath As String) As Boolean
 
    On Error GoTo No_Bugs
 
    Dim sDB As String
    Dim sSQL As String
    
    sDB = InStrRev(sDBPath, "\")
    sSQL = Mid(sDBPath, 1, sDB - 1)
    sDB = Mid(sDBPath, sDB + 1)
    sSQL = Mid(sSQL, 3)
    If moCnn.State = adStateClosed Then
        moCnn.ConnectionString = "provider=sqloledb;data source=" & sSQL & ";initial catalog=" & sDB & _
        ";integrated security=sspi;"
        'Add "integrated security=sspi"
        moCnn.Open
        DBConnect = True
    Else
        moCnn.Close
        moCnn.ConnectionString = "provider=sqloledb;data source=" & sSQL & ";initial catalog=" & sDB & _
        ";integrated security=sspi;"
        moCnn.Open
        DBConnect = True
    End If
    Exit Function
 
No_Bugs:
 
    If Err.Number = "-2147467259" Then
        MsgBox "Error connecting to database!", vbOKOnly + vbCritical, "RobDog888 - DBConnect"
    Else
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, "RobDog888 - DBConnect"
    End If
    DBConnect = False
    Exit Function
    Resume
End Function
 
Private Sub Class_Initialize()
    Set moCnn = New ADODB.Connection
    Set moSQLTie = New clsSQL
End Sub
 
Private Sub Class_Terminate()
    Set moCnn = Nothing
    Set moSQLTie = Nothing
End Sub
 
 
'********************************************************************************************************
 
Option Explicit
'RobDog888 10/01/2004
'Class: clsSQL
'Instancing: Private
Public Function QrySQLTable(ByVal sAction As String) As ADODB.Recordset
 
    On Error GoTo No_Bugs
 
    Dim oRs As New ADODB.Recordset
    Dim sSQL As String
 
    sSQL = "SELECT"
    sSQL = sSQL & " Field1,"
    sSQL = sSQL & " Field2,"
    sSQL = sSQL & " Field3"
    sSQL = sSQL & " FROM"
    sSQL = sSQL & " Table1"
    sSQL = sSQL & " WHERE"
    sSQL = sSQL & " Field1 = '" & sAction & "'"
    
    Set oRs = New ADODB.Recordset
    oRs.Open sSQL, goEnv.Cnn, adOpenKeyset, adLockOptimistic, adCmdText
    Set QrySQLTable = oRs
    Set oRs = Nothing
    Exit Function
    
No_Bugs:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, "RobDog888 - QrySQLTable"
End Function

VB/Outlook Guru!

[mudfish]

Great!
Soon as I am allowed to work on the program again, I will try it!

Thanks again.

[RobDog888]

No prob.

Later.