Sub Create DOCX

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
1,107
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
That's undoubtedly because you haven't done what the comment in the line above that one says you need to do...
 
Upvote 0
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top