Dynamic excel range when copying/pasting data to master excel file

[VeryGut]

I am working on a project that could help me import data from multiple excel files into a master file, all within the directory the script was put into. The example would be:

Excel1, Excel2, Excel3 -  each one with 10 rows of data

MasterExcel - total of 30 rows of data (in order: Excel, Excel2, Excel3)

Below is my code so far:

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>

HotKeySet("{ESC}", "Terminate")

Global $Files = _FileListToArray (@ScriptDir, "*")
$oExcel = _Excel_Open()
$MasterFile = _Excel_BookNew($oExcel, 1)
$Invoices = 0
$xlup = -4162


for $i=2 to $Files[0]
   If $Files[$i] = "InvoiceCounter" Then
      $i = $i + 1
   Else
      Sleep(500)
      $sWorkbook = @ScriptDir & "\" & $Files[$i]
      Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)
      Sleep(500)
      $NumberOfRows = $oExcel.ActiveSheet.Range("A65536").End($xlup).Row
      $Invoices = $Invoices + $NumberOfRows - 4 ;skip first 4 rows as they do not contain data
      Sleep(500)
      Local $oRange = $oWorkbook.ActiveSheet.Range("A5:A100") ;skip first 4 rows as they do not contain data
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange, "A1:A100")
   EndIf
Next
MsgBox($MB_OK, "", "Number of invoices: " & $Invoices)

Func Terminate()
    Exit
EndFunc   ;==>Terminate

 

I have trouble with ths part:

Local $oRange = $oWorkbook.ActiveSheet.Range("A5:A100")
 _Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange, "A1:A100")

Instead of using the fixed range "A1:A100", I want my script to adjust to the number of rows that are actually used. I imagine this could be solved similar to this (unfrotunatelly it DOESNT WORK) :

$StartingField = "A1"   (declared before the For...Next loop)

[...]
Local $oRange = $oWorkbook.ActiveSheet.Range("A5":"A" & $NumberOfRows)
_Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange, $StartingField : A" & $Invoices)
$StartingField = "A" & 1 + $NumberOfRows   ; rememer that this is in loop, so the A1 value will be overwritten

 

How could I make this work? Is it possible to make the ranges dynamic in some way instead of being locked like "A1:A100"?

 

 

Thanks in advance!

 

Edited June 1, 2017 by VeryGut

[water]

Do you want to copy all used cells or just the used cells in column A?
Details about "used cells" can be found in the wiki: https://www.autoitscript.com/wiki/Excel_Range#Used_Range

[VeryGut]

1 minute ago, water said:

Do you want to copy all used cells or just the used cells in column A?
Details about "used cells" can be found in the wiki: https://www.autoitscript.com/wiki/Excel_Range#Used_Range

I intend to copy multiple columns, but for the purpose of the example I decided to limit it to column A. I'v read the info under the link you provided, but unfortunatelly I am unable to come up with a working solution based on it. To my understanding, it does not cover the possibility of using variables in the cell range and therefore I am stuck to fixed cell ranges like "A1:A100".

[water]

Something like this?

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>

HotKeySet("{ESC}", "Terminate")

Global $aFiles = _FileListToArray(@ScriptDir, "test*.xlsx")
Global $oExcel = _Excel_Open()
Global $oMasterFile = _Excel_BookNew($oExcel, 1)
Global $iInvoices = 0
Global $xlup = -4162
Global $sWorkbook, $oWorkbook, $iLastRow, $oRange, $iFirstRow2Insert = 1

For $i = 1 To $aFiles[0]
    If $aFiles[$i] = "InvoiceCounter" Then ContinueLoop
    $sWorkbook = @ScriptDir & "\" & $aFiles[$i]
    ConsoleWrite("Processing: " & $sWorkbook & @CRLF)
    $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)
    $iLastRow = $oExcel.ActiveSheet.Range("A65536").End($xlup).Row
    $iInvoices = $iInvoices + $iLastRow - 4 ; skip first 4 rows as they do not contain data
    $oRange = $oWorkbook.ActiveSheet.Range("A5:A" & $iLastRow)
    _Excel_RangeCopyPaste($oMasterFile.ActiveSheet, $oRange, "A" & $iFirstRow2Insert)
    $iFirstRow2Insert = $iFirstRow2Insert + $iLastRow - 4
    _Excel_BookClose($oWorkbook, False)
Next
MsgBox($MB_OK, "", "Number of invoices: " & $iInvoices)

Func Terminate()
    Exit
EndFunc   ;==>Terminate

 

[VeryGut]

Thanks Water, it works like a charm!

I have modified my code based on your way of dealing with variables in ranges and it works as well Compared to the clear way that your code is presented, I have much to work on. Anyway, below is my piece of cr%p:

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>

HotKeySet("{ESC}", "Terminate")

Global $Files = _FileListToArray (@ScriptDir, "*")
$oExcel = _Excel_Open()
$MasterFile = _Excel_BookNew($oExcel, 1)
$Invoices = 0
$xlup = -4162
$iFirstRow2Insert = 1


