How to convert files between xls and xlsm formats in the command line? Is there such a way?

Microsoft's OMPM is suitable for converting xls to xlsm, but as far as I know, it only works in one direction.

  • What tools do you want to use? If the tools are Microsoft Office Excel, then there are 2 commonly used methods: through a vb-script call, which starts Excel and the conversion macro starts. And the launch of the necessary Excel-document, in which the macro is registered in the autoload. As you understand, you also need to have a macro (however, there is a simple code for resaving the document to the desired format). - Daemon-5
  • However, there is a simple code in the macro to re-save the document in the desired format. :-) The main two functions: Workbooks.Open and ActiveWorkbook.SaveAs - Daemon-5
  • @ Daemon-5, apparently, will have to pay attention to vb, never encountered it. In idel it would be some kind of third-party software or any library for python. In particular, the conversion is needed to work with files through openpyxl, which does not know how with xls. - Akari Gale
  • You decide to use VBA, I'll throw off the script that I used a long time ago to convert all xls, xlsm to xlsx in a given directory. Started it manually from Excel. - Daemon-5
  • @ Daemon-5 would appreciate it. - Akari Gale

3 answers 3

I offer my own version that meets the requirements of the launch from the command line VB script runs under WSH.

Call:

cscript converter.vbs <Папка-источник файлов, требующих преобразование> <Папка-содержит преобразованные файлы>

xls -> xlsm or vice versa xlsm -> xls

Example call: cscript converter.vbs D:\Tst_Fldr\From\ D:\Tst_Fldr\To\

Result of work:

Work result a.xls - plain text file with xls extension

