Recent

Author Topic: [Solved] Can it be used as an Excel database?  (Read 857 times)

loaded

  • Hero Member
  • *****
  • Posts: 632
[Solved] Can it be used as an Excel database?
« 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.
« Last Edit: October 31, 2022, 06:07:42 am by loaded »
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

rvk

  • Hero Member
  • *****
  • Posts: 5017
Re: Can it be used as an Excel database?
« Reply #1 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.

Zvoni

  • Hero Member
  • *****
  • Posts: 1527
Re: Can it be used as an Excel database?
« Reply #2 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"";"
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

loaded

  • Hero Member
  • *****
  • Posts: 632
Re: Can it be used as an Excel database?
« Reply #3 on: September 20, 2022, 03:51:30 pm »
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.
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

wp

  • Hero Member
  • *****
  • Posts: 10288
Re: Can it be used as an Excel database?
« Reply #4 on: September 20, 2022, 03:52:21 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.

loaded

  • Hero Member
  • *****
  • Posts: 632
Re: Can it be used as an Excel database?
« Reply #5 on: September 21, 2022, 10:34:56 am »
Thank you for your reply and additional information, wp
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

heejit

  • Full Member
  • ***
  • Posts: 245
Re: Can it be used as an Excel database?
« Reply #6 on: October 31, 2022, 05:31:44 am »
You can use pl/python in Postgresql also not a FPC solution.

loaded

  • Hero Member
  • *****
  • Posts: 632
Re: Can it be used as an Excel database?
« Reply #7 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][+][-]
  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;
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

Nicole

  • Sr. Member
  • ****
  • Posts: 496
Re: [Solved] Can it be used as an Excel database?
« Reply #8 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.

loaded

  • Hero Member
  • *****
  • Posts: 632
Re: [Solved] Can it be used as an Excel database?
« Reply #9 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][+][-]
  1. ExcelApp .Visible:=false;
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

Thaddy

  • Hero Member
  • *****
  • Posts: 12207
Re: [Solved] Can it be used as an Excel database?
« Reply #10 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:-)
« Last Edit: October 31, 2022, 01:15:16 pm by Thaddy »
Manuals, manuals, manuals first.
You have incompetence and sheer incompetence.

loaded

  • Hero Member
  • *****
  • Posts: 632
Re: [Solved] Can it be used as an Excel database?
« Reply #11 on: October 31, 2022, 02:03:41 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.  :)
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

rvk

  • Hero Member
  • *****
  • Posts: 5017
Re: [Solved] Can it be used as an Excel database?
« Reply #12 on: October 31, 2022, 02:16:20 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)

loaded

  • Hero Member
  • *****
  • Posts: 632
Re: [Solved] Can it be used as an Excel database?
« Reply #13 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.
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

Nicole

  • Sr. Member
  • ****
  • Posts: 496
Re: [Solved] Can it be used as an Excel database?
« Reply #14 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.

 

TinyPortal © 2005-2018