Exel Files

[uznut]

How would i go about making autoit read and excel file then compare it to another excel file and then post all the information that is the same into another excel file?

im still a newb so i don't know were to start on this one

ty

tyler

[Blue_Drache]

How would i go about making autoit read and excel file then compare it to another excel file and then post all the information that is the same into another excel file?

im still a newb so i don't know were to start on this one

ty

tyler

<{POST_SNAPBACK}>

Until COM goes in, there's nothing we can do in AutoIt with Excel.

Suggestion? Visual Basic 6.0

[phillip123adams]

How would i go about making autoit read and excel file then compare it to another excel file and then post all the information that is the same into another excel file?

im still a newb so i don't know were to start on this one

<{POST_SNAPBACK}>

Here's a concept script that gathers data from 2 spreadsheets and places it in separate AutoIt variables. It is only a concept, and requires additional coding to make it practical, and possibly some tweaking of the sleep periods to ensure reliability.

For demonstration purposes, I am writing the data from the two spreadsheets to individual text files in order to display the data in notepad. In reality, the data is in the form of tab delimited strings which can be processed in AutoIt.

I did not do any code that actually differs the data, I only provided a call to a UDF to do so. The data I write to a 3rd spreadsheet is all fake, and is, again, intended only for concept purposes.

The script requires 3 spreadsheets to be opened, with titles being "Microsoft Excel - Book1", "Microsoft Excel - Book2", and "Microsoft Excel - Book3" (change as required). For demonstration purposes, it also requires Notepad in order to display the data from the first two spreadsheets.

Hopefully it will get you started.

expandcollapse popup

If WinExists("Microsoft Excel - Book1") And  WinExists("Microsoft Excel - Book2") And  WinExists("Microsoft Excel - Book3") Then
  ; Get data from book1
   Clipput("")
   WinActivate("Microsoft Excel - Book1")
   WinWaitActive("Microsoft Excel - Book1")
   Sleep(25)
   Send("{ESC}{ESC}^{HOME}")
   Sleep(25)
   Send("^a")
   Sleep(25)
   Send("^c")
   Sleep(100)
   $sExcel_1 = ClipGet()
  ;
  ; for demonstration, write data to a file and display it using notepad... 
   $hFile = FileOpen(@tempdir & "\Test-Excel-1.txt", 2)
   FileWrite($hFile, $sExcel_1)
   FileClose($hFile)
   sleep(100)
   run("notepad.exe " & @tempdir & "\Test-Excel-1.txt")
   sleep(100)

  ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
  ; Get data from book2
   Clipput("")
   WinActivate("Microsoft Excel - Book2")
   WinWaitActive("Microsoft Excel - Book2")
   Sleep(25)
   Send("{ESC}{ESC}^{HOME}")
   Sleep(25)
   Send("^a")
   Sleep(25)
   Send("^c")
   Sleep(100)
   $sExcel_2 = ClipGet()
  ;
  ; for demonstration, write data to a file and display it using notepad... 
   $hFile = FileOpen(@tempdir & "\Test-Excel-2.txt", 2)
   FileWrite($hFile, $sExcel_2)
   FileClose($hFile)
   sleep(100)
   run("notepad.exe " & @tempdir & "\Test-Excel-2.txt")
   sleep(100)
   
  ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
  ; Get the differences between the spreadsheets
   $sDiff = _Difference($sExcel_1, $sExcel_2)

  ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
  ; Write differences to a 3rd spreadsheet
   Sleep(100)
   WinActivate("Microsoft Excel - Book3")
   WinWaitActive("Microsoft Excel - Book3")
   Sleep(25)
   Send("{ESC}{ESC}^{HOME}")
   Sleep(25)
   Send("^a")
   Sleep(25)
   Send("^c")
   Sleep(100)
   Send("{DEL}")
   Sleep(100)
   Send("{ESC}{ESC}^{HOME}")
   Sleep(100)
  ; Send the new data to the 3rd spreadsheet.
   Send($SdIFF)
Else
   MsgBox(4096, "test", "These Windows must already exists: "&@lf&"Microsoft Excel - Book1"&@lf&"Microsoft Excel - Book2"&@lf&"Microsoft Excel - Book3")
EndIf
Exit


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Func _Difference($sExcel_1, $sExcel_2)
  ; Retrun the difference between data from 2 spreadsheets.
  ; The returned value is a tab delimited string.
  ;
  ; $sExcel_1 = Data from spreadsheet 1 (tab delimited)
  ; $sExcel_2 = Data from spreadsheet 2 (tab delimited)
  ;
;*********************************************************************************
;***** NEED TO COMPLETE CODING, THE FOLLOWING IS JUST TO CONCEPT THIS SCRIPT *****
;*********************************************************************************
   Local $sDiff1 = "DIFF_1"&@TAB&"DIFF_2"&@TAB&"DIFF_3"&@TAB&"DIFF_4"&@TAB&"DIFF_5"&@CR
   Local $sDiff2 = "D1"&@TAB&"D2"&@TAB&"D3"&@TAB&"D4"&@TAB&"D5"&@CR
   Local $sDiff3 = "D-2"&@TAB&"D-3"&@TAB&"D-4"&@TAB&""&@TAB&"D-5"&@CR
   Return $sDiff1 & $sDiff2 & $sDiff3
EndFunc;  _Difference

[bshoenhair]

The best and quickest way is to use Exel builtin VBA.

[Lazycat]

The best and quickest way is to use Exel builtin VBA.

Agree with this point - for now. But when COM support will be included, this probably will be not too many differences...

[zeroZshadow]

mm you can let exel output his sheets in .txt comma seppareted.

that would be read able for autoit.

don't know if it does it backwards

[bshoenhair]

Agree with this point - for now. But when COM support will be included, this probably will be not too many differences...

<{POST_SNAPBACK}>

I can hardly wait for Com support in AutoIt, but for this instance Com will never replace the simplicity and ease of the Excel builtin VBA, and all its built in features. Edited February 17, 2005 by bshoenhair

[Jaque]

I agree the easiest way would be to use VBA. Just use AutoIt to run the VBScript file.

Here is an example of a VBScript (.VBS) file which uses VBA.

File: TEST.VBS

==========

Option Explicit

Dim appExcel,xlsheet,xlbook

Set appExcel = CreateObject("Excel.Application")

Set xlbook = AppExcel.Workbooks.Add

Set xlsheet = xlbook.Worksheets(1)

xlsheet.Range("A1").Value="This is a test!"

xlsheet.Range("A2").Value="Test 2"

xlsheet.Range("A1").Interior.Color=RGB(255,255,0)

xlsheet.Range("A2").Interior.Color=RGB(255,154,0)

xlsheet.Cells(3,1).Value=1

xlsheet.Cells(4,1).Value=2

xlsheet.Columns("A").ColumnWidth=20

xlbook.activate

appExcel.visible = true

Set xlsheet = Nothing

Set xlbook = Nothing

Set appExcel = Nothing

Your AutoIt file may look like this:

======================

FileInstall("TEST.VBS",@TempDir & "\TEST.VBS",1)

RunWait("cscript.exe " & @TempDir & "\TEST.VBS","",@SW_HIDE)

FileDelete(@TempDir & "\TEST.VBS")

[Jaque]

Or would it be possible to convert this VBS script to an AutoIt script with COM support?

http://www.autoitscript.com/forum/index.ph...eateObject&st=0

That would be great!