topic Re: Macro or batch to convert .xlsb to .xlsx in QlikView

Macro or batch to convert .xlsb to .xlsx

bohravanraj — Fri, 18 Aug 2017 03:03:21 GMT

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

Re: Macro or batch to convert .xlsb to .xlsx

passionate — Fri, 18 Aug 2017 05:54:45 GMT

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

Re: Macro or batch to convert .xlsb to .xlsx

bohravanraj — Fri, 18 Aug 2017 06:25:32 GMT

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

Re: Macro or batch to convert .xlsb to .xlsx

passionate — Fri, 18 Aug 2017 08:14:28 GMT

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

Re: Macro or batch to convert .xlsb to .xlsx

bohravanraj — Mon, 21 Aug 2017 01:50:31 GMT

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

Re: Macro or batch to convert .xlsb to .xlsx

bohravanraj — Mon, 21 Aug 2017 07:02:20 GMT

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

Set objWorkbook = Nothing

Set objExcel = Nothing

Set fso = Nothing

Set myFolder = Nothing

Set fileColl = Nothing

End Sub

Regards,

Vanraj Dinesh Bohra

Re: Macro or batch to convert .xlsb to .xlsx

passionate — Thu, 24 Aug 2017 05:58:11 GMT

Hi Vanraj

Yes, the filecode should have been changed.

Cheers,

Pankaj

Re: Macro or batch to convert .xlsb to .xlsx

ashishkra — Mon, 14 Jan 2019 06:27:33 GMT

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?