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

Programming => Databases => Topic started by: loaded on September 20, 2022, 02:33:40 pm

Title: [Solved] Can it be used as an Excel database?
Post by: loaded on September 20, 2022, 02:33:40 pm

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.

Title: Re: Can it be used as an Excel database?
Post by: rvk on September 20, 2022, 03:13:08 pm

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.

Title: Re: Can it be used as an Excel database?
Post by: Zvoni on September 20, 2022, 03:48:30 pm

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"";"

Title: Re: Can it be used as an Excel database?
Post by: loaded on September 20, 2022, 03:51:30 pm

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

Quote from: rvk on September 20, 2022, 03:13:08 pm

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.

Quote from: rvk on September 20, 2022, 03:13:08 pm

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.

Quote from: Zvoni on September 20, 2022, 03:48:30 pm

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.

Title: Re: Can it be used as an Excel database?
Post by: wp on September 20, 2022, 03:52:21 pm

Quote from: loaded on September 20, 2022, 02:33:40 pm

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.

Title: Re: Can it be used as an Excel database?
Post by: loaded on September 21, 2022, 10:34:56 am

Thank you for your reply and additional information, wp

Title: Re: Can it be used as an Excel database?
Post by: heejit on October 31, 2022, 05:31:44 am

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

Title: Re: Can it be used as an Excel database?
Post by: loaded on October 31, 2022, 06:07:28 am

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][+]

procedure TForm1.Button_ConnectClick(Sender: TObject);
var
    ExcelApp,ExcelApp_Book,ExcelApp_Sheet:OleVariant;
begin
  ExcelApp := GetActiveOleObject('Excel.Application');
  ExcelApp_Book := ExcelApp.ActiveWorkBook;
  ExcelApp_Sheet:=ExcelApp_Book.ActiveSheet;
  ExcelApp_RowCount:=ExcelApp_Sheet.Usedrange.EntireRow.count;
  ExcelApp_ColCount:=ExcelApp_Sheet.UsedRange.Columns.count;
  //string(ExcelApp_Book.name +' [' +ExcelApp_Sheet.name+ ']');
end; 

Title: Re: [Solved] Can it be used as an Excel database?
Post by: Nicole on October 31, 2022, 12:21:47 pm

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.

Title: Re: [Solved] Can it be used as an Excel database?
Post by: loaded on October 31, 2022, 01:08:20 pm

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][+]

ExcelApp .Visible:=false;

Title: Re: [Solved] Can it be used as an Excel database?
Post by: Thaddy on October 31, 2022, 01:10:34 pm

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.. O:-)

Title: Re: [Solved] Can it be used as an Excel database?
Post by: loaded on October 31, 2022, 02:03:41 pm

Quote from: Thaddy on October 31, 2022, 01:10:34 pm

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. ;D
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. :)

Title: Re: [Solved] Can it be used as an Excel database?
Post by: rvk on October 31, 2022, 02:16:20 pm

Quote from: loaded on October 31, 2022, 02:03:41 pm

Quote from: Thaddy on October 31, 2022, 01:10:34 pm
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. ;D

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 :D

(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)

Title: Re: [Solved] Can it be used as an Excel database?
Post by: loaded on October 31, 2022, 02:55:11 pm

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

Title: Re: [Solved] Can it be used as an Excel database?
Post by: Nicole on October 31, 2022, 05:45:29 pm

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.