najaden Posted June 21, 2006 Share Posted June 21, 2006 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 Link to comment Share on other sites More sharing options...
randallc Posted June 21, 2006 Share Posted June 21, 2006 (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 June 21, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
najaden Posted June 21, 2006 Author Share Posted June 21, 2006 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 Link to comment Share on other sites More sharing options...
najaden Posted June 21, 2006 Author Share Posted June 21, 2006 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 makesthe final results covering 6 classes, mix- and ladies entries, 3 kind of teams, 22 differentcar 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 Link to comment Share on other sites More sharing options...
Ravenlark Posted June 21, 2006 Share Posted June 21, 2006 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@NajadenYes, 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 Link to comment Share on other sites More sharing options...
randallc Posted June 21, 2006 Share Posted June 21, 2006 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
randallc Posted June 21, 2006 Share Posted June 21, 2006 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
najaden Posted June 22, 2006 Author Share Posted June 22, 2006 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 ============================================================ Link to comment Share on other sites More sharing options...
Ahmedmb Posted June 27, 2006 Share Posted June 27, 2006 have this error Unknown function name.: on line $oConn = ObjCreate("ADODB.Connection") PLZ help Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now