none
PowerShell, Excel, Word Issue RRS feed

  • Question

  • I am working on a PowerShell script to create an automated phone directory. I am so close to being done, but, I have a few more issues I need to work out. The first issue, is the output has spaces between each entry. I tried various no spacing settings, but have had no luck. The other is regarding the spacing between the columns. They tend to be on top of one another, with a lot of space between the name and the number. Any ideas? Here is the script that takes the XLSX document, imports it to word, and prints to pdf. Any help would be greatly appreciated. 

    # File paths
    $FN = "C:\PhoneReport\Output\FirstName.xlsx"
    $LN = "C:\PhoneReport\Output\LastName.xlsx"
    $pdfPath = "C:\PhoneReport\Output"
    $FileName1 = "Directory - FirstName"
    $FileName2 = "Directory - LastName"
    $objExcel=New-Object -ComObject Excel.Application
    $objExcel.Visible=$false
    $objExcel.DisplayAlerts=$false
    $WorkBook=$objExcel.Workbooks.Open("$FN")
    
    $Workbook.WorkSheets.item(1).activate();
    $WBRange=$Workbook.WorkSheets.item(1).range("A1","B220");
    $WBRange.copy() | out-null
    
    #$worksheet = $objExcel.sheets.item("PhoneDir")
    #$range = $worksheet.range("A1","B220")
    #$range.Copy() | out-null 
    
    $word = new-object -comobject Word.application
    $word.visible = $false
    $DocPath1 = "$pdfPath\FirstName.docx"
    $DocPath2 = "$pdfPath\LasttName.docx"
    $doc = $word.documents.add()
    $Selection = $Word.Selection
    $selection.Font.Size=8
    $selection.Font.Name="Calibri"
    $Selection.Font.Bold = 0
    $margin = 15
    $doc.PageSetup.LeftMargin = $margin
    $doc.PageSetup.RightMargin = $margin
    $doc.PageSetup.TopMargin = $margin
    $doc.PageSetup.BottomMargin = $margin
    $doc.PageSetup.Orientation = 0
    $selection.PageSetup.TextColumns.SetCount(4)
    $selection.PageSetup.TextColumns.NoSpacing
    $selection.PageSetup.TextColumns.EvenlySpaced
    $doc.Range().Paste()
    
    
    # Export the PDF file and close without saving a Word document
    $doc.ExportAsFixedFormat($pdfPath,$wdExportFormatPDF)
    $doc.close([ref]$wdDoNotSaveChanges)
    [System.Windows.Forms.Clipboard]::Clear()
    $word.Quit()
    $objExcel.Quit()
    
    $null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$word)
    [gc]::Collect()
    [gc]::WaitForPendingFinalizers()
    Remove-Variable word
    Remove-Variable objExcel

     

    Sunday, May 12, 2019 3:55 PM

