Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shaan007
Partner
Partner

How to Skip an ODBC connection scan if the excel file is opened by another user

I need to scan all the excel files' info into [LoadedTables] from a shared location (D:\SharedExcelFiles)

I have created an ODBC connection. but if anyone keeps an excel file open I am getting this error while loading.

"The Microsoft Access database engine cannot open or write to the file. It is already opened exclusively by another user, or you need permission to view and write its data."

I wanted to skip this error and scan the next excel file connection.

something like this

FOR EACH file in FileList(Path &'\*.' &'xlsx');

if ( excel file is not opened by another user / ODBC connection is fine)

then ODBC CONNECT32 TO [SOH_Excel_Files;DBQ=$(file)];

else

next file

----------------------------------------------------------------------------

Current working code to scan subfolders is >>

sub ScanFolder(Root)

FOR EACH file in FileList(Root &'\*.' &'xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [SOH_Excel_Files;DBQ=$(file)];

LoadedTables:
SQLtables;
DISCONNECT;


next file

for each SubDirectory in DirList(Root &'\*')
call ScanFolder(SubDirectory)
next SubDirectory

end sub

Call ScanFolder('D:\SharedExcelFiles');

Labels (1)
1 Reply
Brett_Bleess
Support (Former)
Support (Former)

The list of Error Variables can be found here:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Error...

The other option might be ErrorMode:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Error...

Regards,
Brett

 

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.