MGW1138
01-26-2015, 02:53 PM
Object of the exercise: 1) Use OCR to convert PDF Electrical Print to Excel
2) Use VBA to import notes from Excel into the Word Document Table(3) - First two tables concern non-print data.
The Script follows with these results:
"First Blank Out" writes to table cell (7,8) correctly and is overwritten by next command correctly.
"Second Blank Out" writes to table cell (7,8) correctly.
"Third Blank Out" writes to table cell (7,8) correctly
However the Selection.Range.ListFormat.ApplyListTemplateWithLevel Method starts the number List in Cell(5,8) Not (7,8)
as desired.
Note that rows 1 through 4 of the table are in the doc header. I have looked for information on the Method without
results.
Any help would be appreciated. Thanks
Michael Wandrey
Sub ExtractExcelData()
Dim I As Long
Dim J As Long
Dim s As String
Dim sw As String
Dim W As Workbook
Dim E As New Excel.Application
Dim NewNum As Boolean
Set W = E.Workbooks.Open("H:\fai\FAI OCR Test.xls")
s = W.Sheets("Overview").Range("C4")
For I = 3 To 3
NewNum = True
'ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "First Blank out"
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "" ' writes to table cell 7,8 correctly
For J = 1 To 30
s = W.Sheets("Overview").Cells(J, 3)
If Left(s, 1) = I And Mid(s, 2, 1) = "." Then
If NewNum = True Then
NewNum = False
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "Second Blank Out" 'also writes to cell 7,8
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "" 'also writes to cell 7,8
With ListGalleries(wdNumberGallery).ListTemplates(1).ListLevels(1)
.NumberFormat = Str(I) & ".%1"
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleArabec
.NumberPosition = InchesToPoints(0.25)
.Alignment = wdListLevelAlignCenterAlignLeft
.TextPosition = InchesToPoints(0.5)
.TabPosition = wdUndefined
.ResetOnHigher = 0
.StartAt = 1
.LinkedStyle = ""
End With
ListGalleries(wdNumberGallery).ListTemplates(1).Name = ""
'Next line starts numbered sequence in cell 5,8 ????? ============?
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:=False, _
ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:=wdWord10ListBehaviour
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "Third Blank out"
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = ""
Selection.TypeText Text:=s
Else
Selection.TypeParagraph
Selection.TypeText Text:=s
End If
End If
Next J
Next I
W.Close
End Sub
2) Use VBA to import notes from Excel into the Word Document Table(3) - First two tables concern non-print data.
The Script follows with these results:
"First Blank Out" writes to table cell (7,8) correctly and is overwritten by next command correctly.
"Second Blank Out" writes to table cell (7,8) correctly.
"Third Blank Out" writes to table cell (7,8) correctly
However the Selection.Range.ListFormat.ApplyListTemplateWithLevel Method starts the number List in Cell(5,8) Not (7,8)
as desired.
Note that rows 1 through 4 of the table are in the doc header. I have looked for information on the Method without
results.
Any help would be appreciated. Thanks
Michael Wandrey
Sub ExtractExcelData()
Dim I As Long
Dim J As Long
Dim s As String
Dim sw As String
Dim W As Workbook
Dim E As New Excel.Application
Dim NewNum As Boolean
Set W = E.Workbooks.Open("H:\fai\FAI OCR Test.xls")
s = W.Sheets("Overview").Range("C4")
For I = 3 To 3
NewNum = True
'ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "First Blank out"
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "" ' writes to table cell 7,8 correctly
For J = 1 To 30
s = W.Sheets("Overview").Cells(J, 3)
If Left(s, 1) = I And Mid(s, 2, 1) = "." Then
If NewNum = True Then
NewNum = False
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "Second Blank Out" 'also writes to cell 7,8
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "" 'also writes to cell 7,8
With ListGalleries(wdNumberGallery).ListTemplates(1).ListLevels(1)
.NumberFormat = Str(I) & ".%1"
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleArabec
.NumberPosition = InchesToPoints(0.25)
.Alignment = wdListLevelAlignCenterAlignLeft
.TextPosition = InchesToPoints(0.5)
.TabPosition = wdUndefined
.ResetOnHigher = 0
.StartAt = 1
.LinkedStyle = ""
End With
ListGalleries(wdNumberGallery).ListTemplates(1).Name = ""
'Next line starts numbered sequence in cell 5,8 ????? ============?
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:=False, _
ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:=wdWord10ListBehaviour
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = "Third Blank out"
ActiveDocument.Tables(3).Cell(I + 4, 8).Range.Text = ""
Selection.TypeText Text:=s
Else
Selection.TypeParagraph
Selection.TypeText Text:=s
End If
End If
Next J
Next I
W.Close
End Sub