Open a workbook and get data from it

[Peter]

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?

 

[Don Guillett]

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

 

[JLGWhiz]

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]

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?

 

[JLGWhiz]

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.

 

[Don Guillett]

application.screenupdating=false
code
reset to true

or, use formula links to closed file.

 

[Peter]

I'm able to find what I need, just having some issues with the syntax for
coping a row of data from the source sheet to the destination.

Here is what I've got so far...

Sub FindStuff()
Dim wbkthis As Workbook
Dim shtthis As Worksheet
Dim rngThis As Range
Dim rngFind As Range
Dim firstAddress As String
Dim addSelection As String
Dim cnt
Dim Workbook
Dim Worksheet
Dim Xobj
Dim SrcRange As Range


Set wbkthis = ThisWorkbook
Set shtthis = wbkthis.Worksheets("UserList")
Workbooks.Open Filename:="c:\UserInfo.xls", ReadOnly:=True
Set Xobj = ActiveWorkbook.Sheets("owssvr(1)") 'owssvr is in userinfo.xls
Windows("UserInfo.xls").Visible = False


Set SrcRange = Xobj.Range("B02", "B275")

With SrcRange
Set rngFind = .Find("username1")

cnt = 0
If Not rngFind Is Nothing Then
firstAddress = rngFind.Address 'Take a note of where we first
found it
addSelection = addSelection & rngFind.Address & "," 'Add the
cell's range to our selection
rngFind.EntireRow.Copy
Workbooks(wbkthis).Sheets(shtthis).Range("H2") '(attempt to copy)
cnt = cnt + 1
End If
End With
Debug.Print cnt
Workbooks("UserInfo.xls").Close SaveChanges:=False
End Sub



The idea, is in the userlist workbook(ThisWorkBook) I've got a list of user
namea, and I'm searching for the match over in userInfo.xls, then I'd copy
that row, and paste it in the workbook. So if I want to pull information
about username1, which happens to be at H3 in this example, I search
UserInfo.xls, when I find the match, I copy the row and paste it in the cell
next to the username, H3 H4 etc etc.