Thread Modes Python code to copy data from multiple workbooks into master sheet

3 New Member

I have to copy data from 6 workbooks and paste it into a master workbook. All the workbooks are located in a folder on my desktop: C:\Users\f6565\Desktop\data

The workbooks contain a sheet named 'Main Data', I have to open each workbook, go to sheet 'Main Data’, select columns range A to GJ starting from row 5 to row 'x' (end of the rows), then copy and paste the data range into the master worksheet. In the master worksheet (named MAIN DATA), I paste the data into Column A row 5 till the end and continue pasting/appending the data as I copy data from more workbooks. Eventually, the master workbook has the data in columns A row 5 to GJ from every workbook in one sheet.

The columns range A to GJ and starting from row 5 always remains constant in all the sheets (Main Data) of every workbook. Each workbook contains several sheets, but I am only interested in sheet ‘Main Data’. I have to repeat the same steps for the 6 workbooks and continue pasting/appending the data into master sheet. So, I was wondering if someone could please help me to create a python code for this?
I was able to do this with VB and it works. However I need to be able to do this with python entirely!. Here is my VB code:

Expand|Select|Wrap|Line Numbers

 Sub CopyRange()

    Application.ScreenUpdating = False

    Dim wkbDest As Workbook

    Dim wkbSource As Workbook

    Set wkbDest = ThisWorkbook

    Dim LastRow As Long

    Const strPath As String = "C:\Users\f6565\Desktop\data\"

    ChDir strPath

    strExtension = Dir("*.xlsx*")

    Do While strExtension <> ""

        Set wkbSource = Workbooks.Open(strPath & strExtension)

        With wkbSource

            LastRow = .Sheets("Main Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

            .Sheets("Main Data").Range("A5:GJ" & LastRow).Copy wkbDest.Sheets("MAIN DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

            .Close savechanges:=False

        End With

        strExtension = Dir

    Loop

    Application.ScreenUpdating = True

End Sub

I'm not so good with python and would really appreciate your help!

Please let me know if you require any clarification.

Many thanks!

Aug 20 '19 #1

Subscribe Reply

1119

dwblas

626

Recognized Expert Contributor

I'm not so good with python and would really appreciate your help!

You have posted nothing that we can help with. Post the code that you have so far.

Aug 20 '19 #2

Fatman003

New Member

So far I have done this:

Expand|Select|Wrap|Line Numbers

 import openpyxl as xl
 
path1="C:\Users\f6565\Desktop\data\data1.xlsx"

path2="C:\Users\f6565\Desktop\data\Result.xlsx"
 
wb1 = xl.load_workbook(filename=path1)

ws1 = wb1.worksheets[0]

wbX = xl.load_workbook(filename=path0)

wsX = wbX.worksheets[0]

wb2= xl.load_workbook(filename=path2)

ws2= wb2.worksheets[0]
 
for row in ws1:

        for cell in row:

            ws2[cell.coordinate].value=cell.value

wb2.save(path2)

However, it only copies some data values and not every data. Also, I was only able to transfer data from one workbook and not the 7.

Aug 21 '19 #3

Similar topics

Modify Python Code - no idea at all

by: Chris McKeever | last post by:

I am trying to modify the Mailman Python code to stop mapping MIME-types and use the extension of the attachment instead. I am pretty much clueless as to what I need to do here, but I think I have...