Open a workbook and get data from it
in [Programming]
|
From: Peter on 30 Mar 2010 17:33 Hello I'm working a project where I've got a list of names in one spreadsheet, and I need to pull corrasponding data from another spreadsheet. The concept is simple.... get a name from spreadsheet1, then go into spreadsheet2, find that username, and copy the row over to spreadsheet1. At least that is how you do it manually. In excel VBa, from one workbook, how do I a open another workbook and worksheet then get the correct data i need?
From: Don Guillett on 30 Mar 2010 18:19 Modify to suit Sub OpenFileFindNameCopyToThisFile() Workbooks.Open Filename:="C:\sourcefoldername\sourcefilename.xls" Set myfind = ActiveWorkbook.Sheets("sourcesheetname").Columns("B") _ .find(What:="leslies", LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not myfind Is Nothing Then myfind.EntireRow.Copy _ Workbooks("destinationfilename.xls").Sheets("sheet36").Range("a17") ActiveWorkbook.Close SaveChanges:=False End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Peter" <noMorespam(a)MSUK.com> wrote in message news:umc%23uCF0KHA.4752(a)TK2MSFTNGP04.phx.gbl... > Hello > > I'm working a project where I've got a list of names in one spreadsheet, > and I need to pull corrasponding data from another spreadsheet. The > concept is simple.... get a name from spreadsheet1, then go into > spreadsheet2, find that username, and copy the row over to spreadsheet1. > At least that is how you do it manually. > > In excel VBa, from one workbook, how do I a open another workbook and > worksheet then get the correct data i need? > > >
From: JLGWhiz on 30 Mar 2010 18:45 This is psuedo code, intended to illustrate how to do what you want. There are propbably more sophisticated ways but this is based on the description provided of the task. Dim wb1 As WorkBook, wb2 As Workbook Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As long, c As Range, fN As Range Set wb1 = ThisWorkbook Set wb2 = Workbooks.Open FileName:='path and file to open Set sh1 = wb1.ActiveSheet Set sh2 = wb2.Sheets('Sheet name containing data in second wb2) lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row 'Assumes Col A with names lr2 = sh2 Cells(Rows.Count, 1).End(xlUp).Row 'Change Col if req'd For Each c in Range("A2:A" & lr1) If Not c Is Nothing Then Set fN = sh2.Range("A2:A" & lr2).Find(c.Value, LookIn:=xlValues) If Not fN is Nothing Then fN.EntireRow.Copy sh1."need to specify destination range here 'If you only want to add supplemental data to the existing 'row in sh1, then you would not use EntireRow as the range 'to copy. Say you only need five columna to the right of 'the found fN, then you could do: 'fN.Offset(0, 1).Resize(0, 5),Copy sh1.Range("B" & c.Row) 'Which would put the copied data immediately to the right 'of the source search item. End If End If Next "Peter" <noMorespam(a)MSUK.com> wrote in message news:umc%23uCF0KHA.4752(a)TK2MSFTNGP04.phx.gbl... > Hello > > I'm working a project where I've got a list of names in one spreadsheet, > and I need to pull corrasponding data from another spreadsheet. The > concept is simple.... get a name from spreadsheet1, then go into > spreadsheet2, find that username, and copy the row over to spreadsheet1. > At least that is how you do it manually. > > In excel VBa, from one workbook, how do I a open another workbook and > worksheet then get the correct data i need? > > >
From: Peter on 30 Mar 2010 20:56 Thanks for the help that works, just one more question. When I open the second workbook, the one that I'm coping data from Workbooks.Open Filename:="c:\UserInfor.xls" the workbook actually opens and is visible to the user. Is there a way to hide it so a person can't see it? "Peter" <noMorespam(a)MSUK.com> wrote in message news:umc%23uCF0KHA.4752(a)TK2MSFTNGP04.phx.gbl... > Hello > > I'm working a project where I've got a list of names in one spreadsheet, > and I need to pull corrasponding data from another spreadsheet. The > concept is simple.... get a name from spreadsheet1, then go into > spreadsheet2, find that username, and copy the row over to spreadsheet1. > At least that is how you do it manually. > > In excel VBa, from one workbook, how do I a open another workbook and > worksheet then get the correct data i need? > > >
From: JLGWhiz on 30 Mar 2010 23:40
You can hide individual sheets in the workbook. As an alternative, you can close the workbook after you have copied your data. Open and close it as needed. Unless it is a very large workbook, it doesn't take much longer to close and open than to hide and unhide the sheets. "Peter" <noMorespam(a)MSUK.com> wrote in message news:uIUz4zG0KHA.4636(a)TK2MSFTNGP06.phx.gbl... > Thanks for the help that works, just one more question. > > When I open the second workbook, the one that I'm coping data from > Workbooks.Open Filename:="c:\UserInfor.xls" the workbook actually opens > and is visible to the user. Is there a way to hide it so a person can't > see it? > > > "Peter" <noMorespam(a)MSUK.com> wrote in message > news:umc%23uCF0KHA.4752(a)TK2MSFTNGP04.phx.gbl... >> Hello >> >> I'm working a project where I've got a list of names in one spreadsheet, >> and I need to pull corrasponding data from another spreadsheet. The >> concept is simple.... get a name from spreadsheet1, then go into >> spreadsheet2, find that username, and copy the row over to spreadsheet1. >> At least that is how you do it manually. >> >> In excel VBa, from one workbook, how do I a open another workbook and >> worksheet then get the correct data i need? >> >> >> > > |