Solved: Open 1st workbook from userform in workbook 2

[Gil]

Hello
Not being very good at this I need some help.

I have 2 workbooks named Workbook 1 & Workbook 2. Using command buttons on a user form in book 1, I want to open book 2. I dont want book 2 to be visible when openned (I will be doing a search and copy of book 2 from a cell in book 1) can this be hidden or at least minimised while it is open.When I have finished I want to close book 2 with another command button from book 1.
The User form, command buttons and associating code to them I can cope with.
I need help with the vba code please to execute the opening and closing.


Many thanks in anticipation

[lucas]

Will you be searching and copying from book 2 using code?

You can open and close a workbook like this:

Option Explicit
Sub OpenBook2()
Dim fPath As String, fName As String
'You must change this line to your desired folder location!
fPath = ActiveWorkbook.Path & "\"
' fPath = "f:\Temp\"
'Change Book2.xls to the file to open
fName = "Book2.xls"
On Error Resume Next
'Workbooks(fName).Activate use this code to activate an open workbook
Workbooks.Open fPath & fName
'If Excel cannot activate the book, then it's not open, which will
' in turn create an error not of the value 0 (no error)
If Err = 0 Then
Workbooks("Book1.xls").Activate
Exit Sub 'Exit macro if no error
End If
Err.Clear 'Clear erroneous errors

End Sub
Sub CloseBook2()
Workbooks("Book2.xls").Close False
End Sub

[Gil]

Hello Lucas
Many thanks for the solution to opening and closing the workbook 2. The answer was spot on.
As you asked ,the next part is to enter into a cell in book1 example A1 enter 2468, on pressing the enter key a search is made of book2 for 2468 and is found in book2 sh1 H27. It then needs to copy the data in book2 E27 & f27 and paste in book 1 A4 & A5.
I hope that is clear and look forward to another result.

Many thanks
Gil

[Aussiebear]

Gil, A coupleof questions to helpclear up this matter,
1.Will the value that you are chasing in Book 2 be in the same column or row?
2. If you search for another value, will the copy and destination still be the same?
3. If the destination range is to be the same, does the code require the new data overwrite the existing data or move the existing data down the sheet, or even write the data to the next blank row?

Please consider posting a sample workbook with two sheets,one named Book1 and the second, Book2 so we can see what you are looking at. Each sheet should look like your existing workbooks.

[Gil]

Hello Aussiebear

Thankyou for the interest,In answer to your questions

1. No. The value I am chasing could be anywhere in book2 on any sheet
2. Yes. The copy and destination will still be the same
3. Yes. The destination range is to be the same and the data to be overwritten.

[GTO]

Greetings Gil,

Steve already showed opening and closing the source wb, this would just be a different take on keeping the opened wb hidden. I tacked in searching through all the sheets as well.

In a Standard Module (in Workbook 1)


Option Explicit

Sub exa()
Dim _
XL As Excel.Application, _
xl_wbSource As Workbook, _
xl_wks As Worksheet, _
xl_rngValFound As Range

'// Change path and source wb name to suit //
Const strPath As String = "D:\2010\2010-02-26\2 Sample workbooks\"
Const strSourceName As String = "Workbook 2.xls"

'// Create a secondary instance of Excel, keeping it hidden. //
Set XL = New Excel.Application
With XL
.Visible = False
On Error GoTo NoBook
Set xl_wbSource = .Workbooks.Open(strPath & strSourceName, False, True)
On Error GoTo 0

'// Look for our desired val in all the worksheets of source. If found, //
'// grab vals from offset of where found. //
For Each xl_wks In xl_wbSource.Worksheets
Set xl_rngValFound = RangeFound(xl_wks.Cells, _
ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, _
, xlValues, xlWhole, xlByRows, xlNext)
If Not xl_rngValFound Is Nothing Then
ThisWorkbook.Worksheets("Sheet1").Range("A4:B4").Value = _
xl_rngValFound.Offset(, -3).Resize(, 2).Value

Exit For
End If
Next
'// If we didn't find the val in any of the sheets, tell user... //
If xl_rngValFound Is Nothing Then
MsgBox ThisWorkbook.Worksheets("Sheet1").Range("A1").Value & _
Chr(32) & "not found.", 0, vbNullString
End If
'// quit the added instance. //
.Quit
End With
Exit Sub
NoBook:
MsgBox strPath & strSourceName & Chr(32) & "does not exist.", 0, vbNullString
XL.Quit
End Sub

Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

Hope that helps,

Mark

[Gil]

Thank you GTO
Your submission works well and not being ungratefull I would like to keep the 2 operations seperate. That way if I want to vary the entry cell I can. The destination range will still be the same.

Many thanks Gil

[GTO]

Thank you GTO
Your submission works well and not being ungratefull I would like to keep the 2 operations seperate. That way if I want to vary the entry cell I can. The destination range will still be the same.

Many thanks Gil

I am not sure what you mean by '2 operations' and 'entry cell'? As I took it, you want to be able to plug a number into a cell (let's say A1), press a button, and find/import vals offset from the other wb.

Is that close?

Mark

[Gil]

Hello GTO
What I mean by 2 operations is
1. To open Workbook 2 from Workbook 1

Initial request to VBA Express Forum Achieved

Anticipating a question of what next due to my bracketed comment in my initial request for help
(I will be doing a search and copy of book 2 from a cell in book 1)

2.The next part is to enter into a cell in book1 example A1 enter 2468, on pressing the enter key a search is made of book2 for 2468 and is found in book2 sh1 H27. It then needs to copy the data in book2 E27 & f27 and paste in book 1 A4 & A5.

If I can get the code for the second part then the thread will be closed.

Many thanks and sorry to be a pain.
Gil

[GTO]

...
2.The next part is to enter into a cell in book1 example A1 enter 2468, on pressing the enter key a search is made of book2 for 2468 and is found in book2 sh1 H27. It then needs to copy the data in book2 E27 & f27 and paste in book 1 A4 & A5.

...

Many thanks and sorry to be a pain.

Greetings Gil,

You are not being a pain, I am just having a hard time following. Thank you for the clarification, if we are on the same page now, you are just asking how to get entering a new val in A1 to call the macro.

If that is correct, try:

In Sheet1's Worksheet Module:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Address = "$A$1" Then
Call exa
End If
End Sub


BTW, I think that you should experiment a bit, and try Steve's method as well. It would run faster than starting a new instance, and I do not think the user would be much (if any) aware of the second workbook.

Hope that helps,

Mark

[Gil]

Hello GTO
Many thanks for that addition,it works well and I do appreciate your help.
However it does leave the opening, search and close in one operation.This method may not have the flexibility I think I will need in the future.
For the time being I will use Lucas's 2 pieces of code for opening and closing my workbook.
The extra I need now is the search of workbook2 from workbook1.

If you or anyone else can help I thank you in advance.
Gil
In the message after your code what or who is BTW.

[Gil]

Hello
My initial request has been fully met with some usefull additions.
Many thanks to all who helped me. I have learnt a few bits & pieces that I am experimenting with.
Gil