# Sub Create DOCX



## Pinaceous (Oct 25, 2018)

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??



```
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


----------



## Macropod (Oct 26, 2018)

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.


----------



## Pinaceous (Oct 26, 2018)

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


----------



## Macropod (Oct 27, 2018)

The basic code would be something like:

```
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.


----------



## Pinaceous (Oct 27, 2018)

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


----------



## Pinaceous (Oct 29, 2018)

Macropod said:


> The basic code would be something like:
> 
> ```
> 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


----------



## Macropod (Oct 29, 2018)

That's undoubtedly because you haven't done what the comment in the line above that one says you need to do...


----------



## Pinaceous (Oct 29, 2018)

Macropod said:


> 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/ar...-project-ED28A713-5401-41B0-90ED-B368F9AE2513


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

Thanks again,
Paul


----------



## Macropod (Oct 29, 2018)

Pinaceous said:


> 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.


----------



## Pinaceous (Oct 30, 2018)

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
> ...



Is there a work around to this?
Please let me know.
And many thanks again,
Pinaceous


----------



## Pinaceous (Oct 25, 2018)

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??



```
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


----------



## Macropod (Oct 30, 2018)

I've modified the code in post 4 slightly. It now leaves the Word session visible with your newly-created document open. You'll note two commented-out lines, too. Uncomment those and the documents will be closed and the Word session exited.


----------



## Pinaceous (Oct 30, 2018)

Macropod said:


> I've modified the code in post 4 slightly. It now leaves the Word session visible with your newly-created document open. You'll note two commented-out lines, too. Uncomment those and the documents will be closed and the Word session exited.




Hello Micropod,

That is really fantastic!

Thank you for modifying the code in post 4.  That is really fantastic!

I'm trying to figure out where in you code I can change the name of the docx?

For example, in lieu of the name "TEST" as the created file name; can you adjust the code to produce:
"ActiveSheet Name" And "yyyymmdd\_hhmm".docx' 
As its File Name??

Thanks again!!
Pinaceous


----------



## Macropod (Oct 30, 2018)

Change:
FlNm = Split(.FullName, ".xls")(0) & ".docx"
to:
FlNm = .Path & "" & ActiveSheet.Name & " " & Format(Now,"YYYYMMDD_hhmm") & ".docx"


----------



## Pinaceous (Oct 31, 2018)

Hi Macropod,

Thank you for clearing that up!

Let me give it a go!

Thanks,
Pinaceous


----------



## Pinaceous (Nov 1, 2018)

Hello Macropod,

I am wondering, if in following my original post#1 code proposal, if you can have the created word file "not open" and to "save it in a location" according to:


```
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="Microsoft Word Document Files (*.docx), *.docx", _
        Title:="Select Folder and FileName to save")
```

Is this possible to seed this into your code (?)

I'm trying different ways, but I cannot see myself through this situation.

Please help me, if you can.

Many thanks,
Pinaceous


----------



## Macropod (Nov 1, 2018)

As indicated in post 11, if you uncomment the two commented-out lines, Word will close the file and exit. If you also change:
.Visible = True
to:
.Visible = False
you'll never even see the Word session running. As for allowing an optional save name, you could replace:
.SaveAs Filename:=FlNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
with:

```
With wdApp.Dialogs(wdDialogFileSaveAs)
      .Name = FlNm
      .AddToMRU = False
      .Show
    End With
```


----------



## Pinaceous (Nov 2, 2018)

Hello Macropod,
Thank you for explaining to me how & what to do.  Appreciate it!
The code works really works good now.
However, when I go to open the created word document (DOCX), I get the following error message, which is similar to what I've experienced to the (XLSM) in post#10.



> File In Use
> Destop Test1 YYYMMDD_
> is locked for editing by 'Authorized user'.
> Do you want to:
> ...





But; how do I fix this??

I'm thinking that I need to do something similar like the reference fix I've performed inside the XLSM or with the DOCX(?) 

