Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
bohravanraj
Partner - Creator II
Partner - Creator II

Macro or batch to convert .xlsb to .xlsx

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

1 Solution

Accepted Solutions
bohravanraj
Partner - Creator II
Partner - Creator II
Author

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

View solution in original post

7 Replies
passionate
Specialist
Specialist

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
Partner - Creator II
Partner - Creator II
Author

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
Specialist
Specialist

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
Partner - Creator II
Partner - Creator II
Author

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.

Error.PNG

Regards,

Vanraj Dinesh Bohra

bohravanraj
Partner - Creator II
Partner - Creator II
Author

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
Specialist
Specialist

Hi Vanraj

Yes, the filecode should have been changed.

Cheers,

Pankaj

ashishkra
Contributor
Contributor

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?