export to word issue

[BrendanDixon]

Hi All,

I am trying to export to word from excel. I am having 2 issues that I cannot seem to resolve.

1. If word is already open then the code will just crash. I suspect that I need to check it the word app exists then it needs to do something different in the line

Set wordApp = New Word.Application

2. I have a random Issue that sometimes while doing the Page setup of margins the code will crash. I have no idea why sometimes it work and sometimes it crashes. I did try put a delay before this to see if it helped but it still does the same thing. Does anyone know how I can improve on these?

Sub ExcelToWord()
   'Using Early Binding
    Dim wordApp As Word.Application
    Dim mydoc As Word.Document
    Dim Name As String
    
    Name = "C:\Users\brend\Desktop\27467\" & "test.docx"

    'Creating a new instance of word only if there no other instances
    Set wordApp = New Word.Application

    'Making word App Visible
    wordApp.Visible = True

    'Creating a new document
    Set mydoc = wordApp.Documents.Add()

    'copying the content from excel sheet
    ThisWorkbook.Sheets("Sheet1").Range("A1:I30").Copy

    'Pasting on the document
    mydoc.Paragraphs(1).Range.PasteAndFormat (wdFormatOriginalFormatting)
    
    'setup page format narrow margins
    With mydoc.PageSetup
        .TopMargin = InchesToPoints(0.5)
        .BottomMargin = InchesToPoints(0.5)
        .LeftMargin = InchesToPoints(0.5)
        .RightMargin = InchesToPoints(0.5)
    End With

    'saving the document
    mydoc.SaveAs Name

    'closing the document
    mydoc.Close
    wordApp.Quit

    'Emptying the Clipboard
    CutCopyMode = False

  

End Sub

 

[BrendanDixon]

Managed to sort out the Page Setup issue. Changed the Page setup section as below

With mydoc.PageSetup
        .TopMargin = wordApp.InchesToPoints(0.5)
        .BottomMargin = wordApp.InchesToPoints(0.5)
        .LeftMargin = wordApp.InchesToPoints(0.5)
        .RightMargin = wordApp.InchesToPoints(0.5)
End With