Update Excel Workbook from another 1

[onpnt]

Quick questions. I've looked around and still am in the process but I'm not finding the answer easily so I thought I would post the question also.

I need to update an Excel file from the contents of another Excel file.

Both Excel files contain only one worksheet but the names of the sheets are different and not sheet1,sheet2 etc..
I know you can reference the sheet by index so that is not a question. Basically I need to run down the contents of say column B in workbook1.sheet1 and find a valid numeric (currency in this case) value. If I find one I then need to grab the value on that row in column A which holds the material number. That number is then what I need to attempt to match in workbook2.sheet1 in column A. If I iterate through column A in workbook2.sheet1 and find a material number matching I must take the value in column B of workbook2.sheet1 and update workbook1.sheet1 column B which was the first numeric value I found. *sigh* hopefully all the this1, that2, this2, this1 made sense.

I know how to open workbooks via Workbooks.Open but sense I need multiple workbooks open at once would I then do a

Set workbook1 = Workbooks("Excel1.xls")

?

second question is I see a number of forms of looping through columns but the one sheet I have a problem with is the first one which has many empty cells in the mix of the data I need to update. Are there functions (IsEmtpy) etc.. that will assist me in navigating through these without going into a wild script due to end of file issues. I'm not sure how Excel deals with the last cell or is there a last cell?

I guess I'm asking for some assistance in putting together the methods I should use and more so the functions available to me to get this task done quicker pretty much without spending hours reading up on VBA references. This is probably going to be a one time deal and my VB6 is rusty and I've never touched VBA code.

Thanks for the assistance!


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[CautionMP]

onpnt,

I know how to open workbooks via Workbooks.Open but sense I need multiple workbooks open at once would I then do a...

Yes, sort of. If you are running the macro from one workbooks you will need a single workbook object that refers to the second workbook. If you are running this from a third workbook you will need two workbook objects.

but the one sheet I have a problem with is the first one which has many empty cells in the mix of the data I need to update.

You can use the [tt]UsedRange[/tt] property of a worksheet to limit the number of rows your code looks at (instead of doing all 65,536 rows in a worksheet).

Do you have a starting point (a.k.a. what have you tried)?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[onpnt]

Thanks CautionMP for the input

I read up a bit late last night on referencing the books as objects and got that down I believe as such

    Set wBook_IBG = Workbooks.Open("File1.xls")
        If wBook_IBG Is Nothing Then
            Set wBook = Nothing
            On Error GoTo 0
        Else
            Set wBook = Nothing
            On Error GoTo 0
        End If

    Set wBook_CS = Workbooks.Open("File2.xls")
        If wBook_CS Is Nothing Then
            Set wBook = Nothing
            On Error GoTo 0
        Else
            Set wBook = Nothing
            On Error GoTo 0
        End If
        
    Dim wSheet_CS As Worksheet
    Set wSheet_CS = wBook_CS.ActiveSheet
    Dim wSheet_IBG As Worksheet
    Set wSheet_IBG = wBook_IBG.ActiveSheet
        
    MsgBox (wSheet_CS.Cells(1, 1).Value)
    MsgBox (wSheet_IBG.Cells(195, 8).Value)

For a test which worked the way I expected it to and gives me pretty much the main thing I was unsure of. I'll look into the UsedRange property now and post back what I get going before throwing more questions out there.

Thanks and I appreciate the input


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

    MsgBox (wSheet_CS.UsedRange.Rows.Count)
    MsgBox (wSheet_IBG.UsedRange.Rows.Count)

Thank you. That returned the exact value I need to finish the project I believe.

Apologies for the initial post was confusing.


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811