Convert vbs ADODB connection to AutoIt

[najaden]

Hello Guys,

I'm a new AutoIt user.

Can anyone help me to convert below standing ADODB connection-string which is made in vbs to use in AutoIt. I use this to extract data from a singe Excelsheet to make lists.

code:

asvmail.xls is the name of the excel file

mail$ is the name of the sheet

=======================================================

' Open a connection to the database

Set objConn = Wscript.CreateObject("ADODB.Connection")

objConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _

"DriverId=790;" & _

"Dbq=asvmail.xls;"

Set myFileSystem=Wscript.CreateObject("Scripting.FileSystemObject")

Pad = myFileSystem.GetAbsolutePathName(".")

Set mytextstream=myFileSystem.CreateTextFile(Pad & "\myfile.txt")

strSQL = "SELECT * FROM [mail$] WHERE kl = 'A' ORDER BY nr ASC; "

Set objRst = objConn.execute(strSQL)

=================================================

all results are wriiten to myfile.txt.

Thanks very much,

Najaden

[randallc]

Hi,

This might get you started, but not accurate?

$s_asvmail=@ScriptDir&"\asvmail.xls"
$s_Sheet="mail$"
;asvmail.xls is the name of the excel file
;mail$ is the name of the sheet
;=======================================================

; Open a connection to the database
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"&$s_asvmail&";" )

$myFileSystem=ObjCreate("Scripting.FileSystemObject")
$Pad = $myFileSystem.GetAbsolutePathName(".")
$mytextstream=$myFileSystem.CreateTextFile($Pad & "\myfile.txt")


$strSQL = "SELECT * FROM [mail$] WHERE kl = 'A' ORDER BY nr ASC; "
$objRst = $objConn.execute($strSQL)

Also, do a "search" for adodb connection scripts..

I would be interested to see the finished script; is that the complete vbs anyway?

Randall

Edited June 21, 2006 by randallc

[najaden]

Thanks,

I'm gonna try it right away, I'll be back to you very soon....

//

Of course this is just the beginning of the script.

This Excel sheet contains all the data of a huge autosports-event and makes

the final results covering 6 classes, mix- and ladies entries, 3 kind of teams, 22 different

car brands and some more results.

All of them are extracted from this excel-sheet and written into a html formatted txt-file.

See you ..

Najaden

=========

Hi,

This might get you started, but not accurate?

$s_asvmail=@ScriptDir&"\asvmail.xls"
$s_Sheet="mail$"
;asvmail.xls is the name of the excel file
;mail$ is the name of the sheet
;=======================================================

; Open a connection to the database
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"&$s_asvmail&";" )

$myFileSystem=ObjCreate("Scripting.FileSystemObject")
$Pad = $myFileSystem.GetAbsolutePathName(".")
$mytextstream=$myFileSystem.CreateTextFile($Pad & "\myfile.txt")
$strSQL = "SELECT * FROM [mail$] WHERE kl = 'A' ORDER BY nr ASC; "
$objRst = $objConn.execute($strSQL)

Also, do a "search" for adodb connection scripts..

I would be interested to see the finished script; is that the complete vbs anyway?

Randall

[najaden]

Thanks,

I'm gonna try it right away, I'll be back to you very soon....

//

Of course this is just the beginning of the script.

This Excel sheet contains all the data of a huge autosports-event and makes

the final results covering 6 classes, mix- and ladies entries, 3 kind of teams, 22 different

car brands and some more results.

All of them are extracted from this excel-sheet and written into a html formatted txt-file.

See you ..

Najaden

=========

Sorry,

does not work.

says:

ERROR:

unable to parse line: $objRst = $objConn.execute($strSQL)

By the way:

Do I need the new Beta version for this ???

Do I need any includes ???

Najaden

[Ravenlark]

Sorry,

does not work.

says:

ERROR:

unable to parse line: $objRst = $objConn.execute($strSQL)

By the way:

Do I need the new Beta version for this ???

Do I need any includes ???

Najaden

@Najaden

Yes, you need the Beta to use ObjCreate, which is probably why you get an error using the object.

Ravenlark

[randallc]

Hi,

This works for Access ; is that any help?

$sDbTable="Access"
$sDb=@ScriptDir&"\access.db3"
local $NewRows,$sOutput,$sNewQuery
;$s_database=FileGetShortName(@ProgramFilesDir&"\Deltec\CoZmanager\HistoryLogDB.mdb")
$s_database=FileGetShortName(@ScriptDir&"\HistoryLogDB.mdb")
$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$s_database&';UID=admin;PWD=deltec')
$oRS.Open("SELECT * FROM qryBGHistory ORDER BY qryBGHistory.TimeStamp DESC" , $oConn, 1, 3)

Differences are;

1. FileGetshortName

2. $oRS = ObjCreate("ADODB.Recordset") - ; and open that? - but I'm not sure why it should be different in vbs...

Randall

[randallc]

PS -see above;

I see somewhere else that the Autoit Obj commands may only like text in the table names; are they changeable in the spreadsheet?

Best, Randall

[najaden]

PS -see above;

I see somewhere else that the Autoit Obj commands may only like text in the table names; are they changeable in the spreadsheet?

Best, Randall

Thanks so far Randall.

In the meantime I found the code that gives no errors and seems to connect to the Excel properly.

Both connection strings seems to be allright, the first one is the one I use in VBS.

The only problem is that the results look like:

~1/2R (1/2 is one half sign; R is registrade sign)

X1/4R (1/4 is one quarter; R is registrade sign)

and so on, instead of names.

The results from both connection string are different, but the same strings over and over.

I think this is far to difficult for me...

Best

Najaden

===========================================================

; Open a connection to the database

$objConn = ObjCreate("ADODB.Connection")

$objConn.Open ("Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq=asvmail.xls;" )

; $objConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=asvmail.xls;Extended Properties=""Excel 8.0;HDR=Yes""")

$strSQL = "SELECT * FROM [asvmail$] WHERE kl = 'A' ORDER BY nr ASC; "

$objRst = $objConn.execute($strSQL)

$i = 0

While $i <= 10

MsgBox(0, "test", $objRst("bestuurder"))

$i = $i + 1

WEnd

============================================================

[Ahmedmb]

have this error

Unknown function name.:

on line

$oConn = ObjCreate("ADODB.Connection")

PLZ help