Python Forum
Python code to copy data from multiple workbooks into master sheet
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python code to copy data from multiple workbooks into master sheet
#1
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 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!
Reply
#2
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.
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply
#3
(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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  python convert multiple files to multiple lists MCL169 6 1,436 Nov-25-2023, 05:31 AM
Last Post: Iqratech
  How to copy work sheet data one workbook to other? sayyedkamran 2 645 Nov-03-2023, 09:10 AM
Last Post: Larz60+
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,696 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,925 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  python multiple try except block in my code -- can we shorten code mg24 10 5,893 Nov-10-2022, 12:48 PM
Last Post: DeaD_EyE
  python Extract sql data by combining below code. mg24 1 914 Oct-03-2022, 10:25 AM
Last Post: mg24
  Load multiple Jason data in one Data Frame vijays3 6 1,500 Aug-12-2022, 05:17 PM
Last Post: vijays3
  OCR-Python from Multi TIFF to HOCR getting only Data from 1st Page of multiple TIFF JOE 0 2,120 Feb-18-2022, 03:18 PM
Last Post: JOE
  Python code to read second line from CSV files and create a master CSV file sh1704 1 2,353 Feb-13-2022, 07:13 PM
Last Post: menator01
  Python, how to manage multiple data in list or dictionary with calculations and FIFO Mikeardy 8 2,524 Dec-31-2021, 07:47 AM
Last Post: Mikeardy

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020