Import Excel Data

sandramac used Ask the Experts™
Hello all,

I have a an excel file called "Overall", that when you open it up I need it to go to another excel file located at \\data\track\sab.xls and get the data under sheet "MET" range from A3 to G the last row before the words CAO appear.  For example if the CAO appears in row 32, then copy the range from A3 to G31, if say CAO is on row 44, then copy from A3 to G43.  Then take that data and copy into the "Overall" excel file sheet "TAC" cell A3.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
place this macro code in the "ThisWorkbook" section of the VBA editor in the "Overall" excel file
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim result As Range

    '-- open other excel file
    Set ws = Workbooks.Open("d:\temp\import_test_sab.xls").Sheets("MET")
    '-- search for CAO
    Set result = ws.UsedRange.Find("CAO")
    If result Is Nothing Then MsgBox ("CAO could not be found"): End
    '-- copy data
    ws.Range("A3:G" & result.Row - 1).Copy ThisWorkbook.Sheets("TAC").Range("A3")

    '-- close other excel file
    Set result = Nothing
    Set ws = Nothing

End Sub

Open in new window

oh, and update line 6 to
Set ws = Workbooks.Open("\\data\track\sab.xls").Sheets("MET")

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial