VBA - Best method to copy data from a closed workbook

Question

I'm an intern in an industrial company in Brazil and it happens that I'm using excel a lot. I just started playing with VBA couple of days ago, and I'm amused of many things it can do for me!!

I don't have a strong programming background, so I'm learning by doing basically. The code is working fine and it takes less than 15 seconds from start to end. I don't bother with the time, but if it could be improved that'd be great.

My main goal is to keep the code simple and efficient. I'll be leaving the company in the next months and I'd like it to be easy to mantain and use. What I'm asking is a better way to write my code so others can understand easier, and if possible (of course it is!) to take less time.

My code delete 4 sheets of content in my current workbook, and then copy the updated data from 4 others closed workbooks. Then close everything. :) The data is about the daily production and their names are in portuguese, sorry about that.

Sub CopiarBase()

'
' Atalho do teclado: Ctrl+q
'


    ' Variables
    Dim MyCurrentWB As Workbook
    Dim BMalharia As Worksheet
    Dim BBeneficiamento As Worksheet
    Dim BEmbalagem As Worksheet
    Dim BDikla As Worksheet

    Set MyCurrentWB = ThisWorkbook
    Set BMalharia = MyCurrentWB.Worksheets("B-Malharia")
    Set BBeneficiamento = MyCurrentWB.Worksheets("B-Beneficiamento")
    Set BEmbalagem = MyCurrentWB.Worksheets("B-Embalagem")
    Set BDikla = MyCurrentWB.Worksheets("B-Dikla")

    'Clean all the cells - Workbook 1


    Dim Malharia_rng As Range
    Set Malharia_rng = BMalharia.Range("A2:CN" & BMalharia.Cells(Rows.Count, 1).End(xlUp).Row)
    Malharia_rng.ClearContents

    Dim Ben_rng As Range
    Set Ben_rng = BBeneficiamento.Range("A2:CY" & BBeneficiamento.Cells(Rows.Count, 1).End(xlUp).Row)
    Ben_rng.ClearContents

    Dim Emb_rng As Range
    Set Emb_rng = BEmbalagem.Range("A2:CT" & BEmbalagem.Cells(Rows.Count, 1).End(xlUp).Row)
    Emb_rng.ClearContents

    Dim Dikla_rng As Range
    Set Dikla_rng = BDikla.Range("A2:AV" & BDikla.Cells(Rows.Count, 1).End(xlUp).Row)
    Dikla_rng.ClearContents


    'Copy from Malharia Workbook

    Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Malharia Base.xls"

    LastRowMB = Workbooks("Malharia Base.xls").Worksheets("Malharia Base").Cells(Rows.Count, 1).End(xlUp).Row
    Dim Malha_base As Range
    Set Malha_base = Workbooks("Malharia Base.xls").Worksheets("Malharia Base").Range("A2:CN" & LastRowMB)

    MyCurrentWB.Worksheets("B-Malharia").Range("A2:CN" & LastRowMB).Value = Malha_base.Value
    Workbooks("Malharia Base.xls").Close

    'Copy from Beneficiamento Workbook

    Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Beneficiamento Base.xls"

    LastRowBB = Workbooks("Beneficiamento Base.xls").Worksheets("Beneficiamento Base").Cells(Rows.Count, 1).End(xlUp).Row
    Dim Ben_base As Range
    Set Ben_base = Workbooks("Beneficiamento Base.xls").Worksheets("Beneficiamento Base").Range("A2:CY" & LastRowBB)

    MyCurrentWB.Worksheets("B-Beneficiamento").Range("A2:CY" & LastRowBB).Value = Ben_base.Value
    Workbooks("Beneficiamento Base.xls").Close

    'Copy from Embalagem Workbook

    Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Embalagem Base.xls"

    LastRowEB = Workbooks("Embalagem Base.xls").Worksheets("Embalagem Base").Cells(Rows.Count, 1).End(xlUp).Row
    Dim Emb_base As Range
    Set Emb_base = Workbooks("Embalagem Base.xls").Worksheets("Embalagem Base").Range("A2:CT" & LastRowEB)

    MyCurrentWB.Worksheets("B-Embalagem").Range("A2:CT" & LastRowEB).Value = Emb_base.Value
    Workbooks("Embalagem Base.xls").Close

    'Copy from Dikla Workbook

    Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Diklatex Base.xls"

    LastRowDB = Workbooks("Diklatex Base.xls").Worksheets("Diklatex Base").Cells(Rows.Count, 1).End(xlUp).Row
    Dim Dikla_base As Range
    Set Dikla_base = Workbooks("Diklatex Base.xls").Worksheets("Diklatex Base").Range("A2:AV" & LastRowDB)

    MyCurrentWB.Worksheets("B-Dikla").Range("A2:AV" & LastRowDB).Value = Dikla_base.Value
    Workbooks("Diklatex Base.xls").Close

End Sub

I'm sorry if I was not clear enough, of course english is not my native language. Any doubts about my code or the whole idea feel free to ask questions.

Thanks in advance for any help guys!

Answer 1

I know this is quite old but I think for others who see this it may help them out in understand how to make their own VBA programs performance faster. Also, the code below is as efficient as I believe I can currently make it.

A few quick notes for improving performance in future development projects.

1. Avoid concatenation. Strings in general - across many programming languages - if you get down to assembly are slow as they are mainly used for comparisons with other strings.

2. Ranges are slow. try to use them as little as possible. They are used for gathering 2 dimensional arrays such as in the code below. Just debug to see whats happening on the lines with "data =".

