Format Date in Word

[Camper]

Aloha,

I want to enter a date as m/d/yyyy format in a template file in Word. The
original date was entered as a text string dd mmmm yyyyy in a Word file, not
in Field Code or Table. From the Discussion Groups I was able to assemble the
following code (below). To summarize, the macro starts in Word where date is
searched for and copied; pasted in Excel, formatted and copied; and then
pasted in the Word template. The Dim statements defined, and the Reference
was set to Excel prior to running the macro. But an error occurs at the
"ActiveCell.Paste" line in Excel: Error 91: Objective variable or With block
variable not set.

Any help will be much appreciated. If someone knows of a easier way, I am
all ears. Please let me know if you need additional information.

--
Mahalo for your help,
Camper

____________________

Sub FormatDate()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

' Set workbook.
WorkbookToWorkOn = "C:\Documents and Settings\CampR\My
Documents\dateconversion.xls"

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set xlApp = New Excel.Application
End If

On Error GoTo Err_Handler
Set xlBook = xlApp.Workbooks.Open(FileName:=WorkbookToWorkOn)

' Find and copy date record in document 1.
Windows("DATA SET SUMMARY HA001KAPA1993SEP1").Activate
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Start Date:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.EndKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=2, Extend:=wdExtend
Selection.Copy

' Paste and format date in Excel.
xlBook.Sheets(1).Range("A1").Select
ActiveCell.Paste
xlSelection.NumberFormat = "m/d/yyyy"
Selection.Copy

' Paste formatted date into template.
Windows("SMMS- template2").Activate
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Beginning Date:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.EndKey Unit:=wdLine
Selection.PasteExcelTable False, True, False
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Beginning Date:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.EndKey Unit:=wdLine
Selection.PasteExcelTable False, True, False
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.HomeKey Unit:=wdStory


If ExcelWasNotRunning Then
xlApp.Quit
End If

' Release object reference.
Windows("dateconversion.xls").Activate

Set xlRng = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
xlApp.Quit
End If

End Sub

 

[Jezebel]

First: you need to make a distinction between dates as data types, and
formatted dates, which are strings. The date data type has no format as
such. Dates have a format only when converted to a display. You don't need
to use Excel for this (which won't actually work anyway). VBA's format
function will put the date into any format you like.

And assuming the SMMS file is under your control, it would be simpler to use
a document property or bookmark to set the result in the output --

Dim pRange As Word.Range
Dim pPoint As Long

Set pRange = ActiveDocument.Range
With pRange.Find
.ClearFormatting
.Text = "Start date:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

If pRange.Find.Execute Then
pPoint = pRange.End
pRange.Expand Unit:=wdSentence
pRange.Start = pPoint

Documents("SMMS- template2").CustomDocumentProperties("MyDate") =
Format$(CDate(pRange), "m/d/yyyy")
End If

 

[Camper]

Aloha Jezebel,

Thank you for your help. It is however a bit above my head. So I plugged in
your code and the following problems arose. 1) it did not copy the date, and
2) a type mismatch error occurs, but this may be related to the date
type/string issue. Please let me clarify. The date of a survey, e.g., 29
September 1993., was key stroked into a Word document summarizing the survey.

The Word document does not contain any imbedded code or bookmarks. I have
more than 500 of these documents that requires some information to be
extracted and pasted (in some cases also formatted) into a template file (the
template file meets government requirements, whereas my Word documents are
not in compliance). In addition, the template file does not contain
bookmarks, but I could add them if this would expidate the process.

If you would, please provide additional assistance. I do have a copy of
Microsoft Using Word 2000 Special Edition, so I can look things up with some
guidance.

 

[Jezebel]

First: Step through your code by pressing F8 repeatedly. After this line

check the value of pRange. It should be the date as shown in the document.
If you get an error on any other line, check for typos, and failing that,
post back.

Second: You need to define the document property (called "MyDate" in the
code I gave, but you can use any name you like) -- go to File > Properties
and use the Custom tab. The data type should be string.