Excel Range Copy Paste from source to multiple files

DigDeep · March 27, 2018

I have 1 source Excel (Excel 1), which I am reading to find some texts and copying the row to 4 different Excels (2, 3, 4 & 5).

If Excel 1 cell reads as Grade A, the row should be copied to Excel 2

If Excel 1 cell reads as Grade B, the row should be copied to Excel 3

If Excel 1 cell reads as Grade C, the row should be copied to Excel 4

If Excel 1 cell reads as Grade D, the row should be copied to Excel 5

I am currently doing the above tasks as a lengthy process. Saving excel1 as excel 2, Looking for anything but Grade 1, deleting all of them. Then saving excel1 as excel 3 and do the same task. This is working all good but is way too lengthy process & trying to shorten it into 1 shot itself. The problem is I am not able to get the _Excel_RangeCopyPaste set correctly.

Can anyone please help?

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

; Create application object and open an example workbook
Local $oExcel = _Excel_Open(False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open Excel 2. This will hold Result A
Local $oWorkbook2 = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel2.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Test\Excel2.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; Open Excel 3. This will hold Result B
Local $oWorkbook3 = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel3.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Test\_Excel3.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; Open Excel 4. This will hold Result C
Local $oWorkbook4 = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel4.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Test\Excel4.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; Open Excel 5. This will hold Result D
Local $oWorkbook5 = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel5.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Test\Excel5.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf


; Open Excel 1. To be used as the Source Sheet.
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel1.xls", True)
If @error Then
;~  MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Test\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf



    Local $LastColNum = $oWorkbook1.ActiveSheet.UsedRange.Columns.Count ; Get last column Number from Excel 1 (Source sheet)
    Local $ColName
    Local $LastColName

    For $j = $LastColNum To 1 Step -1
        Local $FilePath = @ScriptDir & '\Test\Output.txt'
        Local $Output_FileOpen = FileOpen($FilePath, 2)
        Local $FileWrite = FileWrite($Output_FileOpen, $j)
        FileClose($Output_FileOpen)
        Local $TotalCol = FileRead($FilePath)
        Local $LastColName = _Excel_ColumnToLetter($TotalCol) ; Get last column Header Name
        Local $ColName = _Excel_RangeRead($oWorkbook1, Default, $LastColName & '1')

        Local $Result_Col = 'Result'

        If $ColName = $Result_Col Then
            Local $rCountLines = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count

            For $r = $rCountLines To 1 Step -1
                Local $sResult_Tab = _Excel_RangeRead($oWorkbook1, Default, $LastColName & $r)
                Local $result1 = StringInStr($sResult_Tab, "Grade A")
                If $result1 = 1 Then
                    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $ColName, $oWorkbook2) ; Copy the Row from Excel 1 to Excel 2
                EndIf

                Local $result2 = StringInStr($sResult_Tab, "Grade B")
                If $result2 = 1 Then
                    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $ColName, $oWorkbook3) ; Copy the Row from Excel 1 to Excel 3
                EndIf

                Local $result3 = StringInStr($sResult_Tab, "Grade C")
                If $result3 = 1 Then
                    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $ColName, $oWorkbook4) ; Copy the Row from Excel 1 to Excel 4
                EndIf

                Local $result4 = StringInStr($sResult_Tab, "Grade D")
                If $result4 = 1 Then
                    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $ColName, $oWorkbook5) ; Copy the Row from Excel 1 to Excel 5
                EndIf

            Next
        EndIf

    Next

_Excel_Close($oExcel)

Edited March 27, 2018 by DigDeep

Subz · March 27, 2018

Can you post your Excel files for testing?

DigDeep · March 27, 2018

@Subz, attaching a sample here.

Test.zip

Subz · March 27, 2018

What about something like:

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

; Create application object and open an example workbook
Local $oExcel = _Excel_Open(False)

; Open Excel 1. To be used as the Source Sheet.
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel1.xls", True)
Local $aWorkbook1 = _Excel_RangeRead($oWorkbook1, Default, $oWorkbook1.ActiveSheet.UsedRange)
;~ Sort by Grade
_ArraySort($aWorkbook1, 0, 1, 0, 3)

Local $aExcelGradeA[0][UBound($aWorkbook1, 2)], $aExcelGradeB[0][UBound($aWorkbook1, 2)], $aExcelGradeC[0][UBound($aWorkbook1, 2)], $aExcelGradeD[0][UBound($aWorkbook1, 2)]
_ArrayAdd($aExcelGradeA, _ArrayToString($aWorkbook1, "|", 0, 0))
_ArrayAdd($aExcelGradeB, _ArrayToString($aWorkbook1, "|", 0, 0))
_ArrayAdd($aExcelGradeC, _ArrayToString($aWorkbook1, "|", 0, 0))
_ArrayAdd($aExcelGradeD, _ArrayToString($aWorkbook1, "|", 0, 0))