Please help me if you can.
Thank you!
Pinaceous


----------



## Macropod (Nov 2, 2018)

Show me the _exact_ code you're using.


----------



## Pinaceous (Nov 2, 2018)

Hi Macropod,

I don't really know, but I've restarted the computer and ran the code again and it appears that it is working without any hiccups on opening the newly created word docx.

Here is the code, just in case ...



```
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 = 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 = False
  Set wdDoc = .Documents.Add
  xlRng.Copy
  With wdDoc
    .Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
    '.SaveAs Filename:=FlNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      With wdApp.Dialogs(wdDialogFileSaveAs)
      .Name = FlNm
      .AddToMRU = False
      .Show
    End With
    '.Close False
  End With
  '.Quit
End With
Application.CutCopyMode = False
Set xlRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
```


.. you see something here that needs a tweak (?), please do so.  I am guessing it was my computer.

Many thanks for all of your help and assistance.

Respectfully,
Pinaceous


----------



## Macropod (Nov 2, 2018)

You haven't done as suggested in post 11 -
.Close False
and
.Quit
are both still commented-out!


----------



## Pinaceous (Oct 25, 2018)

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??



```
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


----------



## Pinaceous (Nov 2, 2018)

Macropod said:


> You haven't done as suggested in post 11 -
> .Close False
> and
> .Quit
> are both still commented-out!




Oh boy, that was dumb of me.

Thanks for telling me again!

Pinaceous


----------



## Pinaceous (Nov 6, 2018)

Macropod said:


> The basic code would be something like:
> 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.



Macropod,
I hope that you are not too surprised that I am now asking you about this part of your post as I evolve with this learning topic for me.  I am discovering that what I read here I am now experiencing anew. 

If I were to modify my requirements to include adjusting the page size & margins, where would this go?

I’ve create a sub from through the Page Layout through the Print Titles to affect the scaling of the Excel’s active sheet that I’m going to posting here:


```
Sub Macro1()
'
' Macro1 Macro
'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$J$41"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintSheetEnd
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = False
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    
End Sub
```

I’ve tried to place 
	
	
	
	
	
	



```
Call Macro1
```
 in various places of the Sub Excel_to_Word() to see how it affects the created DOCX document scaling parameters but what I attempt here, is not changing it at all.
If there is a code through your words:



Macropod said:


> 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.



What would it look like and where would it go??

Please let me know, only when you can.

Thank you for coaching and mentoring stance in helping me on this topic.

Respectfully,
Pinaceous


----------



## Macropod (Nov 6, 2018)

Your additional Excel code is irrelevant - it's the Word document you need to modify, using Word's own code. Thus, after:
With wdDoc
you might insert something like:

```
With .PageSetup
      .PaperSize = wdPaperLetter
      .Orientation = wdOrientLandscape
      .LeftMargin = wdApp.InchesToPoints(0.25)
      .RightMargin = wdApp.InchesToPoints(0.25)
      .TopMargin = wdApp.InchesToPoints(0.25)
      .BottomMargin = wdApp.InchesToPoints(0.25)
    End With
