Match Cell content in another workbook question

[Nigel]

Hi,
Here is my typical senario:
Quote book is open and i run a macro that gets the cell content from the
following cells( C2, C4, C5,G1)
These are all passed to a variable.
I need to open another workbook( if not already open), search a range of
B5:B1000 and find a amtching number that is equal to C2 in first book.

example
( job number = C2(2256))
need to find 2256 in other workbook in range B5:B1000

Once this has been found, i need to insert the other cell contents into the
cells on the right of this first cell.

Example
B D E F ( columns)
(2256 £12,000 £3500 £4000 ( Cell contents )
C2 C4 C5 G1 ( Range in first workbook )

I am really struggling with a routine to open the book if it is not open or
switch to it if it is open ( yo prevent open read only ) search the range for
a matching value, and inserting the cell contents. This is going to ave me
hours if not days & weeks of work.

any help greatly appreciated.

Regards,

Nigel

 

[David Lloyd]

Nigel:

To see if the workbook is open you can iterate through the Worbooks
collection examining the Name property versus the workbook name you are
seeking. If you do not find it in the Workbooks collection, then you can
open the workbook using the Workbooks.Open method. If you find the workbook
in the Workbooks collection, you can call the Activate method on the
workbook, and then use the Sheets and Cells collections to search, etc.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi,
Here is my typical senario:
Quote book is open and i run a macro that gets the cell content from the
following cells( C2, C4, C5,G1)
These are all passed to a variable.
I need to open another workbook( if not already open), search a range of
B5:B1000 and find a amtching number that is equal to C2 in first book.

example
( job number = C2(2256))
need to find 2256 in other workbook in range B5:B1000

Once this has been found, i need to insert the other cell contents into the
cells on the right of this first cell.

Example
B D E F ( columns)
(2256 £12,000 £3500 £4000 ( Cell contents )
C2 C4 C5 G1 ( Range in first workbook )

I am really struggling with a routine to open the book if it is not open or
switch to it if it is open ( yo prevent open read only ) search the range
for
a matching value, and inserting the cell contents. This is going to ave me
hours if not days & weeks of work.

any help greatly appreciated.

Regards,

Nigel

 

[Nigel]

hi,
i dont fully understand. Is there a workbook collection code i can use? is
it workbook.collection?

Sub workbookfind()

if workbook.collection = ("Register") then
Active.workbook = Register

end if
exit sub

if workbook.collection <> ("register") then
workbook.open = ("register")

end if

end sub

I am not so good with vb as i have only been doing this for about 5 week ish.

is this about right??

Regards,

nigel

 

[Alok]

Nigel,

Workbooks is called a workbook collection
Worksheets is a Worksheet collection

so Workbooks("A.xls") refers to Workbook "A.xls"
similarly you can use a 1 based index to get to the Workbooks collection.
Hence Workbooks(1) is the first workbook in the set of workbooks open. Since
it is a collection it has properties like Count that return the number of
workbooks open.
Hence Workbooks(Workbooks.Count) will return a reference to the last
workbook in the collection.

Your code is nearly correct. It should be
Function Workbookfind( Byval sWkbkName as String) as Boolean

Dim wb as Workbook
for each wb in Workbooks
if wb.Name = sWkbkName then
Workbookfind = True
exit function
end if
Next wb
exit Function

Hope this helps.

Alok Joshi

 

[Nigel]

Hi Alok,
i don't quite understand. My workbook is called Quote Register so how do i
get this routine to select the book if it is open, or open it if it is
closed. i just keep getting errors.

Regards,

nigel

 

[Alok]

Nigel,
I gave you the code for checking if the workbook is open. You use it in a
subroutine as follows

Sub SelectOrOpen()
if WorkbookFind("Quote Register.xls") then
Workbooks("Quote Register.xls").Activate
else
Workbooks.Open ".....full path\Quote Register.xls"
end Sub

Alok Joshi