mLipok

EXAMPLE: XLS , MDB from scratch with ADOX

4 posts in this topic

#1 ·  Posted (edited)

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
1 person likes this

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



#2 ·  Posted (edited)

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

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

#4 ·  Posted (edited)

:)

 

 

 

Edited by Skysnake

Skysnake

Why is the snake in the sky?

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

    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.