Open Hiden Excel and stop running

[Sucrilhus]

Hi there,

I'm new here, I'm really used to program on VBA, and trying to get used with Autoit.

I created an Workbook that hides itself when I open it, and shows only the a userform.

But it flashes the entire excel background for a second when I'm opening it, that's why I'm turning to Autoit.

I wrote this...

"#include <Excel.au3>

$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"

$oExcel = _ExcelBookOpen($sFilePath1,0)"

It works perfectly on the opening process, that is... only the userform shows up.

However when I close my WorkBook, the following error appears...

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (194) : ==> Variable must be of type "Object".:

For $i = 1 To .ActiveWorkbook.Sheets.Count

For $i = 1 To .ActiveWorkbook^ ERROR"

I just want the Autoit program to open the workbook and stop running.

How do I do that?

Thanks in advance

>>>> Edit:

I've already tried using

"ShellExecute( ...... , @SW_HIDE) "

But the Excel's background still shows up on the screen...

Edited May 16, 2010 by Sucrilhus

[l3ill]

Your script works fine on my PC (script closes after excel open). I'm thinking there must be some issue with your VBS userform window that is causing AutoIT to stall.

try disabling it for testing to see if you get the same error.

[Sucrilhus]

Billo, Thanks for the reply

You are right!

Without the activation of the userform, my autoit program closes just fine

Is there a way to run worksheet's macros using autoit?

I've tryed putting a hot key on my macro that calls the Userform "Ctrl + Shift + F"

and wrote 2 extra lines on the autoit program....

Send("{Lctrl down}" + "{LShift down}" + "{f}")

Send("{Lctrl up}" + "{LShift up}")

But it didn't work...

[somdcomputerguy]

Try

Send("^+f")

instead of

Send("{Lctrl down}" + "{LShift down}" + "{f}")
Send("{Lctrl up}" + "{LShift up}")

[l3ill]

Strange...thats how I ended up getting mine to work.

try this hotkey {Ctrl & F} instead and use the syntax like this:

Send("^f")

[Sucrilhus]

Thank you both,

"Send("^+f")"

Works just fine when I open the workbook not hidden

I mean... by doing this...

#include <Excel.au3>    
$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"
$oExcel = _ExcelBookOpen($sFilePath1,1) ;Shows the application
Send("^+f")

... the program opens my workbook and run the macro perfectly...

But when I put to open in hidden state...

#include <Excel.au3>    
$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"
$oExcel = _ExcelBookOpen($sFilePath1,0) ;Hides the application
Send("^+f")

... it opens the workbook but doesn't run the macro...

I concluded that because I've made the following test....

The macro assigned "^+f" just writes on cell A1 "I was here" , which was previously empty

Even after running the following program

#include <Excel.au3>    
$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"
$oExcel = _ExcelBookOpen($sFilePath1,0)
Send("^+f")
_ExcelBookClose($oExcel) ;Closes Saving changes

...cell A1 remained empty.

[somdcomputerguy]

Try ControlSend to send to a hidden (IOW, non-focused) app.

You can use AutoIt Window Info Tool (AU3Info) to get info needed for ControlSend.

Edited May 16, 2010 by somdcomputerguy

[Sucrilhus]

somdcomputerguy,

It's weird, but again , the same problem remains....

#include <Excel.au3>    
$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"
$oExcel = _ExcelBookOpen($sFilePath1,1)
ControlSend("Microsoft Excel - My_WorkBook.xlsm","My_WorkBook.xlsm","","^+d")
_ExcelBookClose($oExcel)

Opens my Workbook, activate the macro, save and close

but..

#include <Excel.au3>    
$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"
$oExcel = _ExcelBookOpen($sFilePath1,0)
ControlSend("Microsoft Excel - My_WorkBook.xlsm","My_WorkBook.xlsm","","^+d")
_ExcelBookClose($oExcel)

Either doesn't run the macro... or doesn't save the changes....

>>> Edit:

Is there a way to Stop Screen Update for a certain time, like a second?

Therefore I could Freeze Screen Updating... so that short flash of excel's background wouldn't be displayed....

Edited May 16, 2010 by Sucrilhus

[somdcomputerguy]

Hmm.. I'm afraid I won't be able to offer any detailed suggestions as I don't have Excel on my PC. Sorry, but good luck..

[Sucrilhus]

No problem,

Thanks a lot for helping me,

I really appreciated it

[somdcomputerguy]

Thanks a lot for helping me,

Happy to help what little I could man.

[Juvigy]

You can run the macro like this :

#include <Excel.au3>    
$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"
$oExcel = _ExcelBookOpen($sFilePath1,0)
$oExcel.Run("Macro1")
$oExcel.ActiveWorkbook.Save
$oExcel.ActiveWorkbook.Close

[Sucrilhus]

You can run the macro like this :

#include <Excel.au3>    
$sFilePath1 = @ScriptDir & "\My_WorkBook.xlsm"
$oExcel = _ExcelBookOpen($sFilePath1,0)
$oExcel.Run("Macro1")
$oExcel.ActiveWorkbook.Save
$oExcel.ActiveWorkbook.Close

Woah Juvigy! ^^

Thanks a lot!

Not only you've solved my problem but also showed me another way to run macros.

Thanks, everything is running just fine now.