Jump to content
VeryGut

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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".

Share this post


Link to post
Share on other sites
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

 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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 ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by VeryGut

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 :(

Share this post


Link to post
Share on other sites
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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
VeryGut

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

#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

 

Share this post


Link to post
Share on other sites
water

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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

×