Jump to content
mLipok

EXAMPLE: XLS , MDB from scratch with ADOX

Recommended Posts

A one user ask about Creating XLS file with ADO.

 

This was interesting to me so I use uncle google and find this:

https://forums.autodesk.com/t5/visual-basic-customization/ado-connection-create-file-excel/td-p/1675928

EDIT: https://msdn.microsoft.com/en-us/library/ms675849(v=vs.85).aspx

 

How you can read in this mentioned above link, This is not posible with ADO but quite easy with ADOX.

Here is AutoIt example:

;~ https://forums.autodesk.com/t5/visual-basic-customization/ado-connection-create-file-excel/td-p/1675928
;~ https://msdn.microsoft.com/en-us/library/ms675849(v=vs.85).aspx

#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

Global Const $ADO_adInteger = 3
Global Const $ADO_adVarWChar = 202

#include <Array.au3>
#include <MsgBoxConstants.au3>

_Example()

Func _Example()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    #forceref $oErrorHandler

    Local $oConnection = ObjCreate('ADODB.Connection')
    Local $oCatalog = ObjCreate('ADOX.Catalog')
    Local $oTable = ObjCreate('ADOX.Table')
    ; Local $oColumn = ObjCreate('ADOX.Column')

    $oConnection.open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & @ScriptDir & '\ADOX_EXAMPLE__MyContacts.xls' & ";Extended Properties=Excel 8.0")
    $oCatalog.ActiveConnection = $oConnection

    With $oTable
        .Name = "MyContacts"
        .ParentCatalog = $oCatalog
        
        ; Create fields and append them to the new Table object.
        .Columns.Append("ContactId", $ADO_adInteger)
        .Columns.Append("CustomerID", $ADO_adVarWChar, 16)
        
        ; Make the ContactId column and auto incrementing column
        .Columns("ContactId").Properties("AutoIncrement") = True
                
        ; Create fields and append them to the new Table object.
        .Columns.Append("FirstName", $ADO_adVarWChar, 80)
        .Columns.Append("LastName", $ADO_adVarWChar, 80)
        .Columns.Append("Phone", $ADO_adVarWChar, 20)
        .Columns.Append("Notes", $ADO_adVarWChar)
    EndWith

    $oCatalog.Tables.Append($oTable)

    ;Clean up
    $oConnection.Close

    $oConnection = Null
    ; $oColumn = Null
    $oTable = Null
    $oCatalog = Null
EndFunc    ;==>_Example


; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc    ;==>_ErrFunc

 

Have fun,
mLipok

Edited by mLipok
Title changed - mdb

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
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 APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

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 * Good coding practices in AutoIt * 

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) * IE in TaskScheduler

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: 2019-10-01

Share this post


Link to post
Share on other sites

Some time ago I asked on the forum: how to make SQL Schema comparison of the two databases.
Now Thanks to ADOX I think this is possible to enumerate each DB element and store information about it to XML file.
In that case this will be easy to compare 2 separate XML files at least with WinMerge.
I hope that before the beginning of summer I can do it.

EDIT:
Reference:

https://msdn.microsoft.com/en-us/library/ms254934(v=vs.110).aspx

https://msdn.microsoft.com/en-us/library/cc668760(v=vs.110).aspx

 

EDIT2:

Save Recordsets To/Load Recordsets From XML Files Using ADO 2.5:
http://www.freevbcode.com/ShowCode.asp?ID=312

http://www.freevbcode.com/ShowCode.asp?ID=1204

Persisting Records in XML Format
https://msdn.microsoft.com/en-us/library/ms681538(v=vs.85).aspx

Saving to the XML DOM Object
https://msdn.microsoft.com/en-us/library/ms675954(v=vs.85).aspx


Schema Section
https://msdn.microsoft.com/en-us/library/ms675943(v=vs.85).aspx

 

Hierarchical Recordsets in XML
https://msdn.microsoft.com/en-us/library/ms676166(v=vs.85).aspx

Edited by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
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 APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

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 * Good coding practices in AutoIt * 

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) * IE in TaskScheduler

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: 2019-10-01

Share this post


Link to post
Share on other sites

Here is new version of this example. Now also with MS ACCESS file:

;~ https://forums.autodesk.com/t5/visual-basic-customization/ado-connection-create-file-excel/td-p/1675928
;~ https://msdn.microsoft.com/en-us/library/ms675849(v=vs.85).aspx

#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

Global Const $ADO_adInteger = 3
Global Const $ADO_adVarWChar = 202
Global Const $ADOX_adPermObjTable = 1 ; The object is a table.
Global Const $ADOX_adAccessRevoke = 4 ; Any explicit access rights of the group or user will be revoked.
Global Const $ADOX_adRightFull = 268435456 ;            (&H10000000)The user or group has all permissions on the object.

#include <Array.au3>
#include <MsgBoxConstants.au3>

_Example_XLS()
_Example_ACCESS()

