Python code to copy data from multiple workbooks into master sheet - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Python code to copy data from multiple workbooks into master sheet (/thread-20585.html) |
Python code to copy data from multiple workbooks into master sheet - Fatman003 - Aug-20-2019 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 VBA code: 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 SubI'm not so good with python and would really appreciate your help! Please let me know if you require any clarification. Many thanks! RE: Python code to copy data from multiple workbooks into master sheet - perfringo - Aug-21-2019 pandas seems good choice - read data from files into dataframe with .read_excel method (defining sheet_name, usecols, skiprows) and write into Excel file using .to_excel method. RE: Python code to copy data from multiple workbooks into master sheet - Fatman003 - Aug-21-2019 (Aug-21-2019, 11:14 AM)perfringo Wrote: pandas seems good choice - read data from files into dataframe with .read_excel method (defining sheet_name, usecols, skiprows) and write into Excel file using .to_excel method. Pandas ruins the formatting and I want the format to still be present in the Master sheet |