Sign in to follow this  
Followers 0
najaden

Convert vbs ADODB connection to AutoIt

9 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


Ravenlark-----------------------------------------------------when you find yourself with the majority, its time to pause and reflect - Mark Twain

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

have this error

Unknown function name.:

on line

$oConn = ObjCreate("ADODB.Connection")

PLZ help

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
Sign in to follow this  
Followers 0