[Solved] Can it be used as an Excel database?

[loaded]

Hi All,
Is there a method where we can query an open and active Excel list on the screen as if we were using a database (with SQL Sentences)?
By the way, I provide the connection with Excel with GetActiveOleObject.
I would be very grateful if you could share your knowledge and experience.

[rvk]

Why do you want to use SQL? Or is it that you just want a TDataset?

You could copy all content of a Excel (via GetOleObject) to a TClientDataSet (TBufDataset).
And work with that.
I'm not aware of a complete TDataSetProvider that directly works with an active object from GetOleObject.

I'm also not sure what you actually want.
You can also look into fpspreadsheet.

[Zvoni]

Provided the OLE-DB-Provider is installed, you can use an ODBC-Connection

This is my Connection-String i use in VBA to query an Excel-Workbook which has MACROS, though using ADODB in VBA (This is NOT Pascal-Syntax!)
Beware of the double-quotes in there. For "Standard"-Excel (no macros) i'm letting you have a guess what has to be changed

Code: [Select]

ConString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=""" & ThisWorkbook.FullName & """;Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1"";"


[loaded]

First of all thank you so much rvk and Zvoni for replying.

I'm also not sure what you actually want.

My workplace receives files in excel format from an official institution with the same schema but changing content, and I calculate the statistics of different information such as unique, similar, total and number in these files.
It is not possible for me to ask for change from the other side.

Why do you want to use SQL? Or is it that you just want a TDataset?

It is very easy to do the above operations in SQL, it is a little more problematic in Excel. For this reason  ;
I wanted to reuse the Sql sentences in the Postgresql database that I am currently using, with a few minor changes.

Provided the OLE-DB-Provider is installed, you can use an ODBC-Connection

Code: [Select]

ConString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=""" & ThisWorkbook.FullName & """;Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1"";"


Yes this might work, thank you very much for the info.

[wp]

Hi All,
Is there a method where we can query an open and active Excel list on the screen as if we were using a database (with SQL Sentences)?

In fpspreadsheet, there is a TsWorksheetDataset which allows to access a spreadsheet like a database. No SQL, though.

[loaded]

Thank you for your reply and additional information, wp

[heejit]

You can use pl/python in Postgresql also not a FPC solution.

[loaded]

Thank you very much for your reply my brother heejit.
I had solved my problem. But I forgot to mention, sorry.
Sometimes the best way is the old way.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button_ConnectClick(Sender: TObject);
2. var
3.     ExcelApp,ExcelApp_Book,ExcelApp_Sheet:OleVariant;
4. begin
5.   ExcelApp := GetActiveOleObject('Excel.Application');
6.   ExcelApp_Book := ExcelApp.ActiveWorkBook;
7.   ExcelApp_Sheet:=ExcelApp_Book.ActiveSheet;
8.   ExcelApp_RowCount:=ExcelApp_Sheet.Usedrange.EntireRow.count;
9.   ExcelApp_ColCount:=ExcelApp_Sheet.UsedRange.Columns.count;
10.   //string(ExcelApp_Book.name +' [' +ExcelApp_Sheet.name+ ']');
11. end;

[Nicole]

I worked a lot with this OLE.
Under the line: I did away with it.
It is too - I do not know an English word for it, German "frickelig". You have to work in Excel and there you can do a wrong click. The cells move that quickly and without warning.

The code was never robust quality.

So my answer is, "if any different way is possible, - avoid it". Try hard to avoid it, it will not make you happy on long run.

[loaded]

Thank you very much Nicole for your reply and warning.
I've been experiencing the problem you mentioned from time to time. As a solution, I work with a single excel file as much as possible. Before working, I use the following code.

Code: Pascal  [Select][+][-]

1. ExcelApp .Visible:=false;

[Thaddy]

Translation is "prickly", "awkward" or "nasty". Even "cumbersome" would apply.
I learned to avoid OLE like the plague since 1998...If possible use early binding.

Then again: German is a rather strange dialect of Dutch..

[loaded]

If possible use early binding.

Thank you very much for the reply, Master Thaddy. Maybe I've been using ole for twelve years, I didn't know about such a thing.
I learned something new today thanks to you. I hope one day I'll buy you all a cup of coffee in return for what I've learned. 

[rvk]

If possible use early binding.

Thank you very much for the reply, Master Thaddy. Maybe I've been using ole for twelve years, I didn't know about such a thing.

Please note that your program won't run on systems without (offline-) Office installed when using early binding.
That's why I avoid early binding at all cost 

(My software also runs needs to run on systems with LibreOffice or even without an office-suit in which case cloud services could be used)

[loaded]

Thank you very much for the information rvk
In this forum, pearls are poured out as the masters speak.

[Nicole]

ExcelApp .Visible:=false;
This is a good idea!

And a second point. If you enjoy the platform neutral Lazarus, you bind yourself to any Office and windows.

Sure, if you have "working team" with Excel since decades, it is not a good idea to change anything.

For me it is good to know: I am free to change to Linux every time. That I will not do it, because I am afraid, I am quite happy with the functionality of Windows, this is a different topic.