Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have written a script that loads specific fields from all .xls files within a folder:
Load Date,
Budget,
Forecast,
[Outlook This Week],
[Outlook Last Week],
filename() as NameFrom C:\testLOADALL\*.xls
(biff, embedded labels, header is 2 lines, table is Sheet1$);
This probem is, when I run this script I get a 'Cannot read BIFF file' error however the correct fields are still loaded from all .xls files in the folder:
Script still loads fields after error:
Does anybody know why this error is happening yet I am getting the correct result? I modified my script to read .xlsx ooxml files and I didn't receive an error however all of the files I need to load are .xls.
Any help/thoughts appreciated,
Matt
Hi Matt,
seems that one of your .xls isn't a "real" xls-File. Some programs export csv-files and call them .xls...
I remember having this kind of problem at my previous job. I solved it by writing a little EXCEL-Macro which opened all files in the path and saved them explicitly as .xls (Excel 97-2003).
regards,
Edgar
Hi Matt,
seems that one of your .xls isn't a "real" xls-File. Some programs export csv-files and call them .xls...
I remember having this kind of problem at my previous job. I solved it by writing a little EXCEL-Macro which opened all files in the path and saved them explicitly as .xls (Excel 97-2003).
regards,
Edgar
I agry with Edgar. Coming to your next Q.. Script will load after error If you press OK. If you would have press Cancel It would have closed and would have asked you To Load with old data.
Swapnil.
Thanks for your response Edgar and Swapnil.
I tried creating an Excel .xls file and the script worked fine with it so it looks like I will have to create a macro like you suggested.
Matt
Edgar,
Do you have any suggestions on how to write the Excel macro? Also, will the macro need to be written in each individaul excel file? I have multiple excel files within a folder that I'll need to convert to 'real' .XLS files
Thanks!
Liz
How do you know your file is not an xls file?
Could you pls share the macro you used?
Hi ekech_infomotion ,
Can you please share the macro which actually converts to csv from xls.
Edgar,
Do you have any suggestions on how to write the Excel macro? Can you please explain me .
I had this problem with trying to automate the refresh of a saved source XLS file.
I receive the file automatically by email. I was using PowerAutomate to save the email attachment to my QlikSense source directory in my file system via on-premises gateway. This worked, but the Qlik script would not read the file, with the BIFF error below. Opening the document and closing it without saving somehow fixed it.
My solution was to instead have the PowerAutomate flow save the email attachment to a file on Sharepoint. Then next step, fetch the content from the newly created sharepoint file and save this to the File System. Somehow, the process gets the file format right when taking it from Sharepoint, but not from an email attachment.
Just FYI in case it helps anyone.