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:
- Do openpyxl operations and save the results in a new workbook.
- Open new .xlsx file using Excel, hit ctrl s and close the file
- 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 :-)