The essence of the action:

  1. files in the source folder are read;
  2. in the loop, each source folder file is opened as an MS Excel workbook (oExcl.Workbooks.Open (oFl.Path));

  3. The file format is checked (oExclWB.FileFormat) and depending on the value:

    52 (xlOpenXMLWorkbookMacroEnabled (* .xlsm))

    or

    56 - (Excel8 (* .xls)) an action similar to MS Excel ("Save as") is performed with the required parameters (oExclWB.SaveAs ...)

  4. anything that does not fit the file format is not processed;

  5. written minimal information about the actions.

     'конвертирует файлы вида xls в xlsm и обратно 'если на входе xls то результат xlsm 'и наоборот xlsm -> xls 'параметр 1: папка - содержить файлы которые требуется сконвертировать; 'параметр 2: папка - для сохранения результата; 'получаем и обрабатываем переданные параметры Set oArgs = WScript.Arguments Select Case oArgs.Count Case 2 sFldrFrm = oArgs(0) sFldrTo = oArgs(1) Case Else 'Завершаем работы Wscript.Echo("Количество переданных параметров не равно двум") WScript.Quit End Select Wscript.Echo ("Начало преобразования файлов:") Set oFSO = CreateObject("Scripting.FileSystemObject") Set oFldr = oFSO.GetFolder(sFldrFrm) Set oExcl = CreateObject("Excel.Application") ' 'скрыли MS Excel oExcl.Visible = False 'True 'отключили окна запросов для случая перезаписи файлов oExcl.displayalerts=false For Each oFl in oFldr.Files Set oExclWB = oExcl.Workbooks.Open(oFl.Path) 'определяем формат книги MS Excel на основе FileFormat '52 - xlOpenXMLWorkbookMacroEnabled (*.xlsm) '56 - Excel8 (*.xls) 'Wscript.Echo (oExclWB.FileFormat) Select Case oExclWB.FileFormat 'и в зависимости от этого проводим конвертацию 'WScript.Echo (sFldrTo & oFl.Name) Case 52 oExclWB.SaveAs left(sFldrTo & oFl.Name, len(sFldrTo & oFl.Name)-1), 56 Wscript.Echo ("Файл: " & oFl.Path & " преобразован в: " & left(sFldrTo & oFl.Name, len(sFldrTo & oFl.Name)-1)) 'Wscript.Echo (left(sFldrTo & oFl.Name, len(sFldrTo & oFl.Name)-1)) Case 56 oExclWB.SaveAs sFldrTo & oFl.Name & "m", 52 Wscript.Echo ("Файл: " & oFl.Path & " преобразован в: " & sFldrTo & oFl.Name & "m") Case Else Wscript.Echo ("Файл: " & oFl.Path & " не преобразован, не соответствует требованиям форматов ") End Select oExclWB.Close 'формат вызова *.SaveAs( FileName , FileFormat , Password , WriteResPassword , ReadOnlyRecommended , CreateBackup , AccessMode , ConflictResolution , AddToMru , TextCodepage , TextVisualLayout , Local ) Next oExcl.Quit Wscript.Echo ("Преобразование файлов завершено")

    Convert files from Excel

    Two procedures - file selection, creating a new file. The code is placed in any Excel file with macro support. The start of the transformation can be assigned to the opening of a book, to any object, or to run the " ChoiceOfFile " macro in "manual" mode.

    In the directory with the file handler, the Conversion folder is created, where the converted files are added.

    The "direction" of the transformation is defined in the code:

    • source file with a .xls or .xlsx extension - creating an .xlsm file ( .xlsb )

    • macro source file - creating an .xls file ( .xlsx )

    File selection

     Sub ChoiceOfFile() Dim FDial As FileDialog Dim sFName As String Dim sShortFName As String Set FDial = Application.FileDialog(msoFileDialogFilePicker) With FDial .Filters.Clear: .Filters.Add "Excel files", "*.xls*" .Filters.Add "Allfiles", "*.*" .AllowMultiSelect = False .InitialFileName = ThisWorkbook.Path .Title = "Choose the file": .ButtonName = "Open" If .Show = False Then MsgBox "File didn't chosen!", 48, "ERROR": Exit Sub Else sFName = .SelectedItems(1) End If End With Set FDial = Nothing Call FileConversion(sFName) ' к преобразованию' End Sub

    File conversion

     Sub FileConversion(sFName As String) Dim wBook As Workbook Dim sFldr As String, sNewName As String, sFormat As String sFldr = ThisWorkbook.Path & "\" & "Conversion" & "\" ' путь к папке для сохранения' If Dir(sFldr, vbDirectory) = "" Then MkDir sFldr ' если нет, создаем папку' sNewName = Mid$(sFName, InStrRev(sFName, "\") + 1) ' имя без пути' sNewName = Left$(sNewName, InStrRev(sNewName, ".") - 1) ' имя без расширения' ' имя новой книги с расширением и формат сохранения' If Right(sFName, 1) = "s" Or Right(sFName, 1) = "x" Then ' исходный без макросов' sNewName = sNewName & ".xlsm": sFormat = xlOpenXMLWorkbookMacroEnabled 'sNewName = sNewName & ".xlsb": sFormat = xlExcel12' Else ' исходный с макросами' sNewName = sNewName & ".xls": sFormat = xlExcel8 'sNewName = sNewName & ".xlsx": sFormat = xlOpenXMLWorkbook' End If With Application: .ScreenUpdating = False: .DisplayAlerts = False: End With Set wBook = Workbooks.Open(Filename:=sFName) ' открываем книгу' With wBook ' новую книгу сохраняем и закрываем' .SaveAs Filename:=sFldr & sNewName, FileFormat:=sFormat, CreateBackup:=False .Close End With Set wBook = Nothing With Application: .ScreenUpdating = True: .DisplayAlerts = True: End With MsgBox "OK", 64, "" End Sub

    It remains to write a script that opens the book)

    You can send the path to the file without a selection window (of course, if this path is known):

     Sub jjj() Call FileConversion(sFName) End Sub

    Or, alternatively, assign this path to a variable in the procedure and run FileConversion directly (in this case there is no parameter to be passed and nothing needs to be written in brackets after the name):

     Dim FName As String FName = ThisWorkbook.Path & "\папка1\папка25\книга12.xlsx"
    • Thank. And how to automate the process now so that you can pass the file location as a parameter and not manually select it? - Akari Gale
    • Dobovail in response - vikttur
    • I apologize for the intrusiveness, but I still do not really understand how this could be done from the command line or python. You can send to the guides, if any. - Akari Gale
    • Alas, that I am powerless). Start processing can be recorded when opening the book: Private Sub WorkBook_Open (): Call FileConversion (sFName): End Sub - vikttur
     Attribute VB_Name = "Module1" Function procSaveAS(fn As String) As Boolean ' ' procSaveAS Макрос ' ' On Error GoTo ErrSt ' If fn = "" Then GoTo ErrSt ' End If ' Application.DisplayAlerts = False Workbooks.Open Filename:=fn, _ AddToMru:=False, Notify:=False, IgnoreReadOnlyRecommended:=True, UpdateLinks:=0, ReadOnly:=True Application.DisplayAlerts = True st = LCase(Right(fn, 4)) fnn = fn If st = "xlsm" Then fnn = Left(fnn, Len(fnn) - 1) End If Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=fnn & "x", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Application.DisplayAlerts = True ActiveWindow.Close procSaveAS = True Exit Function ErrSt: procSaveAS = False End Function Sub ConvertFormat() ' Const myProgramPath As String = """c:\windows\system32\CMD.exe""" Const lstfile As String = "C:\temp\123.txt" 'временный файл со списком всех файлов к обработке Const FP As String = "C:\WORK\" 'каталог в котором нужно конвертировать файлы exec = myProgramPath & " /c dir /b /s " & FP & "*.xls|findstr /i/v .xls.>" & lstfile & "&dir /b /s " & FP & "*.xlsm>>" & lstfile Shell exec Shell """c:\windows\system32\CMD.exe""" & " /c ping.exe localhost -n 4" Dim objStream, str Set objStream = CreateObject("ADODB.Stream") objStream.Charset = "cp866" objStream.Open objStream.LoadFromFile lstfile arr = Split(objStream.ReadText, vbNewLine) objStream.Close Set objStream = Nothing Dim i As String For Each st In arr If st <> "" Then i = st If procSaveAS(i) Then Kill i End If End If Next End Sub

    It is necessary to call the procedure ConvertFormat. The constants describe the paths of the directory to process (and the temporary file). Search for all * .xls and * .xlsm extension files. The file list is recorded in a temporary text file. Each file and list is processed by the procSaveAS procedure. After which it is deleted.

    • Since then it was still too lazy to implement a recursive file search using VBA, a recursive search was implemented by the dir command. If after processing the files do not need to be deleted, comment the command Kill i - Daemon-5
    • It happened that the Shell exec command was executed for a long time and did not have time to create a temporary file. I had to run the script again, and it was already too lazy to redo it ... - Daemon-5