How do I tell Excel to refresh data, before I copy data and email it, Tks.

[meatsack]

Here is a work in progress, adapted from others work. I am hoping to copy a few Excel data cells -then- Email the Excel data to my cell phone sms. Does anyone have any suggestions on how to make sure I get up-to-date Excel data. I would like to do it in the script. I have been hobboling along relying on xp to open the excel file with the os opening it daily then closing. Then before the closing the Excel file refreshes the data anytime the file is opened with an excel macro thats opened with the open macro. That doesn't seem to work every time or it hangs. Thanks for any suggestions.

expandcollapse popup

#Include <File.au3>
#include <Excel.au3>
#include <Array.au3>
#include <String.au3>
#include <IE.au3>HotKeySet("^.", "quit")
Local $s_FromName = 'mya'              ; From Name
Local $s_FromAddress = 'mya'            ; From @mail
Local $s_ToAddress = ''      ; To @mail
Local $s_Username = 'mya'              ; Gmail Username
Local $s_Password = ''            ; Gmail Password
Local $oFile = @ScriptDir & "\testz.xls"        ; Excel File
Local $vTime = 10                              ; First time to start!
While 1
    Sleep(5000)
    If @HOUR = $vTime Then
        gmailSMTP(excelUpdate())
        $vTime = 17
    EndIf
    If @HOUR = $vTime Then
        gmailSMTP(excelUpdate())
        $vTime = 10
    EndIf
WEnd
Func excelUpdate()
    Local $Excel = ObjCreate("Excel.Application")    $Excel.Visible = True
    $Excel.WorkBooks.Open($oFile, 2, False)
    $Excel.ActiveCell.QueryTable.Refresh
    $Excel.ActiveWorkBook.Saved = 1
    $Excel.Quit
    $oExcel = _ExcelBookOpen($oFile)
    $oRead = _ExcelReadSheetToArray($oExcel, 2, 1, 40)
    _ExcelBookClose($oExcel)    $output = ""
    For $r = 0 to UBound($oRead,1) - 1
        $output = $output & @CRLF
        For $c = 0 to UBound($oRead,2) - 1
            $output = $output & $oRead[$r][$c] & " "
        Next
    Next
    Return $output
EndFunc
Func gmailSMTP($vBody)
    ; SMTP part [ Without Web-based GUI ]    Local $s_SmtpServer = 'smtp.gmail.com'
    Local $s_IPPort = 465
    Local $s_Subject = 'Calender'
    Local $s_Body = $vBody
    Local $objEmail = ObjCreate("CDO.Message")
    $objEmail.From = $s_FromAddress
    $objEmail.To = $s_ToAddress
    $objEmail.Subject = $s_Subject
    $objEmail.TextBody = $s_Body    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $s_IPPort
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    $objEmail.Configuration.Fields.Update
    $objEmail.Send
    If @error Then
        MsgBox(32, "Error", "Error while sending")
        Exit
    EndIf
    $objEmail=""
    $output=""
EndFunc
Func quit()
    Exit
EndFunc

I've been hitting a wall on this one since 2010.

[Juvigy]

Try this:

$oExcel.ActiveWorkbook.RefreshAll

it should work.

[meatsack]

Ok, great Juvigy I will try it. This is going to be great if it works. Autoit to control the world, what do we need a girlfriend.UDF? #Include <Girlfriend.au3> , #Include <Boss.au3> I will post my results after I mess with the code tonight.

[meatsack]

Troubleshooting my script. I am not having much sucess. I tried 1. Putting it on Win7. The use 64x option failed on install. When I tried to edit my script it was full of gibberish every few characters. On second thought I reviewed it again and found that its corrupt even in the reg non 64 version, looks like it was just a corrupt .au3 script file not the application 2. Then I tried running it in Scite F5. It gave me the following warning: >C:\Program Files (x86)\AutoIt3\SciTE\..\au3check.exe "C:\Users\officelaptop\Desktop\email10.au3"