Func _Example_XLS()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    #forceref $oErrorHandler

    Local $oConnection = ObjCreate('ADODB.Connection')
    Local $oCatalog = ObjCreate('ADOX.Catalog')
    Local $oTable = ObjCreate('ADOX.Table')
    ; Local $oColumn = ObjCreate('ADOX.Column')

    $oConnection.open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & @ScriptDir & '\ADOX_Example_XLS__MyContacts.xls' & ";Extended Properties=Excel 8.0")
    $oCatalog.ActiveConnection = $oConnection

    With $oTable
        .Name = "MyContacts"
        .ParentCatalog = $oCatalog

        ; Create fields and append them to the new Table object.
        .Columns.Append("ContactId", $ADO_adInteger)
        .Columns.Append("CustomerID", $ADO_adVarWChar, 16)

        ; Make the ContactId column and auto incrementing column
        .Columns("ContactId").Properties("AutoIncrement") = True

        ; Create fields and append them to the new Table object.
        .Columns.Append("FirstName", $ADO_adVarWChar, 80)
        .Columns.Append("LastName", $ADO_adVarWChar, 80)
        .Columns.Append("Phone", $ADO_adVarWChar, 20)
        .Columns.Append("Notes", $ADO_adVarWChar)
    EndWith

    $oCatalog.Tables.Append($oTable)

    ;Clean up
    $oConnection.Close

    $oConnection = Null
    ; $oColumn = Null
    $oTable = Null
    $oCatalog = Null
EndFunc   ;==>_Example_XLS

Func _Example_ACCESS()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    #forceref $oErrorHandler

    ; https://msdn.microsoft.com/en-us/library/ms680934(v=vs.85).aspx
    Local $oCatalog = ObjCreate('ADOX.Catalog')
    Local $oTable = ObjCreate('ADOX.Table')
    ; Local $oColumn = ObjCreate('ADOX.Column')

    FileDelete('Example_from_AutoIt.mdb')
;~  Local $sConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Data Source=Example_from_AutoIt.mdb"
;~  Local $sConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Data Source=Example_from_AutoIt.mdb"
;~  Local $sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Example_from_AutoIt.mdb"
    Local $sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Example_from_AutoIt.mdb"
    Local $oConnection = $oCatalog.Create($sConnectionString)
    With $oTable
        .Name = "MyContacts"
        .ParentCatalog = $oCatalog

        ; Create fields and append them to the new Table object.
        .Columns.Append("ContactId", $ADO_adInteger)
        .Columns.Append("CustomerID", $ADO_adVarWChar, 16)

        ; Make the ContactId column and auto incrementing column
        .Columns("ContactId").Properties("AutoIncrement") = True

        ; Create fields and append them to the new Table object.
        .Columns.Append("FirstName", $ADO_adVarWChar, 80)
        .Columns.Append("LastName", $ADO_adVarWChar, 80)
        .Columns.Append("Phone", $ADO_adVarWChar, 20)
        .Columns.Append("Notes", $ADO_adVarWChar)
    EndWith

    $oCatalog.Tables.Append($oTable)

    #Region something not working here yet - work in progress
    ; Groups and Users Append, ChangePassword Methods Example (VB)
    ; https://msdn.microsoft.com/en-us/library/ms678350(v=vs.85).aspx
;~  Local $oUser = ObjCreate('ADOX.User')
;~  $oUser.Name = 'AutoIt'
;~  $oUser.ChangePassword("", "Forum")
;~  Local $oUsers_coll = $oCatalog.Users
;~  $oUsers_coll.Refresh()
;~  MsgBox(0, '', $oUsers_coll.Count)
;~  $oCatalog.Groups.Append("Accounting")
;~  $oCatalog.Users.Append("AutoIt2","Forum")

    ; GetPermissions and SetPermissions Methods Example (VB)
    ; https://msdn.microsoft.com/en-us/library/ms677537(v=vs.85).aspx
;~  $oCatalog.Users("AutoIt").SetPermissions("MyContacts", $ADOX_adPermObjTable, $ADOX_adAccessRevoke, $ADOX_adRightFull)
    #EndRegion something not working here yet - work in progress

    ;Clean up
    $oConnection.Close

    $oConnection = Null
    $oTable = Null
    $oCatalog = Null
EndFunc   ;==>_Example_ACCESS

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
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 APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

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 * Good coding practices in AutoIt * 

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) * IE in TaskScheduler

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: 2019-10-01

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

  • Similar Content

    • By Rskm
      Hi, I am using excel as input media for my program. The excel file (i tried with .xls, .xlsx and .xlsm format) has inputs which the autoit script reads during the run and performs few calculations. Some times (not always), after the run, when i try to open the excel file manually, the file doesnt open at all in excel. see the screenshot attached. However, if the execute the autoit script, the scripts still reads the existing data from that excel and performs the calcs. I copied the excel file to another computer and there too, it doesnt open.  So, after this, i cannot edit the excel forever (if i need to change any inputs). It is only this particular file that got affected. other excel files works normal.  What could be the problem here.  please help as this is a new challenge for me during my program development. 

    • By Taxyo
      Hi,
       
      I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 
      I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 
       
      Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc  
      While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 
      Where am I going wrong?
       
      Thanks! 
    • By Most
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\trans.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\trans.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Read data from a single cell on the active sheet of the specified workbook ; ***************************************************************************** Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult) Hi, all.
      Ok, here is the deal. I have simple excel file called trans.xlsx. It's located in the directory of script. In general i don't care where to store it. 
      What i do need is to open excel file and copy one by one numbers from cells. I've tried different ways, examples. But i only get error, says: error = 3, extended = 1. I saw different posts from different years. I even tried to use simple example from manual file. But always get error.

      In general my goal get numbers one by one and post it to let's say search filed in my PC one by one. Or to notepad (but one by one, in kind of loop). 
      I've learned how to copy or show in message box some info from other apps. But with excel i'm stuck. 

      I'm able to open needed window based on "title" of excel. But i don't succeed of copying info from cells. 

      Would be appreciate for any help. 
      So, in this code i'm trying at least to read from cell A1. Doesn't matter what Sheet. 

      I use Windows 10, Excel for Office 365. 
      Thank you in advance. 
    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
×
×
  • Create New...