Excel Import

HCL Domino Domino Forum
1

Hi

Can someone please let me know how to import data from mutiple sheets in a excel file.

I have a excel file with multiple sheets and need a lotusscript code to validate and download data from each sheet (sheet 1, 2 … x) automatically into lotus notes database.

Any thoughts.

Thank you

2

Subject: Excel Import

You didn’t give us much to go on, and there are so many ways to do this, so I will point you to ONE way to do it (I did not write this particular code and I have not used it but I have looked it over) that appears to be quite flexible and fast – it will handle multiple sheets and uses ADODB instead of OLE to read the Excel file. This means you can run it without having Excel on the machine the code is going to run on, like on a server in the case of a scheduled agent. Also, ADODB is a LOT faster than OLE, so if your spreadsheets are large or if you have a lot of spreadsheets to import, this is the way to go.

You WILL have to modify the code to meet your needs, and you will have to pay special attention to data types, but this should get you going…

HTH,

Gary

http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/4ee9b3ae834bf26c85257134004c9add?OpenDocument&Highlight=0,excel,import,sheet

3

Subject: RE: Excel Import

Gary

Thanks for your response. Can you please let me know, if I need to have ODBC setup for this ADB Connection on the server?

Thanks

4

Subject: RE: Excel Import

You do not have to setup ODBC to use ADODB – that’s one of the great things about it!

Gary

5

Subject: RE: Excel Import

Hi Gary

Thanks for your response. I tried the script and it works like charm when i ran manually…

But it doesnt work when the agent is scheduled. I get the following error message:

Error:213 Microsoft JET Database engine cannot open the file \shared_drive\report.xls. It is already opened exclusively by another user or you need permission to view its data.

I used the same ID file to run manually and for scheduled process. I have access to shared network.

can you please help.

Thanks in advance.

6

Subject: RE: Excel Import

If it’s running scheduled then the SERVER or PROCESS needs to be able to access the other server/path/file where the spreadsheet resides. It’s not necessarily your ID that counts here…

Gary

7

Subject: RE: Excel Import

The server can see the shared network drive without any issue. The problem is its not resolved when i run as schedule agent.

Same error that i mentioned in my earlier post…

ADODB Connection: Operation is not allwed when the operation is closed.

Can you please explain?

8

Subject: RE: Excel Import

Can you post your code please? Maybe something you have overlooked…

Gary