Sign in to follow this  
Followers 0
inote

How to use Real-time data in Excel

19 posts in this topic

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

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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)

Posted Image

but when i run by Autoit , the data is the last save (not real time)

Posted Image

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (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.

Posted Image

Edited by inote

Share this post


Link to post
Share on other sites

Could you please test with

$oExcel = _ExcelBookOpen($sFilePath1, 1)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Could you please test with

$oExcel = _ExcelBookOpen($sFilePath1, 1)

Nothing change

Posted Image

Share this post


Link to post
Share on other sites

#8 ·  Posted (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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (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

Posted Image

Here's sheet 1 but Sheet 2 doesn't change

Posted Image

Edited by inote

Share this post


Link to post
Share on other sites

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 here
RegExp tutorial: enough to get started
PCRE 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)

Share this post


Link to post
Share on other sites

Sorry, my bad. Remove "Call". Just do

_ExcelBookOpenEX($sFilePath1, 1)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Can't you just query the SQL engine directly yourself instead?

Yes :sweating:

Share this post


Link to post
Share on other sites

Sorry, my bad. Remove "Call". Just do

_ExcelBookOpenEX($sFilePath1, 1)

Posted Image

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Grrrr ... it's time the Excel UDF gets rewriten!

Then please try

_ExcelBookOpenEX($sFilePath1)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 ? :sweating:

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#18 ·  Posted (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 ? :sweating:

Edited by inote

Share this post


Link to post
Share on other sites

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 line

Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, Default, $bReadOnly, Default, $sPassword, $sWritePassword)
with
Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 3, $bReadOnly, Default, $sPassword, $sWritePassword)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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