All replies

  • You will have to design a format for your spreadsheet and then import that into Word as a formatted table.

    I do not understand why you have firstname and lastname in different spreadsheets.  Just put everything into one spreadsheet.

    You can also print an Excel formatted report directly to a PDF.  Word is not required.

    For assistance in using Office products with automation post in the Office developers forum.

    You method is not how anyone who knows Office automation would do this. 

    It is also very useful to create a template in Office that has all of the required formatting then just create a new document from the template.  Now all you need to do is load the data and print the PDF to a file.


    \_(ツ)_/

    Sunday, May 12, 2019 4:41 PM
  • Frist and Lastname are separate sheets, one listed by first name one by last names. I am just working on the Firstname sheet currently. Once I get it, I will do the other one. 

    As you can see I am not a programmer, I'm a network guy trying to make something work. I did get most of it figured out, expect for the spacing in the paste. It's pasting double spaced and I need single spaced.

    # File paths
    $FN = "C:\PhoneReport\Output\FirstName.xlsx"
    $LN = "C:\PhoneReport\Output\LastName.xlsx"
    $pdfPath = "C:\PhoneReport\Output"
    $FileName1 = "Directory - FirstName"
    $FileName2 = "Directory - LastName"
    
    # Objects-XLS
    
    $objExcel=New-Object -ComObject Excel.Application
    $objExcel.Visible=$false
    $objExcel.DisplayAlerts=$false
    $WorkBook=$objExcel.Workbooks.Open("$FN")
    $Workbook.WorkSheets.item(1).activate();
    $WBRange=$Workbook.WorkSheets.item(1).range("A1","B220");
    $WBRange.copy() | out-null
    
    # Objects-WRD
    
    $word = new-object -comobject Word.application
    $word.visible = $false
    $doc = $word.documents.add()
    $Selection = $Word.Selection
    
    $margin = 15
    $doc.PageSetup.Orientation = 0
    $doc.PageSetup.LeftMargin = $margin
    $doc.PageSetup.RightMargin = $margin
    $doc.PageSetup.TopMargin = $margin
    $doc.PageSetup.BottomMargin = $margin
    
    $selection.Font.Size=8
    $selection.Font.Name="Calibri"
    $Selection.Font.Bold = 0
    $selection.PageSetup.TextColumns.SetCount(4)
    $selection.PageSetup.TextColumns.Width = 0.5
    $selection.PageSetup.TextColumns.Spacing = 10
    #$selection.PageSetup.TextColumns.LineSpacing = 1
    
    $doc.Range().Paste()
    
    # Export the PDF file and close without saving a Word document
    
    $doc.ExportAsFixedFormat($pdfPath,$wdExportFormatPDF)
    [System.Windows.Forms.Clipboard]::Clear()
    $doc.close([ref]$wdDoNotSaveChanges)
    $word.Quit()
    $objExcel.Quit()
    
    $null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$word)
    $null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$doc)
    $null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$objExcel)
    [gc]::Collect()
    [gc]::WaitForPendingFinalizers()
    Remove-Variable word
    Remove-Variable objExcel


    Sunday, May 12, 2019 6:05 PM
  • You have to set the paragraph format settings or use a template that has the correct settings.


    \_(ツ)_/

    Sunday, May 12, 2019 6:07 PM
  • Here is the enum for the line spacing of a paragraph.

    https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.word.wdlinespacing?view=word-pia

    The paragraph and its properties.

    https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.word.paragraph.linespacingrule?view=word-pia

    I would recommend using a table and setting the table properties.  This will allow global alignment and a table can be loaded easily from a spreadsheet or a CSV.


    \_(ツ)_/

    Sunday, May 12, 2019 6:29 PM
  • Thanks for all the help. I get what you're saying, but I dont seem to be able to execute it. I modified this section, but I am missing something. I tried putting -as 0 -as wdLineSpaceSingle

    I think if I did this in the future I would do the template way, but since I just have one thing left, the spacing, can you point out what's wrong?

    $selection.TypeParagraph()
    $selection.Style = "No Spacing"
    $selection.Font.Size=8
    $selection.Font.Name="Calibri"
    $Selection.Font.Bold = 0
    $selection.PageSetup.TextColumns.SetCount(4)
    $selection.PageSetup.TextColumns.Width = 0.5
    $selection.PageSetup.TextColumns.Spacing = 10
    $lineSpacing = “microsoft.office.interop.word.WdLineSpacing” -as [Single]
    
    $doc.Range().Paste()

    Sunday, May 12, 2019 7:43 PM
  • Like I said - you have to set the paragraph spacing which you are not doing.

    An easy way is to select the whole document and set the paragraphs in the document.

    $Selection.ParagraphFormat.LineSpacingRule = [microsoft.office.interop.word.WdLineSpacing]::wdLineSpaceSingle

    Without any knowledge of how to use PowerShell Office automation will be very confusing.


    \_(ツ)_/

    Sunday, May 12, 2019 7:50 PM
  • It HAS been confusing, i'll say that much, thank you for the help. I added that line, still getting an output that is double spaced. I understand if you're done helping. I appreciate what you have done thus far. 

    # Objects-WRD
    
    $word = new-object -comobject Word.application
    $word.visible = $false
    $doc = $word.documents.add()
    $Selection = $Word.Selection
    $Selection.ParagraphFormat.LineSpacingRule = [microsoft.office.interop.word.WdLineSpacing]::wdLineSpaceSingle
    
    $margin = 15
    $doc.PageSetup.Orientation = 0
    $doc.PageSetup.LeftMargin = $margin
    $doc.PageSetup.RightMargin = $margin
    $doc.PageSetup.TopMargin = $margin
    $doc.PageSetup.BottomMargin = $margin
    
    $selection.Font.Size=8
    $selection.Font.Name="Calibri"
    $Selection.Font.Bold = 0
    $selection.PageSetup.TextColumns.SetCount(4)
    $selection.PageSetup.TextColumns.Width = 0.5
    $selection.PageSetup.TextColumns.Spacing = 10
    
    $doc.Range().Paste()

    Sunday, May 12, 2019 8:00 PM
  • For that to work you need to place all lines within the some paragraph or set the spacing  between paragraphs.

    Simplify you issue.  Use a table.  Search for PS examples of how to create a Word table.

    You can also find a co-worker who is trained in Word to help you.  A good Word person can help you understand how Word works an dhow word processing systems are designed to create objects and apply default formats.  This is why using a template is much easier.  Set the template to display text the way you want and then just add text to a new document based on the template.


    \_(ツ)_/

    Sunday, May 12, 2019 8:36 PM