Sign in to follow this  
Followers 0
uznut

Exel Files

9 posts in this topic

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

Share this post


Link to post
Share on other sites



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


Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache

Share this post


Link to post
Share on other sites

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.

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

Phillip

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


*If u thought life couldn't get worse, u meet me *<guy> What would you give my little sister to unzip ?<friend> 10 bucks<guy> No, i mean like Winzip...

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by bshoenhair

Share this post


Link to post
Share on other sites

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")

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