```
Note that I've set all margins to 1/4in; Word is likely to complain otherwise unless you're using a printer that does full-page bleeds.


----------



## Pinaceous (Dec 9, 2018)

Macropod said:


> Your additional Excel code is irrelevant - it's the Word document you need to modify, using Word's own code. Thus, after:
> With wdDoc
> you might insert something like:
> 
> ...




Hey Macropd,

Code working really great!

I've only changed the orientation to 
	
	
	
	
	
	



```
.Orientation = wdOrientPortrait
```
 with success.

I've tried to change the 
	
	
	
	
	
	



```
Right Margin
```
 to various numbers but I cannot move it over to justify the print.  The data range is off the screen, every time. 

If my excel range is 
	
	
	
	
	
	



```
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$41"
```
 is up to Column K, how do I add this to the code?

I am hoping that adding this to the existing code it will help me with my printing range.

Many thanks,
Paul


----------



## Macropod (Dec 9, 2018)

Pinaceous said:


> I've tried to change the
> 
> 
> 
> ...


So what is the range that's actually being copied & pasted into Word and how wide is that range?


Pinaceous said:


> If my excel range is
> 
> 
> 
> ...


You don't. As I'd have thought was clear by now, anything of this nature you specify in Excel is irrelevant where Word is concerned.


----------



## Pinaceous (Dec 10, 2018)

Hi Macropod,

I got it!  I shrunk the columns disproportionately until it came out normal for word.

Many thanks for all of your help!

Paul


----------



## Pinaceous (Feb 7, 2019)

Hey Macropod!

Everything is working out great!

I just have a question with the dialogue box.

If the user choses to "cancel" the saveas doc. how can I then conclude in the code 'Exit Sub' for that case?

Many thanks,
Pal


----------



## Macropod (Feb 8, 2019)

You could change:
.Show to:
If .Show = False Then Exit Sub


----------



## Pinaceous (Feb 8, 2019)

Macropod!

I am enthralled over your knowledge.

I stand humble and I thank you.

Cheers Paul!


----------



## Pinaceous (Feb 12, 2019)

Hello Macropod,



In using the code:


```
'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
  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
      With wdApp.Dialogs(wdDialogFileSaveAs)
      .Name = FlNm
      .AddToMRU = False
      .Show
    End With
    .Close False
  End With
  .Quit
End With
Application.CutCopyMode = False
Set xlRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing
   Exit Sub
```



I've encountered a slight problem with the way that the Microsoft save dialog icon alerts the user, whereby I get only an irritating flash of the main window's title bar and the application's button the taskbar.  



I've trying to 'Force the   
	
	
	
	
	
	



```
wdDialogFileSaveAs
```
  to the front of the excel window, to help properly notify the user, where I've tried a slew of codes and functions with no promise.


Do you have a Function &/or an additional line to add to the code to make the 
	
	
	
	
	
	



```
wdDialogFileSaveAs
```
  pop out in front of the Excel window as opposed to the irritating flash alerting to the user upon the taskbar??



Thank for your continued help and support!!
Paul


----------



## Pinaceous (Oct 25, 2018)

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??



```
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


----------



## Macropod (Feb 13, 2019)

Try inserting:
.Activate
after:
.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False


----------



## Pinaceous (Feb 14, 2019)

Macropod said:


> Try inserting:
> .Activate
> after:
> .Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False




Hey Macropod,

Would you have another solution to try ?

I've tried adding this and it did not work as expected; all of the time.

I'm not sure why?  

Maybe, I have something operating independently that I am unaware of, which is blocking this action?

Not sure how to proceed but to work with the current code until it behaves the way that I want.

Anymore suggestions?? 

Thanks,
pinaceous


----------



## Macropod (Feb 14, 2019)

It works consistently for me. Perhaps you have something else running in the background that's causing Excel to take focus. You might also try inserting:
.Activate
after:
.Visible = True
to activate Word earlier in the process, but I can't guarantee that'll stop Excel stealing focus again.


----------



## Pinaceous (Feb 22, 2019)

Hello Macropod,

I am having difficulties integrating the code to allow Word to popup in front of the Excel window as opposed to behind it based on my workbook.

Is there anyway I can provide you my workbook, so you can take a look at it??

Please let me know.
Respectfully,
pinaceous


----------



## Macropod (Feb 22, 2019)

If you've made all the changes I suggested, yet Word keeps reverting to the background, you evidently have some other code that keeps causing Excel to steal focus. I've done as much as is possible on the Word side; you might try moving:
xlRng.Copy
to before:
With wdApp
Other than that, if you're using anything other than the code discussed in this thread, you should look at what your other Excel code is doing.


----------

