Jump to content
Sign in to follow this  
Malkavian99

Autoit program using Excel

Recommended Posts

Malkavian99

Here is what I have done and I'm not sure how to correct it or stop it.

I have a program that searches drives for particular files and puts them into an Excel spreadsheet. The program starts, opens excel in the background, basically Excel is running as a process but I don't see it on the screen. The program does it's scanning and writes the data to the excel spreadsheet after scanning each drive. Once all drives are scanned it closes the excel file, saves it and kills the Excel process.

If during the scan someone opens the Excel program by double clicking the program icon and a blank sheet appears it actually opens a second Excel process under task manager. This is fine. The program is writing to the one you cannot see and you can see the second one. You can then open an excel file and it still doesn't disturb the first Excel program running.

BUT, you know there always is one..

If during the scan someone opens an Excel file, by double clicking the Excel file itself, it opens the file within the first Excel process in another window/book. This disrupts the program. I know this is a feature of Excel and I'm wondering if there is a way to basically lock the Excel session Autoit opens so that if someone were to double click a file it would not use the first Excel Session and open a second Excel session?

I'm using Excel.au3 to get my excel functions.

Any thoughts would be appreciated. Thanks

Michael

Share this post


Link to post
Share on other sites
czardas

Don't write to the spread sheet until you have collected all the data, or don't write to Excel, but write to a csv file which will open in Excel: so you can still use the program while the script is running. Just a suggestion.

Edited by czardas

Share this post


Link to post
Share on other sites
Malkavian99

Thanks for the information. Here is the problem with holding the information until the end. I have approximately 200 drives to scan and holding the data in an array could get really large.

Michael

Share this post


Link to post
Share on other sites
jchd

Have a look at SQLite functions in AutoIt helpfile. SQLite is a database engine which will hold as much data you need safely and with convenient SQL interface. The DB can be temporary (memory) or permanent (storage-based) and can be accessed by any number of processes.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Malkavian99

I have found the answer or at least the answer that works for my situation.

After opening Excel with with the following line (referencing the Excel.au3 module):

$FF_Excel = _ExcelBookNew(0)

The Excel Object reference is returned to the $FF_Excel variable and the Excel spreadsheet is not viewable by the user, basically running as a hidden process.

I added the following line in my code:

$FF_Excel.Application.IgnoreRemoteRequests = True

This actually changes a parameter in the Excel program. When I double clicked an Excel file to open it a new Excel process was started and that process closed when the second file was closed. My program's Excel process was untouched.

If left as true and the program closes, the user is unable to open Excel files by double clicking the file itself. So I added the following line after the _ExcelBookClose($FF_Excel, 0, 0) line.

$FF_Excel.Application.IgnoreRemoteRequests = False

This set the parameter back in Excel so the user can open Excel file by double clicking the file.

I originally thought I was going to need to modify the Excel.au3 functions, but found that I was able to reference the Excel object directly from my code.

This would be a nice parameter to add in the Excel.au3 function when opening. Default as false, but sometimes you want a little more control over your users and you can set it to true. If anyone knows how to contact to possibly have this added I would like to know.

Thanks

Michael

Edited by Malkavian99

Share this post


Link to post
Share on other sites
Juvigy

I think there was a setting in excel of opening every new file by double clicking as a separate process.Here it is:

My Computer

Tools

Folder Options

File Types

Choose XLS

Go to Advanced

Uncheck "browse in same window" in advanced window.

Then highlight Open

Edit

Make sure in the Action box it says &Open

Make sure in the application used to perform action it says:

"C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE" "%1"

Check the box next to use DDE

Remove anything that is in DDE Message box and DDE Application Not Running box.

Make sure the application box says: EXCEL

And in the Topic box it says: System

Although the .IgnoreRemoteRequests seems better solution.

Share this post


Link to post
Share on other sites
Malkavian99

Since I would not have access to everyone's workstation the .IgnoreRemoteRequests is the best option for me in my situation. You just have to remember to set it back to False before closing the Excel Object. If you don't and they try to open an Excel file by double clicking it, they will receive an error.

Michael

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  

  • Similar Content

    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
×