Sign in to follow this  
Followers 0
JBeardNC

Capture filename being opened in Excel

6 posts in this topic

I need some help with capturing the filename that is being opened (if its even possible). Quick background, we are finding that our antivirus software is causing Excel to open very slow. The reason is when Excel is opened, there are some .TMP files that get placed in the temp folder and the antivirus is scanning these files before Excel will open resulting in a minimum 8-10 second delay of opening Excel. If we change the temp folder location to some other folder that antivirus doesnt scan, then Excel opens in a flash. So we have a situation where when Excel is opened, either by launching the program manually or by double clicking on an .xls file, we need to change the file location for temporary files to a folder that is not being scanned by antivirus and then allow Excel to open. After Excel opens, we then need to change the temp file location back to normal (due to security risks, we don't want to permanently keep the temporary file location assigned to a folder that isnt being scanned). So what I've done is created a script that will sit in the background on the machine and monitor for the EXCEL.EXE executable. When it detects its been opened, it'll close it and set the TEMP environment variable to C:\temp. Then it will open Excel and then change the TEMP environment variable back to its original location (%userprofile%\AppData\Local\Temp). This all happens very quickly and you can't even tell that something is happening behind the scenes. Only problem is, it works if you open Excel by a shortcut or by launching EXCEL.EXE by itself but when you launch Excel by double clicking on an .xls file, it opens Excel but the .xls file doesnt get opened because the filename argument doesn't get passed on to EXCEL.EXE in the script. This is where I need help. Is there a way in my script to determine which .xls file is being opened when a user double-clicks on it so that I can pass that along to EXCEL.EXE? Heres the code I'm using. Very simple... just need to figure out how to pass along the original argument so that Excel opens the file that the user chose. Any ideas? The more I think about it, I am not sure its possible to capture the filename so if you can think of a better way to accomplish this, I'm all ears.

While 1
    sleep(100)
    If ProcessExists ("EXCEL.EXE") Then
        ProcessClose ("EXCEL.EXE")

        ENVSET("TEMP", "C:\Temp")
        Run ("EXCEL.EXE " & <filename argument would be here>)
        EnvSet("TEMP", "%USERPROFILE%\APPDATA\LOCAL\TEMP")
    EndIf
WEnd

Share this post


Link to post
Share on other sites



Any ideas? The more I think about it, I am not sure its possible to capture the filename so if you can think of a better way to accomplish this, I'm all ears.

Why not get the users to select the file first?

$message = "Select your WorkBook"
$var = FileOpenDialog($message, @UserProfileDir & "\Excel", "Sheets (*.xls;*.xl*)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
Else
    $var = StringReplace($var, "|", @CRLF)
    MsgBox(4096,"","You chose " & $var)
EndIf

Share this post


Link to post
Share on other sites

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
                 MsgBox(0,"",$element.FullName)
           Next
$oExcel.Application.Quit

Something like this ?

Share this post


Link to post
Share on other sites

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
                 MsgBox(0,"",$element.FullName)
           Next
$oExcel.Application.Quit

Something like this ?

Thats not quite working the way I need it but I think you're helping me to get on the right track. Below is the code I'm using. I commented out the temp folder modification just for testing. If I launch the script and then try and open Excel by double-clicking on an .xls file, I get an AutoIt error that says "Error: The requested action with this object has failed". However, when I already have Excel opened and then I launch the script, it'll give me the name of the file, which is great, but then when I click OK, it gives me the same error. The error states that its happening on line 9 which is the For $element In $oExcel.Application.Workbooks line. Any ideas?

While 1
    sleep(100)
    If ProcessExists ("EXCEL.EXE") Then
        $oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
                 MsgBox(0,"",$element.FullName)
           Next
        $oExcel.Application.Quit

;~      ENVSET("TEMP", "C:\WINDOWS\System32\Temp")
;~      ENVSET("TMP", "C:\WINDOWS\System32\Temp")
;~      Run ("EXCEL.EXE " & $CmdLineRaw)
;~      EnvSet("TEMP", "%USERPROFILE%\APPDATA\LOCAL\TEMP")
;~      EnvSet("TMP", "%USERPROFILE%\APPDATA\LOCAL\TEMP")
    EndIf
WEnd

Share this post


Link to post
Share on other sites

You are supposed to have a excel file open. Then launch the script.

It works great for me. For testing purposes create a test script with only the lines i

have suggested and open one or more excel files. Then start the script.

All this works for me - should work for you too. Does your excel open each files in a new

taskbar window or opens several files in the same excel window?

Share this post


Link to post
Share on other sites

If I launch the script and then try and open Excel by double-clicking on an .xls file, I get an AutoIt error that says "Error: The requested action with this object has failed".

I slightly adjusted the code to force a valid object.

BUT you are going to have issues with multiple sessions and multiple files.

While 1
    Sleep(10)
    If ProcessExists("EXCEL.EXE") Then
        RunWait(@ComSpec & ' /c cd', '')
        $oExcel = ObjGet("", "Excel.Application")
        While Not IsObj($oExcel)
            $oExcel = ObjGet("", "Excel.Application")
            Sleep(10)
        WEnd
        If @error Then
            MsgBox(0, "ExcelTest", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8))
        EndIf
        $oExcel.Visible = 1
        For $element In $oExcel.Application.Workbooks
            MsgBox(0, "", $element.FullName)
        Next
        $PID = ProcessExists("EXCEL.EXE")
        ;MsgBox(0, '', 'If ' & $PID, 1)
        If $PID Then ProcessClose($PID)
        EnvSet("TMP", "C:\WINDOWS\System32\Temp");<<< ==== This is just for AutoIT environment  see Help File
        ;You need either SetX  or  do a Registry change
        Run("EXCEL.EXE " & $element.FullName)
        ;$oExcel.Application.Quit
    EndIf
WEnd

Remember this wil keep on running while it finds the Excel.exe process!


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