inote Posted April 23, 2013 Share Posted April 23, 2013 i use this code to open Excel and read data to clipboard but my excel is linked to SQL sever (refresh 1 min) when i use the code to open and get data , it is'not real time data (that's last save) $sFilePath1 = @ScriptDir & "\TEP.HD#1.xlsx" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1,@SW_MAXIMIZE) _ExcelSheetActivate($oExcel, 2) _ExcelBookSave($oExcel) _ExcelBookSaveAs($oExcel, @ScriptDir &"\TEP.HD#1.xlsx","xlsx",0,1) so i use the script to open and overwrite it , but the result is the same !! How can i get the real time data Link to comment Share on other sites More sharing options...
water Posted April 23, 2013 Share Posted April 23, 2013 Does the Excel file access and query the SQL server right now? Do you just need to refresh the query? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
inote Posted April 23, 2013 Author Share Posted April 23, 2013 Does the Excel file access and query the SQL server right now? Do you just need to refresh the query?i do , my data in excel is refreshed Let' s see (STATUS in ID=1 and ID=2 is normal) but when i run by Autoit , the data is the last save (not real time) Link to comment Share on other sites More sharing options...
water Posted April 23, 2013 Share Posted April 23, 2013 If you manually open the Excel worksheet do you get a popup asking if linked data should be refreshed? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
inote Posted April 23, 2013 Author Share Posted April 23, 2013 (edited) If you manually open the Excel worksheet do you get a popup asking if linked data should be refreshed?No but if i manually close i'll get popup asking " Do u want to save ?"and here's my Excel connection config. Edited April 23, 2013 by inote Link to comment Share on other sites More sharing options...
water Posted April 23, 2013 Share Posted April 23, 2013 Could you please test with $oExcel = _ExcelBookOpen($sFilePath1, 1) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
inote Posted April 23, 2013 Author Share Posted April 23, 2013 Could you please test with $oExcel = _ExcelBookOpen($sFilePath1, 1) Nothing change Link to comment Share on other sites More sharing options...
water Posted April 23, 2013 Share Posted April 23, 2013 (edited) Another try: Call _ExcelBookOpenEX($sFilePath1, 1) Func _ExcelBookOpenEX($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "") Local $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $fVisible > 1 Then $fVisible = 1 If $fVisible < 0 Then $fVisible = 0 If $fReadOnly > 1 Then $fReadOnly = 1 If $fReadOnly < 0 Then $fReadOnly = 0 With $oExcel .Visible = $fVisible If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly, Default, $sPassword, $sWritePassword) If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly, Default, Default, $sWritePassword) If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly, Default, $sPassword, Default) If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly) ; Select the first *visible* worksheet. For $i = 1 To .ActiveWorkbook.Sheets.Count If .ActiveWorkbook.Sheets($i).Visible = $xlSheetVisible Then .ActiveWorkbook.Sheets($i).Select() ExitLoop EndIf Next EndWith Return $oExcel EndFunc ;==>_ExcelBookOpenEX Edited April 23, 2013 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
inote Posted April 23, 2013 Author Share Posted April 23, 2013 (edited) Another try: Call _ExcelBookOpenEX($sFilePath1, 1) Func _ExcelBookOpenEX($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "") Local $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $fVisible > 1 Then $fVisible = 1 If $fVisible < 0 Then $fVisible = 0 If $fReadOnly > 1 Then $fReadOnly = 1 If $fReadOnly < 0 Then $fReadOnly = 0 With $oExcel .Visible = $fVisible If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly, Default, $sPassword, $sWritePassword) If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly, Default, Default, $sWritePassword) If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly, Default, $sPassword, Default) If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, 3, $fReadOnly) ; Select the first *visible* worksheet. For $i = 1 To .ActiveWorkbook.Sheets.Count If .ActiveWorkbook.Sheets($i).Visible = $xlSheetVisible Then .ActiveWorkbook.Sheets($i).Select() ExitLoop EndIf Next EndWith Return $oExcel EndFunc ;==>_ExcelBookOpenEX Here's sheet 1 but Sheet 2 doesn't change Edited April 23, 2013 by inote Link to comment Share on other sites More sharing options...
jchd Posted April 23, 2013 Share Posted April 23, 2013 Can't you just query the SQL engine directly yourself instead? This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
water Posted April 23, 2013 Share Posted April 23, 2013 Sorry, my bad. Remove "Call". Just do _ExcelBookOpenEX($sFilePath1, 1) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
inote Posted April 23, 2013 Author Share Posted April 23, 2013 Can't you just query the SQL engine directly yourself instead?Yes Link to comment Share on other sites More sharing options...
inote Posted April 23, 2013 Author Share Posted April 23, 2013 Sorry, my bad. Remove "Call". Just do _ExcelBookOpenEX($sFilePath1, 1) Link to comment Share on other sites More sharing options...
water Posted April 23, 2013 Share Posted April 23, 2013 I would be very interested to solve this problem with AutoIt. I'm about to rewrite the Excel UDF and I think everything you can do manually should be possible to do with AutoIt. I think it is just a problem with the parameters for the Workbooks.Open method. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted April 23, 2013 Share Posted April 23, 2013 Grrrr ... it's time the Excel UDF gets rewriten! Then please try _ExcelBookOpenEX($sFilePath1) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
inote Posted April 24, 2013 Author Share Posted April 24, 2013 Grrrr ... it's time the Excel UDF gets rewriten! Then please try _ExcelBookOpenEX($sFilePath1) How can i get the rewritten Excel UDF and how to use it ? Link to comment Share on other sites More sharing options...
water Posted April 24, 2013 Share Posted April 24, 2013 For download please check my signature. Please be aware that the ExcelEX UDF is in an early alpha state! The functions are similar to the Excel UDF and come with help files and examples. The function prefix changed from _Excel to _Excel_ (note the underscore) and some function names have changed. If you have questions I will be glad to help. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
inote Posted April 25, 2013 Author Share Posted April 25, 2013 (edited) For download please check my signature. Please be aware that the ExcelEX UDF is in an early alpha state!The functions are similar to the Excel UDF and come with help files and examples. The function prefix changed from _Excel to _Excel_ (note the underscore) and some function names have changed.If you have questions I will be glad to help.Thx but i just download ExcelEx at ur Signature then Extract it, and put_ExcelBookOpenEX($sFilePath1)replace _ExcelBookOpen($sFilePath1) on my code isn't it ? Edited April 25, 2013 by inote Link to comment Share on other sites More sharing options...
water Posted April 25, 2013 Share Posted April 25, 2013 The ExcelEX UDF is complete different comapred to the "old" Excel UDF. Example: Every script has to start with _Excel_Open. Please have a look at the example scripts (e.g. _Excel_BookOpen) that come with the UDF. Nevertheless function _Excel_BookOpen has to be modified too. Replace lineLocal $oWorkbook = $oExcel.Workbooks.Open($sFilePath, Default, $bReadOnly, Default, $sPassword, $sWritePassword)withLocal $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 3, $bReadOnly, Default, $sPassword, $sWritePassword) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki 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