Macro or batch to convert .xlsb to .xlsx

[bohravanraj]

Hi guys,

i have a data source as Excel binary file(.xlsb). in order to fetch data from this file we need to install ODBC driver which is not possible at my client location.

work around for this i thought can we write an macro or batch file to convert .xlsb to .xlsx , then we can take this data in Qlikview easily.

but i dont have any experience to write macro or batch command.

Please can you all suggest how can we solve this.

Regards,

Vanraj Dinesh Bohra

[passionate]

Hi Vanraj,

Converting from .xlsb to .xlsx is similar to converting .csv to .xlsx

if you rename the file extension this will do.

Search for macro to convert xlsx to csv and fit 5o your requirement

Something like below would do:

ActiveWorkbook.SaveAs FileName:= ActiveWorkbook.Path & "\" & _

Replace(ActiveWorkbook.Name, "xslx", "csv"), _

FileFormat:=xlCSVMac, CreateBackup:=False

Regards,

Pankaj

[bohravanraj]

Hi Pankaj,

I tried finding this macro to convert CSV to .xlsx, but dint found any success.

can you share that macro here.

Regards,

Vanraj Dinesh Bohra

[passionate]

Hi Vanraj,

Use below code in excel macro and execute.

this will convert all xlsb files to xlsx in a folder:

Sub LoopFiles()
    WorkingDir = "C:\Users\xyz\Desktop\Newfolder\"
extension = "xlsb"

Dim fso, myFolder, fileColl, aFile, FileName, SaveName
Dim objExcel, objWorkbook

Set fso = CreateObject("Scripting.FilesystemObject")
Set myFolder = fso.GetFolder(WorkingDir)
Set fileColl = myFolder.Files

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False
objExcel.DisplayAlerts = False

For Each aFile In fileColl
ext = Right(aFile.Name, 4)
If UCase(ext) = UCase(extension) Then
'open excel
FileName = Left(aFile, InStrRev(aFile, "."))
Set objWorkbook = objExcel.Workbooks.Open(aFile)
SaveName = FileName & "xlsx"
objWorkbook.SaveAs SaveName, 23
objWorkbook.Close
End If
Next

Set objWorkbook = Nothing
Set objExcel = Nothing
Set fso = Nothing
Set myFolder = Nothing
Set fileColl = Nothing
End Sub

Regards,

Pankaj

[bohravanraj]

Hi Pankaj,

After Using the above Macro, it do convert into Xlsx format but we are not able to open it.

Please refer below error Screenshot.

Regards,

Vanraj Dinesh Bohra

[bohravanraj · Accepted solution]

Hi Pankaj,

Thank a million.

Ur code was working fine but only the file format in which u were converting was wrong i.e. FileFormat - 23.

It should be 51.

Please find below the correct code for this macro -

Sub LoopFiles()

    WorkingDir = "C:\"

extension = "xlsb"

Dim fso, myFolder, fileColl, aFile, FileName, SaveName

Dim objExcel, objWorkbook

Set fso = CreateObject("Scripting.FilesystemObject")

Set myFolder = fso.GetFolder(WorkingDir)

Set fileColl = myFolder.Files

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False

objExcel.DisplayAlerts = False

For Each aFile In fileColl

ext = Right(aFile.Name, 4)

If UCase(ext) = UCase(extension) Then

'open excel

FileName = Left(aFile, InStrRev(aFile, "."))

Set objWorkbook = objExcel.Workbooks.Open(aFile)

SaveName = FileName & "xlsx"

objWorkbook.SaveAs SaveName, 51

objWorkbook.Close

End If

Next

Set objWorkbook = Nothing

Set objExcel = Nothing

Set fso = Nothing

Set myFolder = Nothing

Set fileColl = Nothing

End Sub

Regards,

Vanraj Dinesh Bohra

[passionate]

Hi Vanraj

Yes, the filecode should have been changed.

Cheers,

Pankaj

[ashishkra]

I tried using this but at the end of each file I get a pop up saying "Microsoft Excel is waiting for another application to complete an OLE action" which needs to be pressed OK before it moves to converting next file. How can I overcome this?