hunte922

[SOLVED] Excel XLS/XLSM Read/Write without Office installed

15 posts in this topic

#1 ·  Posted (edited)

I am using an Excel file as a database for a point of sales system.

I only need to view the database using Office on one computer. All others only need to read/write to the file.

Is there a way to read and write to XLS or XLSM files without installing Office? Changing the file type is okay as long as it can be opened in Excel and has all the same features as an XLS or XLSM file.

Thanks in advance.

Hunter

 

Edit: Solution found

Edited by hunte922

Share this post


Link to post
Share on other sites



You could use ADO to access Excel like a database.
Either check my ADO wiki tutorial or check the ADO UDF written by mLipok.

There are other UDFs available which are limited just to XLSX data type.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
59 minutes ago, water said:

You could use ADO to access Excel like a database.
Either check my ADO wiki tutorial or check the ADO UDF written by mLipok.

There are other UDFs available which are limited just to XLSX data type.

In your "ADO Example Excel", how can I read cells past the 2nd column? I can only get A:B.

Share this post


Link to post
Share on other sites

@hunte922

read here: https://www.connectionstrings.com/excel-2007-odbc/

To learn how to use it you should use this example:

Func _Example_MSExcel()

    Local $sFileFullPath = Default ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath = Default, $sProvider, $sExtProperties, $HDR, $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSExcel

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

14 minutes ago, mLipok said:

@hunte922

read here: https://www.connectionstrings.com/excel-2007-odbc/

To learn how to use it you should use this example:

Func _Example_MSExcel()

    Local $sFileFullPath = Default ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath = Default, $sProvider, $sExtProperties, $HDR, $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSExcel

 

I'm getting this error when I run the example, with or without $sFileFullPath defined:

###############################
ADO.au3 v.2.1.13 BETA (1155) : ==> COM Error intercepted !
$oADO_Error.description is:     The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  1155
$oADO_Error.source is:  Microsoft JET Database Engine
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     5003011
###############################

 

Edited by hunte922

Share this post


Link to post
Share on other sites

Do you have sheet named "Sheet1" in your XLS file ?


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites
1 minute ago, mLipok said:

Do you have sheet named "Sheet1" in your XLS file ?

Yes.

Share this post


Link to post
Share on other sites

Although you can use ADO to access the data from an Excel file without opening the Excel application, to my understanding, you still need to have Excel (or rather Office, since other Office applications also use Excel objects) installed on the machine running the code in order for the Excel object to be available for use.  Else-wise, how would the system know how to access newer excel file versions/formats, if the files needed for it do not come from the installation of the version of Excel that defines that new file version/format.

If Windows came with the objects readily available, nothing would stop developers from creating fully MS Office file compatible applications without anyone having to pay Microsoft for their commercial (not-free) Office at all.  Office is Microsoft's 2nd largest revenue source right now (according to my reading).  They are not going to give it away for free.

Other applications that are able to read/write Office file formats have either had to reverse engineer the file formats, or pay licensing fees to Microsoft to do so.

Since the OP is stipulating "without installing Office", the ADO solution is not going to work.

Recommendations:

  • If you do not want to have to pay for Office licenses on all of the machines, you might want to look into using SQLite, or some other data storage method.  You can always use ODBC on the one machine that needs to look at the data in Excel to pull the data into an Excel file.
  • The other concern you will need to address is that if multiple PCs are attempting to access/update the Excel file concurrently, you will need to implement some form of file locking. (This is also true if using other non-client-server based solutions, including SQLite)  You can look at my File Locking with Cooperative Semaphores for one possible solution to that problem.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

2007 Office System Driver: Data Connectivity Components
https://www.microsoft.com/en-us/download/details.aspx?id=23734


 

Quote

1.    INSTALLATION AND USE RIGHTS.  You may install and use any number of copies of the software on your devices to design, develop and test your programs that read or write files in a Microsoft Office file format.  You may not use the software to develop a general purpose word processing, spreadsheet or database management system, and you may not use the software in your programs as a data store.

 

ps.

Sorry for long waiting ( I was watching "Doctor Who" season 8 "part" 8 , with the "moon story" ).


 

EDIT:

Microsoft Access Database Engine 2010 Redistributable (EDIT: If I understand well, this have more Dev Friendly license)
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
 

Managing Data Sources
https://msdn.microsoft.com/en-us/library/ms712362(v=vs.85).aspx

