EXCELCNV.EXE Lag Time

[jamiers]

Hi All,

For about 24 months, I have been using the EXCELCNV.EXE command line program to convert XLS files to XLSX files so that I can use them in Power BI as datasets. I use a series of batch scripts to convert Excel files that are downloaded from my web application (that I cannot make the Excel file come down as xlsx).

33% of the time over those 24 months, excelcnv.exe worked instantaneously on file conversions.
66% of the time the conversion takes FOREVER (really, between 3-5 minutes).
The files themselves haven't grown in size. They are about the same size as they were 24 months ago.

It is maddening. In the beginning, it worked quickly, then a MS update pushed down and it the conversion lagged. Then a new update would come down and it went back to instantly again. I am now in a long period of the LONG conversions. Repairing MS Office hasn't done anything and a reinstall hasn't either.

I have done searching over the Internet and haven't found anyone experiencing this problem. Anyone have any ideas? (I put this in Excel because it is a file that comes with Excel, but if you think I should put it in the Power Tools forum, I can do that too).

Thanks!

 

[Skybluekid]

If you are using version 2007 or later, this is the default. Would it not be eaiser to open and save each file. Or would that not be possible?

 

[jamiers]

I am using Office 365 Desktop version... 10 files per day is a lot to open each one and then resave them. The automation of the scripts makes sure that I am naming them the right thing and then moving them to the right folder so Power BI can pick them up. The tools are used by many.

 

[Skybluekid]

Have you tried using Power Automate, which is in the 365 Suite?

 

[lrobbo314]

Here is some VBA that will prompt the user to choose a folder. In my testing I had 2 subfolders in the main folder, 'Original' and 'After'. You would need to adjust those folder names.

The code looks at all the files and if it has a file type of '.xls', it will copy the file to the 'After' folder as a '.xlsx'.

Sub renameFiles()
Dim Root As String:     Root = folderName()
If Root <> "" Then
    Dim FSO As Object:              Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim fileLocation As String:     fileLocation = "\Original\"
    Dim newLocation As String:      newLocation = "\After\"
    Dim SP() As String
    Dim Fol As Object, fil As Object
    
    Set Fol = FSO.getfolder(Root & fileLocation)
    
    For Each fil In Fol.Files
        SP = Split(fil.Name, ".")
        If SP(UBound(SP)) = "xls" Then
            FileCopy Root & fileLocation & fil.Name, Root & newLocation & Left(fil.Name, InStrRev(fil.Name, ".") - 1) & ".xlsx"
        End If
    Next fil
End If

End Sub

Function folderName()
Dim result As String

With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then folderName = .SelectedItems(1)
End With
End Function