Speed up 10x copying a workbook's worksheet which has 100k rows to the current workbook

Question

I know this might have been asked a hundred of times but is there a way to really speed up my code below even faster, maybe 10 times faster? I am open to like Python-Excel codes, etc.

This could be a more different question because I am not just limiting myself with plain VBA.

Code:

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlManual

    Dim wb_raw As Workbook
    Set wb_raw = Workbooks.Open("C:\Projects\Raw.xlsx")

    wb_raw.Worksheets(1).Copy Before:=ThisWorkbook.Worksheets(2)
    ' the worksheet being copied has 100k rows

    wb_raw.Close

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomatic

I am also open to the PowerQuery then refresh route.

Does this answer your question? [How To Speed Up VBA Code](https://stackoverflow.com/questions/47089741/how-to-speed-up-vba-code) — Samuel Everson, May 02 '20 at 00:12
@Variatus I just needed help, I also asked if Pyhon-VBA (if there such as thing) can be a little better than plain VBA alone. — XYZKLM, May 02 '20 at 10:42

bradbase · Accepted Answer · 2020-05-06T03:29:35.860

1

There are a few potential pathways through this, depending on the resources you choose to use.

You have not mentioned whether you want values copied, formulas or both. I assume values only.

If you can do the copy while the workbook is closed, maybe pyopenxl or xlrd and xlwrt could do the trick. Both of these can copy values and formulas but neither of these solutions can evaluate formulas but that sounds OK from your description.

If you need the workbook to be open while you're doing the copy or move, there are some great solutions which integrate Python and Excel using COM. xlwings, PyXll , FlyingKoala and DataNitro. With these solutions you can write some Python code that does a copy. If you just want values, you can use dataframes to move the data around.

I have only used xlwings (and [FlyingKoala] - which uses xlwings). xlwings facilitates a two-way communication between Python and Excel so you can call Python from Excel and you can call Excel (including VBA) from Python. This technique can be great for speeding up operations, especially in reporting and modelling.

PyXll and FlyingKoala have an added advantage where they can read Excel formulas and convert them into Python code then execute (evaluate) the dynamically generated Python code.

An example using xlwings to copy values only. I used this to copy 100,000 rows with 26 columns (A through Z) and it took 17 seconds;

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('range_to_copy', np.array, doc='')
@xw.ret(index=False, header=False, expand='table')
def copy_values(range_to_copy):

    return range_to_copy

edited May 06 '20 at 03:29

answered May 04 '20 at 11:44

bradbase

409
6
9

bradbase I actually imported xlwings before I saw your post but never had the chance to explore it yet. These are my requirements, closebook closed, i am copying values only but like any other raw files they have headers, my only problem with Python is our company IT is strict and not sure if I can installl Python (and other libraries) that easily on the end users' ends. But I am still open to it. – XYZKLM May 05 '20 at 04:34
1

Openpyxl or xlrd and xlwrt might do a good enough job then. It would simply a case of writing an iterator to transfer values from cells in worksheet A to worksheet B. Openpyxl has the ability to tell you which cells in a worksheet are full (worksheet.cell_range left and right) and has optimized reading and writing modes to get better performance. In terms of security, openlyxl has the ability to protect against some xml attacks; "By default openpyxl does not guard against quadratic blowup or billion laughs xml attacks. To guard against these attacks install defusedxml." – bradbase May 05 '20 at 07:05
1

Thank you very much, bradbase. I will explore openpyxl, maybe it's better to just focus on one first. – XYZKLM May 05 '20 at 07:11
Hey, do you own the site bradbase.net ? – XYZKLM May 05 '20 at 07:13
So what? Old but gold. Thanks again for the help. – XYZKLM May 06 '20 at 08:19

Speed up 10x copying a workbook's worksheet which has 100k rows to the current workbook

1 Answers1