none
Can't save a new Excel file created with copy() with Powershell RRS feed

  • Question

  • Dear Scripting Guys,

    my problem is not as simple as described in the title, after I copied an Excel worksheet to a new created Worbook, I can't save the new one. 
    $xlfile = new-object -com Excel.Application
    $xlfile.Visible=$true
    $xlfile.DisplayAlerts = $false
    $wb = $xlfile.Workbooks.Open('C:\MyExcelFile1.xlsx')
    $ws = $wb.sheets.item("Data")
    $ws.Activate()

    Then I want to copy the Data sheet into a new workbook and save this one with a new name:

    $ws2 = $ws.copy() # > creates and opens a new Workbook with the default name Book1.xls including the copied Data sheet
    $m= (get-date).month
    $d= (get-date).day
    $y= [string](get-date).year

    $ws2.ActiveWorkbook.SaveAs("V:\MyExcelFile2_"+ $y+ "-"+ $m+ "-"+ $d+ ".xlsx") 

    and then I get the error: "You cannot call a method on a null-valued expression."

    How could I simply save the new Book1 with a new name ?

    • Moved by Bill_Stewart Thursday, January 18, 2018 3:10 PM Move to more appropriate forum
    Thursday, January 18, 2018 1:28 PM

Answers

  • Hello ThierryMulhouse,

    WorkSheet.Copy could open a new workbook with the copied worksheet, however, the method does not return a worksheet object, it won't return any object. So the $ws2 is invalidated and it won't have the ActiveWorkbook property.

    Since the new workbook is newly opened, I would suggest you use Application.ActiveWorkbook to save the new workbook.

    Here is the example.

    $DebugPreference="Continue"
    $xlfile = new-object -com Excel.Application
    $xlfile.Visible=$true
    $xlfile.DisplayAlerts = $false
    $wb = $xlfile.Workbooks.Open('C:\Users\v-guaxu\Desktop\A.xlsx')
    $ws = $wb.sheets.item("Data")
    $ws.Activate()
    
    $ws.copy()
    $m= (get-date).month
    $d= (get-date).day
    $y= [string](get-date).year
    $xlfile.ActiveWorkbook.SaveAs("C:\Users\v-guaxu\Desktop\A_"+ $y+ "-"+ $m+ "-"+ $d+ ".xlsx")
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 19, 2018 3:24 AM

All replies

  • Here  is how to easily create a new book from a sheet:

    $xl = new-object -com Excel.Application
    $xl.Visible = $true
    $xl.DisplayAlerts = $false
    $wb = $xl.Workbooks.Open('d:\scripts\outfile.xlsx')
    $wb.WorkSheets['Test'].Copy()
    $xl.Workbooks['book1'].SaveAs('d:\test\mynewbook.xlsx')
    $xl.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
    


    \_(ツ)_/

    Thursday, January 18, 2018 4:06 PM
  • You can also just do this:

    function Copy-XLSheetToNewBook {
    	Param (
    		[parameter(Mandatory)]$WorkBook,
    		[parameter(Mandatory)]$SheetName,
    		[parameter(Mandatory)]$NewBook
    	)
    	Try{
    		$xl = new-object -com Excel.Application
    		$xl.Visible = $true
    		$xl.DisplayAlerts = $false
    		$wb = $xl.Workbooks.Open($WorkBook)
    		$wb.WorkSheets[$SheetName].SaveAs($NewBook)
    		$xl.Quit()
    		[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
    		[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
    	}
    	Catch {
    		Throw $_
    	}
    }


    \_(ツ)_/

    Thursday, January 18, 2018 4:18 PM
  • Hello ThierryMulhouse,

    WorkSheet.Copy could open a new workbook with the copied worksheet, however, the method does not return a worksheet object, it won't return any object. So the $ws2 is invalidated and it won't have the ActiveWorkbook property.

    Since the new workbook is newly opened, I would suggest you use Application.ActiveWorkbook to save the new workbook.

    Here is the example.

    $DebugPreference="Continue"
    $xlfile = new-object -com Excel.Application
    $xlfile.Visible=$true
    $xlfile.DisplayAlerts = $false
    $wb = $xlfile.Workbooks.Open('C:\Users\v-guaxu\Desktop\A.xlsx')
    $ws = $wb.sheets.item("Data")
    $ws.Activate()
    
    $ws.copy()
    $m= (get-date).month
    $d= (get-date).day
    $y= [string](get-date).year
    $xlfile.ActiveWorkbook.SaveAs("C:\Users\v-guaxu\Desktop\A_"+ $y+ "-"+ $m+ "-"+ $d+ ".xlsx")
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 19, 2018 3:24 AM
  • Hello ThierryMulhouse,

    WorkSheet.Copy could open a new workbook with the copied worksheet, however, the method does not return a worksheet object, it won't return any object. So the $ws2 is invalidated and it won't have the ActiveWorkbook property.

    Since the new workbook is newly opened, I would suggest you use Application.ActiveWorkbook to save the new workbook.

    Here is the example.

    $DebugPreference="Continue"
    $xlfile = new-object -com Excel.Application
    $xlfile.Visible=$true
    $xlfile.DisplayAlerts = $false
    $wb = $xlfile.Workbooks.Open('C:\Users\v-guaxu\Desktop\A.xlsx')
    $ws = $wb.sheets.item("Data")
    $ws.Activate()
    
    $ws.copy()
    $m= (get-date).month
    $d= (get-date).day
    $y= [string](get-date).year
    $xlfile.ActiveWorkbook.SaveAs("C:\Users\v-guaxu\Desktop\A_"+ $y+ "-"+ $m+ "-"+ $d+ ".xlsx")

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Too much unnecessary code.  Just use "SaveAs" on the worksheet.

    \_(ツ)_/

    Friday, January 19, 2018 3:26 AM
  • Hello Terry,

    This resolved my problem, thank you so much!

    Friday, January 19, 2018 7:42 AM
  • Thank you all for your support! :)
    Friday, January 19, 2018 7:43 AM