__Excel_GradeRange()

__Excel_SaveRange($aExcelGradeA, @ScriptDir & "\Test\Excel2.xls")
__Excel_SaveRange($aExcelGradeB, @ScriptDir & "\Test\Excel3.xls")
__Excel_SaveRange($aExcelGradeC, @ScriptDir & "\Test\Excel4.xls")
__Excel_SaveRange($aExcelGradeD, @ScriptDir & "\Test\Excel5.xls")

Func __Excel_SaveRange($aExcelGrade, $sExcelFile)
    If UBound($aExcelGrade) - 1 >= 2 Then
        Local $oWorkbook = _Excel_BookOpen($oExcel, $sExcelFile, False)
        _Excel_RangeWrite($oWorkbook, Default, $aExcelGrade, "A1")
        _Excel_BookClose($oWorkbook, True)
    EndIf
EndFunc

Func __Excel_GradeRange()
    For $i = 1 To UBound($aWorkbook1) - 1
        Switch StringStripWS($aWorkbook1[$i][3], 7)
            Case "Grade A"
                _ArrayAdd($aExcelGradeA, _ArrayToString($aWorkbook1, "|", $i, $i))
            Case "Grade B"
                _ArrayAdd($aExcelGradeB, _ArrayToString($aWorkbook1, "|", $i, $i))
            Case "Grade C"
                _ArrayAdd($aExcelGradeC, _ArrayToString($aWorkbook1, "|", $i, $i))
            Case "Grade D"
                _ArrayAdd($aExcelGradeD, _ArrayToString($aWorkbook1, "|", $i, $i))
        EndSwitch
    Next
EndFunc

DigDeep · March 27, 2018

@Subz what is aExcelGrade referred to the text in the column. What if there will be other sets of texts in that column?

Like: High, Low, Medium, Review and I would like to search for them?

Func __Excel_SaveRange($aExcelGrade, $sExcelFile)

DigDeep · March 27, 2018

And it is giving 'Variable must be of type object error @ line:

Local $aWorkbook1 = _Excel_RangeRead($oWorkbook1, Default, $oWorkbook1.ActiveSheet.UsedRange)

Edited March 27, 2018 by DigDeep

Subz · March 27, 2018

$aExcelGradeA to $aExcelGradeD are arrays that will hold the Grade information for example in the __Excel_GradeRange function, we check Column 3 if it reads "Grade A" and then it writes the line to $aExcelGradeA array and so on for each of the different grades. If you want to change what to search for just change the __ExcelGradeRange() function Case to match the string you wish to search for.

With regards to the variable, in the OP you used Excel1,xls, Excel2.xls etc.. however in the zip file you had Excel 1.xls, Excel 2.xls etc... (note the space between Excel and number), I renamed the zip files to match your OP i.e. Excel1.xls, Excel2.xls etc... and that works fine.

DigDeep · March 27, 2018

Thanks @Subz. It's working as lightning speed. I think I'll have to review my other portions too but for now I am good.

Thank you again.

Subz · March 27, 2018

Already started this so thought I'd add it as well, the difference with this method:

Only requires Excel1.xls to exist, if Excel2 - Excel5 files don't exist they will be created.
__Excel_SearchRange() function will take the Filename, Search String and Column to Search so you can just change this as required

Anyway happy coding.

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

Global $oExcel = _Excel_Open(False)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel1.xls", True)
Global $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange)
_Excel_BookClose($oWorkbook, False)

__Excel_SearchRange(@ScriptDir & "\Test\Excel2.xls", "Grade A", 3)
__Excel_SearchRange(@ScriptDir & "\Test\Excel3.xls", "Grade B", 3)
__Excel_SearchRange(@ScriptDir & "\Test\Excel4.xls", "Grade C", 3)
__Excel_SearchRange(@ScriptDir & "\Test\Excel5.xls", "Grade D", 3)

