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?
Printable View
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?
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?
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!
Try this for starters.
VB Code:
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
That all most the same code I use to start the doc!
So how can I tell when they close the doc?Code:Call WordMain
LoadDocument "FillText40", strPct_of_Time_Overflow '' pg3 Pct of Time_Overflow
objWord.ActiveDocument.Protect wdAllowOnlyFormFields
objWord.Visible = True
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.
Thank I found it! I think I will have more ??? so I am not checking the thread off!
RobDog888
This code would go in excel?
"
Option Explicit
Private RD As clsMyEvents
"
I am using a .dot for this project!
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.
I am east coast!
It is near quitting time here.
Will a macro have to go into my system?
Thank for the help!
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.
VB/Outlook Guru!VB Code:
'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
:D
:D
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!
:D
:mad:
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!
:sick:
Compile error
User-defined type not defined
On
Private RD As clsMyEvents
I have the word reference in the project
Its behind the word document (VBA).
Can I get this to work out of VB to a .dot?
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]
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.Quote:
Originally posted by RobDog888
What is the way you hope to have this working?
From VB and Word Template
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?
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.
Yes I can see that been a problem.
They have pull me off for right now, but there is always tomorrow!
:rolleyes:
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.
Did you try the document attachment in my post that shows the code?
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
You changed the message in this line?
You shouldnt see any more code in the word doc module (ThisDocument)VB Code:
iResp = MsgBox("RobDog888: Do you want to save your document?", vbYesNoCancel + vbQuestion, "My Word Events")
and class file (clsMyEvents) than what I posted earlier.
:confused:
Yes
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?
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.
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?
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.
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.
It like this "MyWordEvents", like the doc name!
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.
It late here 5:30 come fast.
I try at work.
Thanks.
Ok, see you tomorrow. Its 7:00 pm here.
Later
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:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office: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>
<o:DocumentProperties>
<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>
</o:DocumentProperties>
</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-format:other;
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-format:other;
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>
"
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. :D
I am get 2 custom and 1 word message! If I do not save.Quote:
Originally posted by RobDog888
You say you still get the Word save message meaning that
you are not getting the custom save message?
:D
If I save I get 3 custom.
So you think ADO will work?
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.
VB/Outlook Guru!VB Code:
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
:D
Great!
Soon as I am allowed to work on the program again, I will try it!
:rolleyes:
Thanks again.
:bigyello:
No prob.
Later.