About Drivers and Data Sources
https://msdn.microsoft.com/en-us/library/ms710285(v=vs.85).aspx

 

Edited by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

@hunte922

use _ADO_GetProvidersList() to display list of provider instaled on your machine.
Post here content from _ArrayDisplay()

Also post entire SciTE console output from your testing case from post #5

 

Edited by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites
3 hours ago, mLipok said:

@hunte922

use _ADO_GetProvidersList() to display list of provider instaled on your machine.
Post here content from _ArrayDisplay()

Also post entire SciTE console output from your testing case from post #5

No need - I've already figured out how to get it to work. The only problem now is getting Microsoft Access Database Engine 2010 to process Excel files without having it installed. Do you think you know a quick fix?

Func _ADOExcel_Open($sFileName, $readonly = "True")
   Global $ADO_Connection = ObjCreate("ADODB.Connection")
   Local $ADO_ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No;ReadOnly=' & $readonly & '"'
   $ADO_Connection.Open($ADO_ConnectionString)
EndFunc

 

Share this post


Link to post
Share on other sites

I'm a little late but just to let you know that those xls (well all office documents) are just zip files with files that control how the data is formatted in the corresponding office program.

If you rename the .xls to .zip and open it up, there is a file called sharedStrings.xml that has all the data for the xls file and the sheet1.xml file has the row and column for the corresponding data. I've never tried to put it together but if you get the ADO working you could probably figure it out, or someone from google already has and posted some tutorial for deciphering it.

Share this post


Link to post
Share on other sites

@hunte922 If you need to install  Microsoft Access Database Engine 2010 then there is no other way but you must read carefull the license from this MS software.

@InunoTaishou so try it with: https://www.autoitscript.com/w/images/6/6e/ADO_Example_Excel.xls
As I think you mean XLSX , but this is not the sam thing as XLS .


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

I got it to work. Finally.

It's 3 AM.

I made the change from XLS to XLSX which brought some extra, frustrating problems related to cell writing.

I also had to install the 32-bit version of Microsoft Access Database Engine 2010 (I originally had the 64-bit version).

Here's what I've done:

#include <ADO_CONSTANTS.au3>

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
Func MyErrFunc()
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"    & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
Endfunc

Func _ADOExcel_Open($sFileName, $readonly = "True")
   Global $ADO_Connection = ObjCreate("ADODB.Connection")
   Local $ADO_ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0;HDR=NO;IMEX=0;ReadOnly=' & $readonly & '"'
   $ADO_Connection.Open($ADO_ConnectionString)
EndFunc

Func _ADOExcel_SheetRead()
   Local $ADO_Recordset = ObjCreate("ADODB.Recordset")
   Local $ADO_SQL_Query = "Select * FROM [Sheet1$]"
   $ADO_Recordset.Open($ADO_SQL_Query, $ADO_Connection, $ADO_adOpenForwardOnly, $ADO_adLockReadOnly, $ADO_adCmdTableDirect)
   Local $aArray = $ADO_Recordset.GetRows
   $ADO_Recordset.Close
   Return $aArray
EndFunc

Func _ADOExcel_CellRead($col, $row)
   Local $ADO_Recordset = ObjCreate("ADODB.Recordset")
   Local $ADO_SQL_Query = "Select * FROM [Sheet1$]"
   $ADO_Recordset.Open($ADO_SQL_Query, $ADO_Connection, $ADO_adOpenForwardOnly, $ADO_adLockReadOnly, $ADO_adCmdTableDirect)
   Local $aArray = $ADO_Recordset.GetRows
   $ADO_Recordset.Close
   Return $aArray[$row - 1][$col]
EndFunc

Func _ADOExcel_CellWrite($col, $row, $value = "")
   Local $ADO_Recordset = ObjCreate("ADODB.Recordset")
   Local $ADO_SQL_Query = "Select * FROM [Sheet1$]"
   $ADO_Recordset.Open($ADO_SQL_Query, $ADO_Connection, $ADO_adOpenKeyset, $ADO_adLockOptimistic, $ADO_adCmdText)
   $ADO_Recordset.Move($row - 1)
   $ADO_Recordset.Update($col, $value)
   $ADO_Recordset.Close
EndFunc

Func _ADOExcel_Close()
   $ADO_Connection.Close
   $ADO_Connection = Null
EndFunc

 

Edited by hunte922

Share this post


Link to post
Share on other sites

Glad you were able to make it work.  Don't forget to look into the concurrency issue if multiple machines have a potential to update the files.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now