;~ $_sWorkbook = Name of Workbook to save search items to.
;~ $_sSearchString = Search String to search $aWorkbook for.
;~ $_iColumnSearch = $aWorkbook Column to Search String for.
Func __Excel_SearchRange($_sWorkbook, $_sSearchString, $_iColumnSearch)
    Local $oWorkbook, $bWorkbookNew = False, $aExcelRange[0][UBound($aWorkbook, 2)]
    Local $aExcelSearch = _ArrayFindAll($aWorkbook, $_sSearchString, 1, 0, 0, 1, $_iColumnSearch)
        If @error Then Return

    For $i = 0 To UBound($aExcelSearch) - 1
        _ArrayAdd($aExcelRange, _ArrayToString($aWorkbook, "|", $aExcelSearch[$i], $aExcelSearch[$i]))
    Next
    If UBound($aExcelRange) - 1 >= 1 Then
        If FileExists($_sWorkbook) Then
            $oWorkbook = _Excel_BookOpen($oExcel, $_sWorkbook, False)
        Else
            $oWorkbook = _Excel_BookNew($oExcel)
        EndIf

        ;~ Get the number of rows in the current Excel document
        Local $iWorkbookRows = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
            If  $iWorkbookRows = 1 Then $bWorkbookNew = True
        ;~ Check if this is a New Workbook or Existing Workbook
        If $bWorkbookNew Then
            ;~ Add the Excel Header to the Array
            _ArrayInsert($aExcelRange, 0, _ArrayToString($aWorkbook, "|", 0, 0))
            ;~ New Workbook write to cell A1
            _Excel_RangeWrite($oWorkbook, Default, $aExcelRange, "A1")
        Else
            ;~ Existing Workbook append Array data to last row
            _Excel_RangeWrite($oWorkbook, Default, $aExcelRange, "A" & $iWorkbookRows + 1)
            _Excel_BookSaveAs($oWorkbook, $_sWorkbook)
        EndIf
        _Excel_BookClose($oWorkbook, True)
    EndIf
EndFunc

DigDeep · March 27, 2018

@Subz, once again thanks. Tested both versions and they work all good.

But here's one issue. Yesterday I had posted a question regarding an issue with Cell Formatting issue at the below page.

The Solution provided was to use StringInStr and that solved searching the texts. With the above 2 examples shared by you, it is not able to search if there is a formatting issue. Could you please help to see where can we adjust the StringInStr in any of the examples?

Thanks again.

Subz · March 27, 2018

Can you please give me an example, preferably an Excel file with formatting issues? If you look in the second example I use the _ArrayFindAll which does a partial search of the text in the columns, basically the same as StringInStr. In the first example I posted you would have to change the _Excel_GradeRange to:

Func __Excel_GradeRange()
    For $i = 1 To UBound($aWorkbook1) - 1
        If StringInStr($aWorkbook1[$i][3], "Grade A") Then _ArrayAdd($aExcelGradeA, _ArrayToString($aWorkbook1, "|", $i, $i))
        If StringInStr($aWorkbook1[$i][3], "Grade B") Then _ArrayAdd($aExcelGradeB, _ArrayToString($aWorkbook1, "|", $i, $i))
        If StringInStr($aWorkbook1[$i][3], "Grade C") Then _ArrayAdd($aExcelGradeC, _ArrayToString($aWorkbook1, "|", $i, $i))
        If StringInStr($aWorkbook1[$i][3], "Grade D") Then _ArrayAdd($aExcelGradeD, _ArrayToString($aWorkbook1, "|", $i, $i))
        EndSwitch
    Next
EndFunc

DigDeep · March 27, 2018

Attaching the same test file uploaded yesterday. Please look at the 2nd column text. Another thing is that even if the above 2 examples work, why are they not copying the tables?

test.xls

Edited March 27, 2018 by DigDeep

Subz · March 27, 2018

Below worked fine for me, I don't know what you mean by "why are they not copying the tables", Excel2.xls - Excel5.xls are all updated with the full tables from Excel1.xls. What are you seeing in your Excel2.xls?

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

Global $oExcel = _Excel_Open(False)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\test.xls", True)
Global $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange)
_Excel_BookClose($oWorkbook, False)

__Excel_SearchRange(@ScriptDir & "\Test\Excel2.xls", "State", 1)

;~ $_sWorkbook = Name of Workbook to save search items to.
;~ $_sSearchString = Search String to search $aWorkbook for.
;~ $_iColumnSearch = $aWorkbook Column to Search String for.
Func __Excel_SearchRange($_sWorkbook, $_sSearchString, $_iColumnSearch)
    Local $oWorkbook, $bWorkbookNew = False, $aExcelRange[0][UBound($aWorkbook, 2)]
    Local $aExcelSearch = _ArrayFindAll($aWorkbook, $_sSearchString, 1, 0, 0, 1, $_iColumnSearch)
        If @error Then Return

    For $i = 0 To UBound($aExcelSearch) - 1
        _ArrayAdd($aExcelRange, _ArrayToString($aWorkbook, "|", $aExcelSearch[$i], $aExcelSearch[$i]))
    Next
    If UBound($aExcelRange) - 1 >= 1 Then
        If FileExists($_sWorkbook) Then
            $oWorkbook = _Excel_BookOpen($oExcel, $_sWorkbook, False)
        Else
            $oWorkbook = _Excel_BookNew($oExcel)
        EndIf

        ;~ Get the number of rows in the current Excel document
        Local $iWorkbookRows = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
            If  $iWorkbookRows = 1 Then $bWorkbookNew = True
        ;~ Check if this is a New Workbook or Existing Workbook
        If $bWorkbookNew Then
            ;~ Add the Excel Header to the Array
            _ArrayInsert($aExcelRange, 0, _ArrayToString($aWorkbook, "|", 0, 0))
            ;~ New Workbook write to cell A1
            _Excel_RangeWrite($oWorkbook, Default, $aExcelRange, "A1")
        Else
            ;~ Existing Workbook append Array data to last row
            _Excel_RangeWrite($oWorkbook, Default, $aExcelRange, "A" & $iWorkbookRows + 1)
            _Excel_BookSaveAs($oWorkbook, $_sWorkbook)
        EndIf
        _Excel_BookClose($oWorkbook, True)
    EndIf
