0

I have monthly task os printing some data for pure accounting. this data are in some excel workbooks on a hidden sheet, as it is now I have to open the workbook, reveal the sheet, print it and hide it again.

I would like to this task to automated.

All the workbooks are in the same directory

I have a Powershell script that can print every thing i one directory, but i don't know how to target a specific sheet let alone a hidden sheet

Code to print from one directory:

$files = Get-ChildItem “Y:\Booking\Send*.*” 

foreach ($file in $files){
   start-process -FilePath $file.fullName -Verb Print 
}

how would i do this ?

Mikkel
  • 27
  • 1
  • 3
  • Excel can be scripted as a COM object. Search for `powershell excel open workbook` and there will probably be many. https://stackoverflow.com/questions/37665118/how-to-open-excel-workbook-from-powershell-for-automation – lit Oct 18 '18 at 12:26

1 Answers1

0

The below will allow you to print the hidden sheet of a single excel workbook that you could extend to print multiple in a loop.

This will use the default printer set in windows.

$FilePath = Get-ChildItem "Y:\Booking\November\*.xls" 
$HiddenSheet = "Administration"
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $False

foreach ($file in $FilePath){
    $wb = $xl.Workbooks.Open($file) 
    $ws = $wb.WorkSheets.Item($HiddenSheet) 
    $ws.Visible = $True 
    $ws.PrintOut() 
    $wb.close($false) 
}

$xl.quit()

To be able to print the hidden sheet it needs to be set to visible, the script handles this and then closes the workbook and does not keep the changes.

You must declare the name of the $hiddensheet so if the name is different on each workbook I would suggest making a CSV with filepath and name of the sheet so that these can be passed through and handled accordingly.

CraftyB
  • 721
  • 5
  • 11
  • Hey CraftyB Thanks for your answer It worked like a charm on a dokument :) But i would like it to print a directory that contains xls files. And the hidden sheet will always be named the same. I have tryed this: `$FilePath = Get-ChildItem "Y:\Booking\November\*.xls" $HiddenSheet = "Administration" $xl = New-Object -ComObject Excel.Application $xl.Visible = $false $wb = $xl.Workbooks.Open($FilePath) $ws = $wb.WorkSheets.Item($HiddenSheet) $ws.Visible = $True foreach ($file in $FilePath){ $ws.PrintOut() $wb.close($false) $xl.quit() }` But it dosen't work. – Mikkel Oct 19 '18 at 09:08
  • Answer has been updated with the information you have supplied. – CraftyB Oct 19 '18 at 09:22