AutoIt3 Syntax Checker v1.54.8 Copyright © Tylo 2007

C:\Users\officelaptop\Desktop\email10.au3(21,20) : WARNING: $output: possibly used before declaration.

gmailSMTP($output)

~~~~~~~~~~~~~~~~~^

C:\Users\officelaptop\Desktop\email10.au3 - 0 error(s), 1 warning(s)

>Exit code: 1 Time: 0.362

I don't know how to correct this other than move the variable declaration $output higher up in the script. Update, I did that and no syntax error came up yet this message comes up >"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Users\officelaptop\Desktop\email10.au3"

Do I need something lower than IE8 to use AutoIt? DO most just use some other browser such as firefox? I even changed the "Sleep" value to lower thinking it may be taking time to run. 3. I use SciTE right to step though each script step one by one? I just use F5 or is there some other way of watching it run for troubleshooting purposes? Thanks in advance.

expandcollapse popup

#Include <File.au3>
#include <Excel.au3>
#include <Array.au3>
#include <String.au3>
#include <IE.au3>
HotKeySet("^.", "quit")Local $s_FromName = 'mxxxxx' ; From Name
Local $s_FromAddress = 'mxxxx@gmail.com' ; From @mail
Local $s_ToAddress = 'axxxxx@yahoo.com' ; To @mail
Local $s_Username = 'mxxxxxxx' ; Gmail Username
Local $s_Password = 'xxxxxxxxxxxx' ; Gmail Password
Local $oFile = @ScriptDir & "\test1.xls" ; Excel File
Local $vTime = 10  ; First time to start!
Local $output = ""
While 1
Sleep(10)
If @HOUR = $vTime Then
excelUpdate()
gmailSMTP($output)
$vTime = 17
EndIf
If @HOUR = $vTime Then
excelUpdate()
gmailSMTP($output)
$vTime = 10
EndIf
WEndFunc excelUpdate()
Local $Excel = ObjCreate("Excel.Application")
$Excel.Visible = True
$Excel.WorkBooks.Open($oFile, 2, False)
$Excel.ActiveCell.QueryTable.Refresh
$Excel.ActiveWorkBook.Saved = 1
$Excel.Quit$oExcel = _ExcelBookOpen($oFile)
$oExcel.ActiveWorkbook.RefreshAll
$oRead = _ExcelReadSheetToArray($oExcel, 2, 1, 40)
_ExcelBookClose($oExcel)
$output = ""
For $r = 0 to UBound($oRead,1) - 1
$output = $output & @CRLF
For $c = 0 to UBound($oRead,2) - 1
$output = $output & $oRead[$r][$c] & "="
Next
Next
Global $output
EndFuncFunc gmailSMTP($vBody)
; SMTP part [ Without Web-based GUI ]
Local $s_SmtpServer = 'smtp.gmail.com'
Local $s_IPPort = 465
Local $s_Subject = 'Calender'
Local $s_Body = $vBody
Local $objEmail = ObjCreate("CDO.Message")$objEmail.From = $s_FromAddress
$objEmail.To = $s_ToAddress
$objEmail.Subject = $s_Subject
$objEmail.TextBody = $s_Body
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $s_IPPort
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
$objEmail.Configuration.Fields.Update$objEmail.Send
If @error Then
MsgBox(32, "Error", "Error while sending")
Exit
EndIf
$objEmail=""
$output=""
EndFunc
Func quit()
Exit
EndFunc

[meatsack]

I am fairly certain my AutoIt application is running fine on my Win7 as I am able to start up, step through, and troubleshoot the sudoku script I found in the example section of this forum.

[meatsack]

I take it I need to do everything with the more dominant applications, like its easier to support AutoIt users using IE, MS Word, MS Exce and harder to support Chrome, Firefox, LibreOffice? I purused the UDF's and they don't seem to support the later, no?

[Juvigy]

There is an FIrefox udf. For chrome i dont know...