Forum > Databases
[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: ---ConString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=""" & ThisWorkbook.FullName & """;Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1"";"
--- End code ---
loaded:
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.
--- End quote ---
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?
--- End quote ---
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: ---ConString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=""" & ThisWorkbook.FullName & """;Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1"";"
--- End code ---
--- End quote ---
Yes this might work, thank you very much for the info.
wp:
--- 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)?
--- End quote ---
In fpspreadsheet, there is a TsWorksheetDataset which allows to access a spreadsheet like a database. No SQL, though.
Navigation
[0] Message Index
[#] Next page