By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,818 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,818 IT Pros & Developers. It's quick & easy.

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

P: 3
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
  1. Sub CopyRange()
  2.     Application.ScreenUpdating = False
  3.     Dim wkbDest As Workbook
  4.     Dim wkbSource As Workbook
  5.     Set wkbDest = ThisWorkbook
  6.     Dim LastRow As Long
  7.     Const strPath As String = "C:\Users\f6565\Desktop\data\"
  8.     ChDir strPath
  9.     strExtension = Dir("*.xlsx*")
  10.     Do While strExtension <> ""
  11.         Set wkbSource = Workbooks.Open(strPath & strExtension)
  12.         With wkbSource
  13.             LastRow = .Sheets("Main Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  14.             .Sheets("Main Data").Range("A5:GJ" & LastRow).Copy wkbDest.Sheets("MAIN DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
  15.             .Close savechanges:=False
  16.         End With
  17.         strExtension = Dir
  18.     Loop
  19.     Application.ScreenUpdating = True
  20. 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!
3 Weeks Ago #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 619
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.
3 Weeks Ago #2

P: 3
So far I have done this:

Expand|Select|Wrap|Line Numbers
  1. import openpyxl as xl
  3. path1="C:\Users\f6565\Desktop\data\data1.xlsx"
  4. path2="C:\Users\f6565\Desktop\data\Result.xlsx"
  6. wb1 = xl.load_workbook(filename=path1)
  7. ws1 = wb1.worksheets[0]
  8. wbX = xl.load_workbook(filename=path0)
  9. wsX = wbX.worksheets[0]
  10. wb2= xl.load_workbook(filename=path2)
  11. ws2= wb2.worksheets[0]
  13. for row in ws1:
  14.         for cell in row:
  15.             ws2[cell.coordinate].value=cell.value

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.
3 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.