padinski

Compare 2 excel spreadsheets if amatch is found copy certain cells back the original workbook

4 posts in this topic

#1 ·  Posted (edited)

Hi all,

As the title suggests. I have 2 spreadsheets with different information in both, however their is 1 common value to link the 2 spreadsheets together.

What I want to do is have everything from column B in an array from workbook1, the script would loop through the array searching for that exact value in workbook2 in column A. If that value is found in workbook2, I need to copy information from 2 different cells that relate to that record from workbook2 back into workbook1.

This is what I have so far but, just can't get the copying it the cell values from workbook2 into woorkbook 1.

;-----------------------------------------------------------------------------
; Select filepath with message to display in FileOpenDialog.
;-----------------------------------------------------------------------------
Local Const $sMessage = "Select workbook filepath."
Local $sFilePath = FileOpenDialog($sMessage, "D:\Users\xxxxx\Documents\", "Excel (*.xls;*.xlsx;*.csv)|", $FD_FILEMUSTEXIST)
;-----------------------------------------------------------------------------
; Create application object or connect to an already running Excel instance
;-----------------------------------------------------------------------------
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;-----------------------------------------------------------------------------
;open workbook1
;-----------------------------------------------------------------------------
$oWorkbook = _Excel_BookOpen($oAppl, $sFilepath, Default, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error opening '" & $oWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;-----------------------------------------------------------------------------
; Select filepath with message to display in FileOpenDialog.
;-----------------------------------------------------------------------------
Local Const $sMessage2 = "Select workbook2 filepath."
Local $sFilePath2 = FileOpenDialog($sMessage2, "D:\Users\xxxxx\Documents\", "Excel (*.xls;*.xlsx;*.csv)|", $FD_FILEMUSTEXIST)
;-----------------------------------------------------------------------------
;open workbook2
;-----------------------------------------------------------------------------
$oWorkbook2 = _Excel_BookOpen($oAppl, $sFilepath2, Default, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error opening '" & $oWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;-----------------------------------------------------------------------------
;Read array from workbook
;-----------------------------------------------------------------------------
Local $aArray1 = _Excel_RangeRead($oWorkbook, Default)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;-----------------------------------------------------------------------------
; Turn on progress GUI
;-----------------------------------------------------------------------------
ProgressOn("Progress", "data washing progress", "0%")
;-----------------------------------------------------------------------------
; counts the number of rows in the array
;-----------------------------------------------------------------------------
$rows = UBound($aArray1) -1
;-----------------------------------------------------------------------------
; script start
;-----------------------------------------------------------------------------
for $c = 1 to $rows
;-----------------------------------------------------------------------------
; Progress calcualtions
;-----------------------------------------------------------------------------
$p=($c-1)/$rows*100
ProgressSet($p,$c-1&" of "&$rows&" records processed")
;-----------------------------------------------------------------------------
; Find all id's as per value in column B from workbook in workbook2
;-----------------------------------------------------------------------------
_Excel_FilterSet($oWorkbook2, Default, Default, 1, ($aArray1[$c][1]))
If @error Then
ContinueLoop
EndIf
_Excel_RangeCopyPaste($oWorkbook2.Worksheets(1), "AM:AN")
_Excel_FilterSet($oWorkbook, Default, Default, 2, ($aArray1[$c][1]))
_Excel_RangeCopyPaste($oWorkbook.Activesheet, Default, "P", Default, $xlPasteValues)
next

Anyone able to see where I have gone wrong or can suggest a better approach to do this?

Edited by padinski

Share this post


Link to post
Share on other sites



Do you get an error or does it just not give the desired result?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Why do you write this in autoit and not in vba macro. Much easier and much quicker.

 

 

Share this post


Link to post
Share on other sites

thanks for taking a look guys, just not the desired result Water.
Junkew good point.

FYI:  I ended up just combining the two workbooks into one and having the data on 2 worksheets, just using the following array formula in excel:

In cell P2 in Sheet1
=INDEX('Sheet2'!AM:AM, MATCH(B2, 'Sheet2'!A:A, 0))

In cell Q2 in Sheet1
=INDEX('Sheet2'!AN:AN, MATCH(B2, 'Sheet2'!A:A, 0))

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

  • Similar Content

    • Virgilio1
      By Virgilio1
      Salve Amici,
      in un mio progetto vorrei utilizzare una dll per creare dei file excel senza utilizzare l'applicativo Excel.
      Ora dalla versione 2007 di Office la Microsoft utilizza per i file un nuovo formato aperto "Microsoft Open XML format".
      in PHP questo è molto semplice utilizzando la Libreria PHPExcel (http://www.codeplex.com/PHPExcel)
      Cercando in rete ho trovato una dll che dovrebbe fare lo stesso (https://code.google.com/archive/p/excellibrary/) ma non ho assolutamente le capacita di integrare la Dll in autoit, qualcuno mi può aiutare ?
      Sarebbe veramente molto efficiente poter creare e manipolare file excel in autoit senza dover caricare in memoria l'applicativo Excel.
      Grazie
      -.-.-.-.-.
      Hello friends,
      in my project I want to use a dll to create the excel file without using the Excel application.
      Now from the Microsoft Office 2007 version uses for the files a new open format "Microsoft Open XML format".
      PHP This is very simple using the Library PHPExcel (http://www.codeplex.com/PHPExcel)
      Searching the net I found a dll that should do the same (https://code.google.com/archive/p/excellibrary/) but I have absolutely the ability to integrate the .dll in autoit, anyone can help me?
      It would really be very efficient to create and manipulate Excel files into memory autoit without having to load the Excel application.
      Thank you
    • Sergy
      By Sergy
      I have price-list in xls. When I open it by _Excel_Open and _Excel_BookOpen and after that close by _Excel_Close, I have a message about "Save changes?"
      I try open it in read-only mode, but no changes made. I still see annoing message.
      Windows 10 prof, MS Office 2007.
       
      #include <Excel.au3> #include <MsgBoxConstants.au3> ConsoleWrite(@AutoItVersion) Global $sPriceFile = @ScriptDir&"\opt_pr_list-mini.xls" Local $oExcel = _Excel_Open(False, False, False, True)  If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sPriceFile, True )     ; readonly If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error opening workbook @error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel)     Exit EndIf _Excel_Close($oExcel, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 2", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Exit What I do wrong?
      Thanks.
      opt_pr_list-mini.xls.zip
    • StillLearningThisStuff
      By StillLearningThisStuff
      Hello all,
      Summary: I have a basic piece of code that is to be a part of a much larger project; I just can't seem to get the right output. I'm retrieving two lots of powershell data into 2 x 1d arrays and trying to add them into a single 2d array. Retrieving the data together into the 2d array seemed harder, due to the application names varying too much to string split. Data being pulled is application name and GUID. From here I will use this info in a drop down box and an uninstall button to run the required command to remove the selected software (have this sorted already).
      Problem: When I merge the data it doesn't put the application name and GUID on the same row in differing columns eg. my test box has 24 applications plus some superfluous data from Powershell to be cleaned up by the _arraydeletes. Instead I end up with an array with 58 rows and 2 columns; whereas my temp 1d arrays have 28 rows. As you can see I've tried both _ArrayInsert and _ArrayAdd but I still get the same result.
      Question: Is there something that I'm doing wrong in putting the data into the 2d array or do I just need to do some more post processing to tidy it up and align the names and GUIDs?
      Code:
      #include <Array.au3> $Cmd1 = (" /c Powershell.exe " & Chr(34) & "Get-WmiObject -Class win32reg_addremoveprograms | where {$_.ProdID -like " & Chr(34) & Chr(123) & Chr(42) & Chr(125) & Chr(34) & "} | select DisplayName" & Chr(34)) $Cmd2 = (" /c Powershell.exe " & Chr(34) & "Get-WmiObject -Class win32reg_addremoveprograms | where {$_.ProdID -like " & Chr(34) & Chr(123) & Chr(42) & Chr(125) & Chr(34) & "} | select ProdID" & Chr(34)) Global $aNameGUID[1][2] ;_ArrayDisplay($aNameGUID) ReadApps($Cmd1,0) ;_ArrayDisplay($aNameGUID) ReadApps($Cmd2,1) _ArrayDisplay($aNameGUID) Terminate() Func ReadApps($Command,$col) $DOS = Run(@ComSpec & $Command, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) ProcessWaitClose($DOS) $DOSOut = StdoutRead($DOS) ;MsgBox(0,"Data",$DOSOut) ;Show the line items that we want in the array Local $tmpArray = StringSplit(StringTrimRight(StringStripCR($DOSOut), StringLen(@CRLF)), @CRLF) If @error Then MsgBox(0,"FAIL","I failed to find objects") Exit Else _ArrayDisplay($tmpArray) EndIf ;_ArrayDelete($tmpArray, 3) ;_ArrayDelete($tmpArray, 2) ;_ArrayDelete($tmpArray, 1) ;$tmpArray[0] = $tmpArray[0] - 3 For $i = 0 To UBound($tmpArray) - 1 ;_ArrayAdd($aNameGUID, $tmpArray[$i], $col) _ArrayInsert($aNameGUID, 0, $tmpArray[$i], $col) Next $tmpArray = 0 EndFunc ;==>ReadApps While 1 Sleep(1500) WEnd Func Terminate() Exit 0 EndFunc ;==>Terminate Thanks in advance,
      Luxyboy
    • kctvt
      By kctvt
      Hi there, i'm looking for a script to take max number of a column in Excel.
       
      Ex :  Column C , i have : 
      12
      13
      22
      123
      154
      ....
      .....
      .....
      134534
      (About 134600 rows)

      So, How to know which is the max number in Column C.
      I have this code, but it take me a lot of time >"< 
      So... please help me a faster code.
       
      $x = 3 $CloseCheck1 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+1) $CloseCheck2 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+2) $CloseCheck3 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+3) $CloseCheck4 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+4) $CloseCheck5 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+5) $CloseCheck6 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+6) $CloseCheck7 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+7) $CloseCheck8 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+8) $CloseCheck9 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+9) $CloseCheck10 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+10) Local $aArray = StringSplit($CloseCheck1&","&$CloseCheck2&","&$CloseCheck3&","&$CloseCheck4&","&$CloseCheck5&","&$CloseCheck6&","&$CloseCheck7&","&$CloseCheck8&","&$CloseCheck9&","&$CloseCheck10,",") $DMAX = _ArrayMax($aArray, 1, 1) $DMIN = _ArrayMin($aArray, 1, 1) $n = 11 While 1 $CloseCheckn = _Excel_RangeRead($oWorkbook, Default, "C"&$x+n) If $CloseCheckn > $DMAX Then Global $DMAX = $CloseCheckn EndIf If $CloseCheckn < $DMIN Then Global $MIN = $CloseCheckn EndIf If $CloseCheckn = "" Then ExitLoop EndIf $n = $n + 1 WEnd _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMAX, "P1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMIN, "P2")  
       
      Thanks  
       
       
       
       
    • snaileater
      By snaileater
      I'm trying the Excel.udf, my starting point are the examples found in the help.
      My problem is that i can't find any working example of _Excel_RangeInsert ... made many tries but i can't find the reason why ...
      New/existing Worbook nothing changes ... i tried _Excel_RangeWrite without any problem, but with _Excel_RangeInsert i always get an @error=3 and @extended=-2147352562 ...
      What could i be missing ?
      Here's the minimalistic snippet i'm playing with :
      Local $sWorkbook = @ScriptDir & "\pixel.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $dummy=_Excel_RangeInsert($oWorkbook.Activesheet, "1:3") If @error Then    $a=@extended    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error inserting" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Else    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Rows successfully inserted") EndIf ConsoleWrite ($dummy & chr(13) & $a & Chr(13)) I tried every possible (...) syntax for the range object, without success ... 
      I use the latest releases of AutoIt and Office 97 ...
      Thanks for your help ...