Jump to content

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


Recommended Posts

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

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 :(

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...