Excel file with AutoITScript

[Ilounah]

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.

 

Edited June 15, 2018 by Daniza

[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.

[Ilounah]

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 June 16, 2018 by Daniza

[water]

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

[Ilounah]

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

Edited June 16, 2018 by Daniza

[water]

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 June 16, 2018 by water
Fixed typo

[Ilounah]

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 June 16, 2018 by Daniza

[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?

[Ilounah]

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 June 16, 2018 by Daniza

[water]

Glad you got it working