VBA excel to word doc

[Pinaceous]

Hi All,

I have a code to put my excel onto a word doc in updating the Word Object Library in excel.

I would like to know if I can use a word templet for excel in lieu of generating new word document?

Here are my codes:

Sub Excel_to_Word()


Application.ScreenUpdating = False

Application.GoTo Worksheets(3).Range("A1"), True

'Note: This code requires a reference to the Word Object Library to be set.
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim xlRng As Excel.Range, r As Long, c As Long, FlNm As String
With ActiveWorkbook
  FlNm = ActiveSheet.Name & " " & Format(Now, "YYYYMMDD_hhmm") & ".docx"
  With .ActiveSheet
    With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
      r = .Row
      c = .Column
    End With
    Set xlRng = .Range(.Cells(1, 1), .Cells(r, c))
  End With
End With
With wdApp
  .visible = True
  '.Select
   '.Activate
 
  Set wdDoc = .Documents.Add
  xlRng.Copy
  With wdDoc
        With .PageSetup
      .PaperSize = wdPaperLetter
      .Orientation = wdOrientPortrait
      .LeftMargin = wdApp.InchesToPoints(0)
      .RightMargin = wdApp.InchesToPoints(0.25)
      .TopMargin = wdApp.InchesToPoints(0.25)
      .BottomMargin = wdApp.InchesToPoints(0.45)
        End With
    .Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
    '.SaveAs Filename:=FlNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
    '.Activate
   
    ActiveWindow.WindowState = xlMinimized
   
      With wdApp.Dialogs(wdDialogFileSaveAs)
        .Name = FlNm
        .AddToMRU = False
       
If .Show = False Then GoTo Canceled


    End With
    .Close False
  End With
  .Quit
End With
Application.CutCopyMode = False
Set xlRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing

    ActiveWindow.WindowState = xlMaximized
     
    'Application.Goto Worksheets(28).Range("A1"), True
   
    'Application.ScreenUpdating = True


   Exit Sub

Canceled:
     
       
    ActiveWindow.WindowState = xlMaximized
   
    Call CloseWord
   
    Application.ScreenUpdating = True

    'Application.Goto Worksheets(28).Range("A1"), True


Exit Sub


End Sub


Sub CloseWord()

    Dim W As Object
    On Error Resume Next
    Set W = GetObject(, "Word.Application")
    If W Is Nothing Then Exit Sub
    W.ActiveDocument.Close savechanges:=False
    W.Quit
    Set W = Nothing
 
End Sub

I'm thinking something like this:

    Set Word.Document = Word.Document.CreateItemFromTemplate("S:\\Titled.msg")   'Change path to object.

Please let me know, if you have any suggestions.


Thanks!
pinaceous

 

[Pinaceous]

  Set wdDoc = .Documents.Add(Template:="S:\CMP032A\Terminals\...\test1.docx", NewTemplate:=False, DocumentType:=0)

I got it, sorry about that!

 

[Ali M]

@Pinaceous my knowladge in vba is too poor , but I see maybe should delete this part ThisWorkbook.Path

as far as I know this part is relating where is the file run macro from ,then should save the file in the same place .

also you have error about this extensions docx should be dotx becuase the code search for the file test1.dotx when save new WORD file

just guessing , if it doesn't succeed , then we have to wait for the experts to guid us which the right way .