jdsouza
Board Regular
- Joined
- Jul 19, 2012
- Messages
- 105
Good day
I am creating a MS Word document with data from my Excel workbook and it works OK if the user has Office 2010 as the Excel project is built using Office 2010.
I set the references to Microsoft Word 14.0 to get access to the Word parameters.
However, the project won't open with Compile Error in Hidden Module ThisWorkbook when the Excel project is opened on a machine with Office 2007.
I tried changing the line Set wD=Word.Application to an object - See code. With the Object method, the alignments are not working and selection.MoveRight does not move the cursor point.
Kindly help with remedies.
I am creating a MS Word document with data from my Excel workbook and it works OK if the user has Office 2010 as the Excel project is built using Office 2010.
I set the references to Microsoft Word 14.0 to get access to the Word parameters.
However, the project won't open with Compile Error in Hidden Module ThisWorkbook when the Excel project is opened on a machine with Office 2007.
I tried changing the line Set wD=Word.Application to an object - See code. With the Object method, the alignments are not working and selection.MoveRight does not move the cursor point.
Kindly help with remedies.
Code:
Public Sub TestMW()
On Error Resume Next
Dim XX As Integer, I As Integer, J As Integer, b() As String, JJ As Integer, s As String, a(9) As String
Dim sM As String
'-------------------------------------------
'1.
'With As Object, formatting and MoveRight do not work.
'I have to remove the references as I am using Office 2010 and end users have Office 2007
Dim oWord As Object
Dim wD As Object
Set oWord = CreateObject("Word.Application")
' The Visible property is called via IDispatch
Set wD = oWord
wD.Visible = True
'-------------------------------------------
'2. Using Office 2010 Version 14.0 - I do not have Office 2007
' With Set wd = New Word.Application and references to Word 14.0 checked - code works OK.
' Set wd = New Word.Application
'-------------------------------------------
wD.ScreenUpdating = False
With wD.Documents
.Add DocumentType:=wdNewBlankDocument
DoEvents
wD.Activate
DoEvents
DoEvents
wD.Options.MeasurementUnit = wdCentimeters
With wD.ActiveDocument.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientPortrait
.TopMargin = 32
.BottomMargin = 22
.LeftMargin = 70 '
.RightMargin = 30
.Gutter = 0
.HeaderDistance = 28
.FooterDistance = 11
.PageWidth = 595
.PageHeight = 842
.FirstPageTray = wdPrinterDefaultBin
.OtherPagesTray = wdPrinterDefaultBin
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.SuppressEndnotes = False
.MirrorMargins = False
.TwoPagesOnOne = False
.BookFoldPrinting = False
.BookFoldRevPrinting = False
.BookFoldPrintingSheets = 1
.GutterPos = wdGutterPosLeft
End With
wD.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
wD.WordBasic.openorcloseparabelow
wD.Selection.TypeParagraph
wD.Selection.Font.Size = 16
wD.Selection.Font.Name = "Calibri"
wD.Selection.Font.Color = wdColorAutomatic
wD.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
wD.Selection.Font.Bold = wdToggle
wD.Selection.TypeText Text:="WHATEVER"
' CheckSer
If frmLoad = False Then
wD.Selection.Font.Size = 24
wD.Selection.TypeParagraph
wD.Selection.TypeText Text:="TEST M O D E"
End If
wD.Selection.Font.Bold = wdToggle
wD.Selection.TypeParagraph
wD.Selection.TypeParagraph
wD.Selection.Font.Size = 10
wD.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
wD.Paragraphs(1).TabStops.ClearAll
wD.Selection.TypeText Text:="Some Text "
wD.Selection.Font.Bold = wdToggle
wD.Selection.TypeText Text:="Some Text "
wD.Selection.Font.Bold = wdToggle
' WD.Selection.ParagraphFormat.TabStops.Add Position:=application.CentimetersToPoints(9), _
' Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
wD.Selection.Paragraphs.TabStops.Add Position:=Application.CentimetersToPoints(9), _
Alignment:=wdAlignTabLeft ', Leader:=wdTabLeaderSpaces
DoEvents
wD.Selection.TypeText Text:=vbTab & "Owner:"
wD.Selection.TypeParagraph
' WD.Selection.ParagraphFormat.TabStops.Add Position:=application.CentimetersToPoints(9), _
' Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
wD.Selection.Paragraphs.TabStops.Add Position:=Application.CentimetersToPoints(9), _
Alignment:=wdAlignTabLeft ', Leader:=wdTabLeaderSpaces
wD.Selection.TypeText Text:="Some Text " & vbTab & "Some Text "
wD.Selection.TypeParagraph
' WD.Selection.ParagraphFormat.TabStops.Add Position:=application.CentimetersToPoints(9), _
' Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
wD.Selection.Paragraphs.TabStops.Add Position:=Application.CentimetersToPoints(9), _
Alignment:=wdAlignTabLeft ', Leader:=wdTabLeaderSpaces
wD.Selection.TypeText Text:="Some Text " & "Some Text " & vbTab & "Some Text "
wD.Selection.TypeParagraph
wD.Selection.TypeText Text:="Date: " & Format(Now, "mmmm dd, yyyy")
' wd.Selection.TypeParagraph
wD.Selection.TypeParagraph
wD.Selection.TypeParagraph
wD.Selection.ParagraphFormat.Alignment = wdAlignParagraphJustify
s = "Some Text Some Text Some Text Some Text Some Text Some Text Some Text Some Text "
s = s & "Some Text Some Text Some Text Some Text Some Text Some Text Some Text Some Text "
wD.Selection.TypeText Text:=s
wD.Selection.TypeParagraph
' wd.Selection.TypeParagraph
wD.Selection.TypeParagraph
wD.Selection.TypeText Text:=" Some Text _____________________________ at _______________________"
wD.Selection.TypeParagraph
wD.Selection.TypeParagraph
wD.Selection.TypeParagraph
wD.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
wD.ActiveDocument.Tables.Add Range:=wD.Selection.Range, NumRows:=1, NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitContent
With wD.Selection.Tables(1)
If .Style <> "Table Grid 4" Then
.Style = "Table Grid 4"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = 0 ' Options.DefaultBorderColor
End With
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = 0 ' Options.DefaultBorderColor
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = 0 ' Options.DefaultBorderColor
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = 0 ' Options.DefaultBorderColor
End With
End With
' wd.Selection.Cells.VerticalAlignment = wdCellAlignVerticalCenter
wD.Selection.TypeText Text:="Grade"
' wd.Selection.Cells.VerticalAlignment = wdCellAlignVerticalCenter
wD.Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
wD.Selection.Columns.PreferredWidth = 200
wD.Selection.MoveRight Unit:=wdCell
wD.Selection.TypeText Text:="Quantity (in MT)"
' wd.Selection.Cells.VerticalAlignment = wdCellAlignVerticalCenter
wD.Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
wD.Selection.Columns.PreferredWidth = 90
wD.Selection.MoveRight Unit:=wdCell
''' WD.Selection.TypeText Text:="Number of Tanks used"
''' WD.Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
''' WD.Selection.Columns.PreferredWidth = 110
''' WD.Selection.MoveRight Unit:=wdCell
wD.Selection.TypeText Text:="Some Text "
wD.Selection.MoveRight Unit:=wdCell
wD.Selection.TypeText Text:=" HFO HS"
wD.Selection.MoveRight Unit:=wdCell
wD.Selection.TypeText Text:="Some Text"
' wd.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
wD.Selection.MoveRight Unit:=wdCell
' wd.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
DoEvents
wD.ScreenUpdating = True
wD.Visible = True
End With
DoEvents
wD.Application.Activate
Set wD = Nothing
End Sub