EndFunc

DigDeep · March 27, 2018

@Subz, I know it might be frustrating for you but it is not working at my end. I even tried with your last example above ad it does nothing.

The only option it works with the excel samples I had sent initially.

Also by tables, I meant the borders. When the rows are getting copied to Excel2.xls, the borders are not. Which is why the Date format also changes.

The way you have shown is really a good piece and needed this to make the work which is around 15-20 minutes now drop to few seconds. I will be glad if you can still help me fix this.

Excel2.xls

Subz · March 27, 2018

@DigDeep Sorry for the delay, sorry I didn't see your CopyandPaste function which is required for copying formatting etc... So here is a different method, just using Excel filtering and Copy and Paste to new Workbooks, again the files Excel2 - Excel5 do not have to exist, if they do the data will be appended to the end of sheet.

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

Global $oExcel = _Excel_Open(False)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test\Excel1.xls", True)
    __Excel_FilterRange(@ScriptDir & "\Test\Excel2.xls", "Grade A", 4)
    __Excel_FilterRange(@ScriptDir & "\Test\Excel3.xls", "Grade B", 4)
    __Excel_FilterRange(@ScriptDir & "\Test\Excel4.xls", "Grade C", 4)
    __Excel_FilterRange(@ScriptDir & "\Test\Excel5.xls", "Grade D", 4)
_Excel_BookClose($oWorkbook)

Func __Excel_FilterRange($_sWorkbook, $_vFilterCriteria, $_iFilterColumn)
    Local $bWorkbookNew = False
    _Excel_FilterSet($oWorkbook, Default, Default, $_iFilterColumn, $_vFilterCriteria)
    If FileExists($_sWorkbook) Then
        $oXLWorkbook = _Excel_BookOpen($oExcel, $_sWorkbook, False)
    Else
        $oXLWorkbook = _Excel_BookNew($oExcel)
    EndIf
    ;~ Get the number of rows in the current Excel document
    Local $iWorkbookRows = $oXLWorkbook.ActiveSheet.UsedRange.Rows.Count
    If  $iWorkbookRows = 1 Then $bWorkbookNew = True
    ;~ Check if this is a New Workbook or Existing Workbook
    If $bWorkbookNew Then
        ;~ Add the Excel Header to the Array
        _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible), $oXLWorkbook.ActiveSheet.Range("A1"))
        _Excel_BookSaveAs($oXLWorkbook, $_sWorkbook)
    Else
        _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.OffSet(1).SpecialCells($xlCellTypeVisible), $oXLWorkbook.ActiveSheet.Range("A" & $iWorkbookRows + 1))
    EndIf
    _Excel_BookClose($oXLWorkbook, True)
EndFunc

DigDeep · March 27, 2018

Thanks @Subz for staying long. I'll get this tested and let you know.

Subz · March 27, 2018

NP, if you want to test your "State" spreadsheet, just use the following:

__Excel_FilterRange(@ScriptDir & "\Test\Excel2.xls", "*State*", 1)

@ScriptDir & "\Test\Excel2.xls = Name of Spreadsheet you want to save the results to
"*State*" = Filter where Cell "contains" the word State
1 = The Column where you want to apply the filter

DigDeep · March 28, 2018

@Subz, I think the ** was the one I was looking for. Appreciate all your help.

I have also added a little piece so we don't have to change the Column Number every time.

Local $GetLastColNumber = $oWorkbook.ActiveSheet.UsedRange.Columns.Count ; Get last column Number

For $i = $GetLastColNumber To 1 Step -1
Local $TotalColumns = $i
    Local $GetLastColName = _Excel_ColumnToLetter($TotalColumns)
    Local $GetColName = _Excel_RangeRead($oWorkbook, Default, $GetLastColName & '1')
    Local $Read_Col = 'Col 3' ; This will be the Column Header we want to read the cells from

    If $GetColName = $Read_Col Then
        __Excel_FilterRange(@ScriptDir & "\Test\Excel2.xls", "*State*", $i)
        EndIf
Next

Edited March 28, 2018 by DigDeep

Excel Range Copy Paste from source to multiple files

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members