Thread: Excel pushing to the front

Hello John,

Thanks, I downloaded the file successfully. I will let you know what I find.

Hi John,

I had a quick look and noticed that the code is very redundant since you are trying to open a set of workbooks in a specific order. You should be able to set up these file names into an array and loop through them instead of repeating code. It won't resolve the speed issue you are having but it might be a start so that people (including yourself) can easily read and make changes to the code.

Here's an example modified from a post by JBeaupcaire:

Option Explicit

Sub Array_WB_WS()

Dim wbARR As Variant, wsARR As Variant
Dim wbDATA As Workbook
Dim fPath As String, a As Integer

  'remember the final \ in this string, the path to all the files
    fPath = "C:\2010\MyFiles\"

  'an array of the non-changing sheet names, in a specific order
    wbARR = Array("Excel Workbook_1.xls", _
              "Excel Workbook_2.xls", _
              "Excel Workbook_3.xls", _
              "Excel Workbook_4.xls", _
              "Excel Workbook_5.xls")

  'an array of the destination sheet tab names, in the SAME order as above (not currently used in example)
    wsARR = Array("Sheet1", "Sheet2", "Sheet3")
              
  'Loop through each item in the wbARR, open file, copy data to matching sheet
    For a = LBound(wbARR) To UBound(wbARR)
        Set wbDATA = Workbooks.Open(fPath & wbARR(a))
            With wbDATA.Sheets(1)
                ' More code here
            End With
        wbDATA.Close False
    Next a
End Sub

You can also modify this to keep the file names in a worksheet instead of hard-coding it into the vba code. For future purposes that would make sense since the individuals (or file names) may change over time.

Hope this helps.

abousetta

Hi John

I believe you're owed an apology...so, apologies from me if not from the Forum. You and I have worked on several projects together and I find your willingness to explore and learn to be refreshing.

The European approach to things can be somewhat "put offish". I find Poster #10 to be extremely intelligent when it comes to things VBA...albiet...the code he provides is often indecipherable to me. Perhaps it is to you.

Self taught is what I am...I don't find it so bad...I learn new things every day from the likes of Mr. Ross and many others such as him who are willing to give their time and share their knowledge with us "self taught and still learning". My wish is that Mr. Ross were in my time zone.

Keep exploring...learning...asking questions. Soon, I'll be asking you questions.

Hello Jomili,

My suspicion lies with the pivot table routines. I don't mix pivot tables and VBA code. They don't play well together. I am not sure yet how we can test this out to either validate or disprove it.