Jump to content
Daniza

Excel file with AutoITScript

Recommended Posts

Daniza
Posted (edited)

Hello, Good Day Guys ^_^

I made a simple stock request for our restaurant store and I want to use AutoIT before opening the file, can I compile them together so I can hide my excel file from the user?

Thaks to 232showtime for helping me using vlookup I appreciate it. :P

 

Edited by Daniza

Share this post


Link to post
Share on other sites
JLogan3o13

Look at FileInstall to include the excel file with the script. When the script runs you can output to a temp directory, then delete when finished.

  • Like 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Daniza
Posted (edited)
5 hours ago, JLogan3o13 said:

Look at FileInstall to include the excel file with the script. When the script runs you can output to a temp directory, then delete when finished.

FileInstall("C:\Documents and Settings\MPC\Desktop\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm", @TempDir & "\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm", 1)

$FileName =  @TempDir & "\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm"
$oExcelDoc = ObjCreate("Excel.Application")
$oExcelDoc.AutomationSecurity = 1 
$oExcelDoc.Visible = 1 
$oExcelDoc.WorkBooks.Open($FileName)

Thanks Sir @JLogan3o13 for FileInstall, I try to search and I come up with this, open file then make sure Macro is enable.

My problem was how to delete the File from the TempDir after they close the File.

I tried to make a  Workbook_BeforeClose event on Excel but does'nt work (Maybe because cannot delete a open file)

Sub Delete()
On Error Resume Next
aFile = Environ("temp") & "\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm"
Kill aFile
' Make sure it actually got deleted.
End Sub

Any suggestion Thanks ^_^

Edited by Daniza

Share this post


Link to post
Share on other sites
water

You could use the AutoIt script to wait for this event and then delete the Excel file.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Daniza
Posted (edited)

@water Using While 1..wend then wait check TaskManager if Winexist if not then delete the file from TempDir?

Edited by Daniza

Share this post


Link to post
Share on other sites
water
Posted (edited)

As you use AutoIt to open the Excel workbook you could use the returned object to wait for the close event (untested):

#include <Excel.au3>

$sFileName =  @TempDir & "\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm"
FileInstall("C:\Documents and Settings\MPC\Desktop\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm", $sFileName, 1)
$oExcel = _Excel_Open(False) ; Open Excel invisible
$oExcel.AutomationSecurity = 1 
$oWorkbook = _Excel_BookOpen($oExcel, $sFileName)
ObjEvent($oWorkbook, "Workbook_")
$bExit = False
While 1 ; Wait until the workbook gets closed
    Sleep(10)
    If $bExit Then 
        Sleep(500) ; Make sure that Excel has closed
        FileDelete($sFileName)
        Exit
    EndIf
WEnd 

Func WorkBook_BeforeClose($bCancel)
    $bExit = True
EndFunc

 

Edited by water
Fixed typo
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Daniza
Posted (edited)

Hello, @water thanks for the Script you made i changed it a little bit Excel_Opn to Excel Open then False to True it Works fine, but I see one problem after file opened if you activate some stuff like notedpad or use alt+tab file excel will stop working and you need to force close it on task manager.

It works fine if you do open>choose form>click, then your free to shift to other stuffs but if you do is open> shift to other stuff then try to go back excel then issue come up. Sorry for my bad English.

 

Edited by Daniza

Share this post


Link to post
Share on other sites
water

I can't imagine that the problem gets caused by the AutoIt script as it does not automate the GUI (and hence might interfere with other programs).
Can you test with another Excel workbook that dos not run a macro?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Daniza
Posted (edited)

Hello @water Upon Observing the code really work's but in a invincible mode, I try to tweak the code put

$oExcel = _Excel_Open(True)

After _Excel_BookOpen then it fix the issue :)

#include <Excel.au3>

$sFileName =  @TempDir & "\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm"
FileInstall("C:\Documents and Settings\MPC\Desktop\[UPDATED]Standard Stock Requistion 06122018 - 06152018.xlsm", $sFileName, 1)
$oExcel = _Excel_Open(False) ; Open Excel invisible
$oExcel.AutomationSecurity = 1
$oWorkbook = _Excel_BookOpen($oExcel, $sFileName)
$oExcel = _Excel_Open(True)
ObjEvent($oWorkbook, "Workbook_")
$bExit = False

While 1 ; Wait until the workbook gets closed
    Sleep(10)
    If $bExit Then
        Sleep(500) ; Make sure that Excel has closed
        FileDelete($sFileName)
        Exit
    EndIf
WEnd

Func WorkBook_BeforeClose($bCancel)
    $bExit = True
EndFunc

 

Thank you for your time @water ;)

Closed..

Edited by Daniza

Share this post


Link to post
Share on other sites
water

Glad you got it working :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

×