3. Try opening excel files in 'read only' and 'update links' not on. In the code below i've also provided an example of this. However, if you start working with .csv files there is an even faster method to read data, but is quick unsafe and you should check the data before hand.

4. Apply ranges to sheets using the resize method in the cells and range methods. They are faster and more efficient when applying values.

5. Change the application things that the other people have already said. I won't go into why because they have already done a good job of this.

Hope this helps you out :)

    Public Const file As String = "C:\Users\marco.henrique\Desktop\Bases\Malharia Base.xls"
    Public Const file_2 As String = "C:\Users\marco.henrique\Desktop\Bases\Beneficiamento Base.xls"
    Public Const file_3 As String = "C:\Users\marco.henrique\Desktop\Bases\Embalagem Base.xls"
    Public Const file_4 As String = "C:\Users\marco.henrique\Desktop\Bases\Diklatex Base.xls"
    Sub CopiarBase() ' Const is faster for the compiler
        ' Saving the Sheets Previous state.
        Dim OldIntState As Boolean: OldIntState = Application.Interactive
        Dim oldCalState As XlCalculation: oldCalState = Application.Calculation
        Dim oldSUState As Boolean: oldSUState = Application.ScreenUpdating
        Application.Interactive = False
        Application.Calculation = XlCalculation.xlCalculationManual
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        'Setting Sheet Values
        Dim MyCurrentWB As Workbook: Set MyCurrentWB = ThisWorkbook
        Dim BMalharia As Worksheet: Set BMalharia = MyCurrentWB.Worksheets("B-Malharia")
        Dim BBeneficiamento As Worksheet: Set BBeneficiamento = MyCurrentWB.Worksheets("B-Beneficiamento")
        Dim BEmbalagem As Worksheet: Set BEmbalagem = MyCurrentWB.Worksheets("B-Embalagem")
        Dim BDikla As Worksheet: Set BDikla = MyCurrentWB.Worksheets("B-Dikla")

        'Clean all the cells - Workbook 1, Range clearing - faster to do Sheet.usedRange.clearContents,
        ' if your clearing all sheet data
        BMalharia.UsedRange.ClearContents
        BBeneficiamento.Range(BBeneficiamento.Cells(2, 1), BBeneficiamento.Cells(BBeneficiamento.UsedRange.rows, "CY")).ClearContents
        BEmbalagem.Range(BEmbalagem.Cells(2, 1), BEmbalagem.Cells(BEmbalagem.UsedRange.rows, "CT")).ClearContents
        BDikla.Range(BDikla.Cells(2, 1), BDikla.Cells(BDikla.UsedRange.rows, "AV")).ClearContents

        'Copy from Malharia Workbook
        Dim WB As Workbook: Set WB = Workbooks.Open(file, 0, 1) ' opening the file with out updating it and in read
        ' only. if you require either of the online documentation is useful. I Just assumed you don't require these
        ' things, this does make the program run faster.
        Dim WS As Worksheet: Set WS = WB.Worksheets("Malharia Base")
        data = WS.Range(WS.Cells(2, 1), WS.Cells(WS.UsedRange.rows.count, "CN")).value
        BMalharia.Cells(2, 1).Resize(UBound(data), UBound(data, 2)).value = data
        WB.Close False

        'Copy from Beneficiamento Workbook
        Set WB = Workbooks.Open(file_2, 0, 1)
        Set WS = WB.Worksheets("Beneficiamento Base")
        data = WS.Range(WS.Cells(2, 1), WS.Cells(WS.UsedRange.rows.count, "CY")).value
        BBeneficiamento.Cells(2, 1).Resize(UBound(data), UBound(data, 2)).value = data
        WB.Close False

        'Copy from Embalagem Workbook
        Set WB = Workbooks.Open(file_3, 0, 1)
        Set WS = WB.Worksheets("Embalagem Base")
        data = WS.Range(WS.Cells(2, 1), WS.Cells(WS.UsedRange.rows.count, "CT")).value
        BEmbalagem.Cells(2, 1).Resize(UBound(data), UBound(data, 2)).value = data
        WB.Close False

        'Copy from Dikla Workbook
        Set WB = Workbooks.Open(file_4, 0, 1)
        Set WS = WB.Worksheets("Embalagem Base")
        data = WS.Range(WS.Cells(2, 1), WS.Cells(WS.UsedRange.rows.count, "AV")).value
        BDikla.Cells(2, 1).Resize(UBound(data), UBound(data, 2)).value = data
        WB.Close False

        ' Restoring the Sheets State before execution
        Application.DisplayAlerts = True
        Application.Interactive = OldIntState
        Application.Calculation = oldCalState
        Application.ScreenUpdating = oldSUState
    End Sub

Answer 2

I usually turn screen update, Interactive and calculate off before doing anything to a workbook, then switch it back to their previous state at the end.

Dim oldInteractive As Boolean = Application.Interactive
Dim oldCalulation As XlCalculation = Application.Calculation
Dim oldScreenUpdating As Boolean = Application.ScreenUpdating
Application.Interactive = False
Application.Calculation = XlCalculation.xlCalculationManual
Application.ScreenUpdating = False

'Your code here

Application.Interactive = oldInteractive 
Application.Calculation = oldCalulation 
Application.ScreenUpdating = oldScreenUpdating

This will prevent calculations from being made while your code is running which can slow things down a lot. It's important to change Application.Calculation back to it's old value as it will stay the way you set it even after your code has finished, which can cause confusion.

Answer 3

I am not sure how much time you will spare, but I would suggest disabling the screen refresh when the macro is running, by adding

Application.ScreenUpdating = False

at the beginning of the sub (and obviously the same line with = True at the end)