Sub Create DOCX
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: Sub Create DOCX
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, NY, LI
    Posts
    534
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Sub Create DOCX

    Hi All,

    I'm using this sub to create a PDF from an Excel's worksheet.

    It works really great!

    Now, after careful review of my posted code below, is it possible to create a DOCX in lieu of the PDF?

    I've tried to rearrange, modify, adjust, change this code to fit this DOCX creation, but I cannot.

    Can someone please help me to change this code to produce a DOCX not a PDF??


    Code:
    Sub CreatePDF()
    
    Dim wsA As Worksheet
    Dim wbA As Workbook
    Dim strTime As String
    Dim strName As String
    Dim strPath As String
    Dim strFile As String
    Dim strPathFile As String
    Dim myFile As Variant
    On Error GoTo errHandler
    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    strTime = Format(Now(), "yyyymmdd\_hhmm")
    'get active workbook folder, if saved
    strPath = wbA.Path
    If strPath = "" Then
      strPath = Application.DefaultFilePath
    End If
    strPath = strPath & "\"
    'replace spaces and periods in sheet name
    strName = Replace(wsA.Name, " ", " ")
    strName = Replace(strName, ".", "_")
    'create default name for savng file
    strFile = strName & "_" & strTime & ".pdf"
    strPathFile = strPath & strFile
    'use can enter name and
    ' select folder for file
    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strPathFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")
    Select Case True
           
    'export to PDF if a folder was selected
    Case myFile <> "False"
        wsA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        'confirmation message with file info
        
        MsgBox "PDF file has been created: " _
          & vbCrLf _
          & myFile
          Exit Sub
                
     Case myFile <> "True"
        Application.CutCopyMode = False 'Clear Clipboard
            MsgBox "Not CREATING PDF!!"
            Exit Sub
      End Select
        Exit Sub
    exitHandler:
        Exit Sub
        
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
                
    End Sub
    Many thanks in advance.

    Respectfully,
    Pinaceous
    Last edited by Pinaceous; Oct 25th, 2018 at 09:12 PM.

  2. #2
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,274
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sub Create DOCX

    You cannot create a Word document of any format via ExportAsFixedFormat or SaveAs from Excel; you'd have to automate Word, create a new Word document, then copy your worksheet content into the Word document before saving it in the desired format.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, NY, LI
    Posts
    534
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub Create DOCX

    Macropod,

    I can understand what you are saying, even the suggestion of its sequence.

    However, can you provide a code from your words to help me better understand??

    Many thanks in advance!

    Respectfully,
    Paul

  4. #4
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,274
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sub Create DOCX

    The basic code would be something like:
    Code:
    Sub Excel_to_Word()
    '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 = Split(.FullName, ".xls")(0) & ".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
      Set wdDoc = .Documents.Add
      xlRng.Copy
      With wdDoc
        .Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
        .SaveAs Filename:=FlNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        '.Close False
      End With
      '.Quit
    End With
    Application.CutCopyMode = False
    Set xlRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub
    The above code simply copies the active sheet, pastes it as a table into a new Word document, then saves that document with the same path & name as the workbook. Modify to suit your requirements, which might include adjusting the page size & margins and/or having to introduce a nested loop to paste different Excel ranges to different Word pages - if the content won't all fit on one page.
    Last edited by Macropod; Oct 30th, 2018 at 08:17 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, NY, LI
    Posts
    534
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub Create DOCX

    Thank you for posting your code and for following it with an explanation.

    I'm going to have to see how I can make this work for what I'm doing.

    Thanks again!
    Paul

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, NY, LI
    Posts
    534
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub Create DOCX

    Quote Originally Posted by Macropod View Post
    The basic code would be something like:
    Code:
    Dim wdApp As New Word.Application
    Hello Macropod,
    I'm getting an error on this line of the code.
    I'm using Excel 2016 on Windows 7.
    The error reads:
    Compile error:
    User-defined type not defined
    Do you know if there is a work around in this respect?
    Thank you,
    Pinaceous
    Last edited by Pinaceous; Oct 29th, 2018 at 09:41 PM.

  7. #7
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,274
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sub Create DOCX

    That's undoubtedly because you haven't done what the comment in the line above that one says you need to do...
    Cheers
    Paul Edstein
    [MS MVP - Word]

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, NY, LI
    Posts
    534
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub Create DOCX

    Quote Originally Posted by Macropod View Post
    That's undoubtedly because you haven't done what the comment in the line above that one says you need to do...
    I'm trying this web site link to aid me in referencing, but I'm still getting an error.


    https://support.office.com/en-us/art...D-B368F9AE2513


    Any suggestions or am I a lost cause at this point?

    Thanks again,
    Paul

  9. #9
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,274
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sub Create DOCX

    Quote Originally Posted by Pinaceous View Post
    I'm trying this web site link to aid me in referencing, but I'm still getting an error.
    So have you actually added the Word library reference? It'll be listed amongst the checked entries at the top if you have.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, NY, LI
    Posts
    534
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub Create DOCX

    Hello Macropod,
    I added the
    Microsoft Word 16.0 Object Library
    of which I had to check and now it is at the top.
    I'm assuming that it is the same as:
    the Word library reference
    The sub runs and produces a file.
    The file that is produced is called TEST.docx.
    When I go to open the TEST.docx I get an error:

    File In Use
    TEST.docx is locked for editing by 'Authorized User'.
    Do you want to:
    *Open a Read Only copy
    * Creat a local copy and merge your changes later
    * Receive notification when the original copy is available
    Is there a work around to this?
    Please let me know.
    And many thanks again,
    Pinaceous
    Last edited by Pinaceous; Oct 30th, 2018 at 08:03 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •