VBA and Fields in Word and Excel 97/2000 
Author Message
 VBA and Fields in Word and Excel 97/2000

Good morning everybody.
I'm new to VBA.
I'd like to write a macro for Word and or Excel 97/2000, which allows me
to insert fields in a document/sheet.
I regularly use the "File - Properties" of a Word or Excel document/sheet
and fill the fields "Title", "Subject" and "Company" under the summary
tab. I also create a "Client", a "Date" and a "Suivi" field under the
custom tab.
So I'd like a macro which opens a window in which all those fields are
listed for me to fill them, and then writes those filled fields in the
File Properties.
Could somebody do this for me or help me to do it?
Many thanks in advance,
Pierre


I'm sorry for my poor English.



Sun, 04 May 2003 03:00:00 GMT  
 VBA and Fields in Word and Excel 97/2000
Hi Pierre,

This is how you could do it in Word. Make an userform in the Visual Basic
Editor and add six textboxes and one commandbutton on it.
In my example code the textboxes are named like the properties but with the
prefix 'Txt', the commandbutton is named Commandbutton1

Here's the code that's behind the form:
-----------------------------------------------------------
Option Explicit

Private Sub CommandButton1_Click()
  On Error Resume Next

    ActiveDocument.BuiltInDocumentProperties _
    (wdPropertyTitle).Value = TxtTitle.Text
    ActiveDocument.BuiltInDocumentProperties _
    (wdPropertySubject).Value = TxtSubject.Text
    ActiveDocument.BuiltInDocumentProperties _
    (wdPropertyCompany).Value = TxtCompany.Text
    WriteDocProp sName:="Client", sValue:=TxtClient.Text
    WriteDocProp sName:="Date", sValue:=TxtDate.Text
    WriteDocProp sName:="Suivi", sValue:=TxtSuivi.Text
    Unload Me

End Sub

Private Sub UserForm_Initialize()

  On Error Resume Next
  'Just in case one of your customproperties
  'isn't made yet

  'Add the current values to the textboxes
  TxtTitle.Text = ActiveDocument.BuiltInDocumentProperties _
                  (wdPropertyTitle).Value
  TxtSubject.Text = ActiveDocument.BuiltInDocumentProperties _
                  (wdPropertySubject).Value
  TxtCompany.Text = ActiveDocument.BuiltInDocumentProperties _
                  (wdPropertyCompany).Value
  TxtClient.Text = ActiveDocument.CustomDocumentProperties _
                  ("Client").Value
  TxtDate.Text = ActiveDocument.CustomDocumentProperties _
                  ("Date").Value
  TxtSuivi.Text = ActiveDocument.CustomDocumentProperties _
                  ("Suivi").Value

End Sub

Sub WriteDocProp(sName As String, sValue As String)

  On Error GoTo ErrHandlerWriteDocProp
  'There needs to be a value for the property
  If Len(sValue) > 0 Then
    'This line will generate an error if the
    'property is not made yet,
    'in that case, the property is made in the errorhandler
    ActiveDocument.CustomDocumentProperties(sName).Value = sValue
  End If
  Exit Sub

ErrHandlerWriteDocProp:
  Err.Clear
  ActiveDocument.CustomDocumentProperties.Add Name:=sName,
LinkToContent:=False, _
  Type:=msoPropertyTypeString, Value:=sValue

End Sub

-----------------------------------------------------------

Hope this helps,
regards,
Astrid

For direct access to all Microsoft newsgroups:



Quote:
> Good morning everybody.
> I'm new to VBA.
> I'd like to write a macro for Word and or Excel 97/2000, which allows me
> to insert fields in a document/sheet.
> I regularly use the "File - Properties" of a Word or Excel document/sheet
> and fill the fields "Title", "Subject" and "Company" under the summary
> tab. I also create a "Client", a "Date" and a "Suivi" field under the
> custom tab.
> So I'd like a macro which opens a window in which all those fields are
> listed for me to fill them, and then writes those filled fields in the
> File Properties.
> Could somebody do this for me or help me to do it?
> Many thanks in advance,
> Pierre


> I'm sorry for my poor English.



Mon, 05 May 2003 07:18:19 GMT  
 VBA and Fields in Word and Excel 97/2000
Now that Astrid has answered the question you asked, is there some reason
you don't want to simply open the Properties Dialog box?

You could combine that with an input box since only one of your properties
is a custom property. Use the input box for the Custom Property (and if you
want to prompt the user as to which fields to fill in in the Properties
Dialog box).

Just a thought.

--
Charles Kenyon

Word New User FAQ & Web Directory:
http://www.addbalance.com/word

Legal Users Guide (modified)
http://www.addbalance.com/usersguide
 --------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.


Quote:
> Good morning everybody.
> I'm new to VBA.
> I'd like to write a macro for Word and or Excel 97/2000, which allows me
> to insert fields in a document/sheet.
> I regularly use the "File - Properties" of a Word or Excel document/sheet
> and fill the fields "Title", "Subject" and "Company" under the summary
> tab. I also create a "Client", a "Date" and a "Suivi" field under the
> custom tab.
> So I'd like a macro which opens a window in which all those fields are
> listed for me to fill them, and then writes those filled fields in the
> File Properties.
> Could somebody do this for me or help me to do it?
> Many thanks in advance,
> Pierre


> I'm sorry for my poor English.



Mon, 12 May 2003 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. VBA and Fields in Word and Excel 97/2000

2. VBA and Fields in Word and Excel 97/2000

3. Excel 97 VBA vs Excel 2000 VBA

4. Compatibility Problem Using MS Excel 97 VBA on Excel 2000/XP - Causes crashes

5. vba problem word 97 and not word 2000

6. Differences between VBA for Word 2000 and Word 97

7. Word 97 and Word 2000 VBA - Functionality Lost?

8. Import From Excel 2000 with VBA code into Access 97

9. How do you send Outlook 2000 email using WORD 97 VBA Macro

10. Word 2000/97 & VBA

11. Access 97/2000 Field in Word Document

12. convert vba 2000 to vba 97

 

 
Powered by phpBB® Forum Software