Home > Back-end >  How to open excel file and save it using cmd?
How to open excel file and save it using cmd?

Time:10-10

I need to open a .xlsx file, instantly save it and close it. Is it possible to do using powershell/cmd?


My problem:
Currently I am using openpyxl to automate some repetitive excel tasks but when I save my workbook after doing manipulations with openpyxl I lose my TextBoxes. I know openpyxl doesn't support TextBoxes but currently I haven't found any better library for working with .xlsx files so I need to somehow workaround this issue.


My findings:
After unzipping .xlsx file and looking through .xml drawings I noticed that openpyxl changes almost every xml tag but after opening .xlsx file manually, hitting ctrl s to save and closing the file, those .xml drawings go back to the usual structure.


My idea to workaround disappearing TextBoxes:

  1. Do openpyxl operations and save the results in a new workbook.
  2. Open new .xlsx file using Excel, hit ctrl s and close the file
  3. Copy xml code for textboxes from original workbook and paste xml code into the new workbook

How do I automate 2. step so I don't have to manually open .xlsx file?

CodePudding user response:

Try using: https://github.com/dfinke/ImportExcel It's a PowerShell module, it can import/export excel spreadsheets.

CodePudding user response:

To directly answer your question, from a previous similar request

Wscript XLfilesave.vbs

XLfilesave.vbs (you can boost it to take optional arguments)

Set WshShell = WScript.CreateObject("WScript.Shell")
' You may need to include the path to excel.exe if it is a portable version like mine
WshShell.Run "EXCEL.EXE " "\xxx\yyy\File.xlsx", 9
' 2000 milli-seconds = 2 seconds use larger if needed
WScript.Sleep 2000
' These are the English key combinations for ALT File Save . SO alter or remove if not needed
WshShell.SendKeys "%FS"
' These are the English key combinations for ALT File eXit . SO alter if needed for french excel
WshShell.SendKeys "%FX"
' Lets us wait 2 seconds for clean closure
WScript.Sleep 2000

However for step 1 & 3 it would be worth considering a VBA macro to run in excel and copy paste as required, (However that is for you to attempt and raise any fresh question that arises, as I dont have such a macro to draw on :-)

  • Related