for $i=2 to $Files[0]
   If $Files[$i] = "InvoiceCounter" Then
      $i = $i + 1
   Else
      $sWorkbook = @ScriptDir & "\" & $Files[$i]
      $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)
      $NumberOfRows = $oExcel.ActiveSheet.Range("A65536").End($xlup).Row
      $Invoices = $Invoices + $NumberOfRows - 4
      $oRange = $oWorkbook.ActiveSheet.Range("A5:A" & $NumberOfRows)
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange, "A" & $iFirstRow2Insert)
      $iFirstRow2Insert = $iFirstRow2Insert + $NumberOfRows - 4
      _Excel_BookClose($oWorkbook, False)
   EndIf
Next
MsgBox($MB_OK, "", "Number of invoices: " & $Invoices)

Func Terminate()
    Exit
EndFunc   ;==>Terminate

 

[water]

If $Files[$i] = "InvoiceCounter" Then
      $i = $i + 1
   Else

This is wrong and ignores the file after "InvoiceCounter" because you increment $i twice. Check my solution

[VeryGut]

39 minutes ago, water said:

If $Files[$i] = "InvoiceCounter" Then
      $i = $i + 1
   Else

This is wrong and ignores the file after "InvoiceCounter" because you increment $i twice. Check my solution

Noted! By the way, do you know a method to paste values only with _Excel_RangeCopyPaste?

_Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange, "A" & $iFirstRow2Insert, $xlPasteValues)

Doesnt seem to be working, as it still imports formulas from the colums I wish to copy

[water]

Sure. Set Parameter 5 to $xlPasteValues.
This is one of the values of the XlPasteType enumeration: http://msdn.microsoft.com/en-us/library/ff837425(v=office.14).aspx

Edited June 1, 2017 by water

[VeryGut]

19 minutes ago, water said:

Sure. Set Parameter 5 to $xlPasteValues.
This is one of the values of the XlPasteType enumeration: http://msdn.microsoft.com/en-us/library/ff837425(v=office.14).aspx

Something funky is happening when Im using $xlPasteValues. I get the values as a result, but still the cells contain formulas. Is there any way to import raw values, simillar to how when manually copying/pasting cells one can chose to "paste values only" from the pop-up context window?

Edited June 1, 2017 by VeryGut

[water]

In your example in post #7 you set parameter #4 - which is wrong! Set parameter 4 to Default and parameter 5 to $xlPasteValues.

[VeryGut]

4 minutes ago, water said:

In your example in post #7 you set parameter #4 - which is wrong! Set parameter 4 to Default and parameter 5 to $xlPasteValues.

_Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange, "A" & $iFirstRow2Insert, Default, $xlPasteValues) doesnt work, it copies the formulas with no reference to the source files (i know, its weird!)

_Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange, "A" & $iFirstRow2Insert, $xlPasteValues) - works, as it displays values, but the formulas reference the source file. I wish to keep raw values, without any reference

[water]

My bad. The help file exactly describes what goes on:
"If $vSourceRange and $vTargetRange are specified parameters $iPaste, $iOperation, $bSkipBlanks and $bTranspose are ignored"

So we need to call the function twice: First copy the source range to the clipboard, then copy the clipboard to the target workbook.
Untested:

_Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange) ; Copy the source to the clipboard
_Excel_RangeCopyPaste($MasterFile.ActiveSheet, Default, "A" & $iFirstRow2Insert, Default, $xlPasteValues)

 

[VeryGut]

Thanks to Water I got my script working just as I wanted! Below is the code

expandcollapse popup

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>

HotKeySet("{ESC}", "Terminate")

$Files = _FileListToArray (@ScriptDir, "*") ;This script needs to be put in the folder where excel files are stored
$oExcel = _Excel_Open()
$MasterFile = _Excel_BookNew($oExcel, 1)     ;This is the masterfile where values will be imported
$Invoices = 0
$xlup = -4162
$iFirstRow2Insert = 1

for $i=1 to $Files[0]
   If $Files[$i] = "InvoiceCounter.au3" Then ContinueLoop
      $sWorkbook = @ScriptDir & "\" & $Files[$i]
      $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)
      $NumberOfRows = $oExcel.ActiveSheet.Range("A65536").End($xlup).Row
      $Invoices = $Invoices + $NumberOfRows - 4
      $oRange = $oWorkbook.ActiveSheet.Range("A5:A" & $NumberOfRows)
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange) ; Copy the source to the clipboard
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, Default, "A" & $iFirstRow2Insert, Default, $xlPasteValues) ;<== this is a value calculated using formulas, in order to be used further, this need to be pasted as a raw value
      $oRange = $oWorkbook.ActiveSheet.Range("H5:H" & $NumberOfRows)
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange) ; Copy the source to the clipboard
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, Default, "H" & $iFirstRow2Insert) ;<== this is the date, so we do not want to paste it as a value or it will bug
      $oRange = $oWorkbook.ActiveSheet.Range("M5:M" & $NumberOfRows)
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, $oRange)
      _Excel_RangeCopyPaste($MasterFile.ActiveSheet, Default, "M" & $iFirstRow2Insert, Default, $xlPasteValues)
      $iFirstRow2Insert = $iFirstRow2Insert + $NumberOfRows - 4
      _Excel_BookClose($oWorkbook, False)
Next
MsgBox($MB_OK, "", "Number of invoices: " & $Invoices)

Func Terminate()
    Exit
EndFunc   ;==>Terminate

 

[water]