Transparently updating another workbook from the current workbook
 

Hi,

When the user clicks on a button I created (call it "update_a"), I
would like to:

1. Get the data from cell d48 (call it "index_a") and
cell g93 (call it "data_a") in the current sheet s1
in the current workbook w1.

2. Open another workbook w8 in the background.

3. In w8 go to sheet s6 and find the the "index_a" value
in column h.
4. If you cannot find "index_a" value in column h, then you
display a popup error message "index_a not found".

5. If you find "index_a" in h345 then assign g345 the
result of g345 - "data_a"

6. Close workbook w8 in the background.


Also, I would like to:
a. Ensure that workbook w8 is not visible while it
is being opened, updated and closed.

b. Interaction with workbook w8 is fast and completely
transparent to the user.


Please show me the VBA code to do all of the above.


Thanks.
Luther



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Transparently updating another workbook from the current workbook
 

Hi:

Try this pseudocode (untested):

Application.ScreenUpdating = False
Workbooks.Open "FullPath\w8.xls"
Set rngFound = Workbooks("w8.xls").Worksheets("s6").Range("H:H"). _
Find(ThisWorkbook.Worksheets(1).Range("D48"))
If rngFound Is Nothing Then
MsgBox ThisWorkbook.Worksheets(1).Range("D48") _
& "not found"
Else
rngFound.Offset(0, -1).Value = ThisWorkbook.Worksheets(1).Range("G93")
End If
Application.ScreenUpdating = True

Regards,

Vasant.


"lothario" wrote in message
...
Hi,

When the user clicks on a button I created (call it "update_a"), I
would like to:

1. Get the data from cell d48 (call it "index_a") and
cell g93 (call it "data_a") in the current sheet s1
in the current workbook w1.

2. Open another workbook w8 in the background.

3. In w8 go to sheet s6 and find the the "index_a" value
in column h.
4. If you cannot find "index_a" value in column h, then you
display a popup error message "index_a not found".

5. If you find "index_a" in h345 then assign g345 the
result of g345 - "data_a"

6. Close workbook w8 in the background.


Also, I would like to:
a. Ensure that workbook w8 is not visible while it
is being opened, updated and closed.

b. Interaction with workbook w8 is fast and completely
transparent to the user.


Please show me the VBA code to do all of the above.


Thanks.
Luther



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Transparently updating another workbook from the current workbook
 

Private Sub Update_a_Click()
Dim sh As Worksheet, sh1 As Worksheet
Dim wkbk As Workbook
Dim res As Variant
Dim bNotFound As Boolean
Application.ScreenUpdating = False
Set sh = ActiveSheet
Set wkbk = Workbooks.Open("C:\My Docs\w8.xls")
Set sh1 = wkbk.Worksheets("s6")
res = Application.Match(sh.Range("d48").Value, _
sh1.Columns(8), 0)
If Not IsError(res) Then
sh1.Columns(8).Cells(res, 7).Value = _
sh.Range("g93").Value
Else
bNotFound = True
End If
wkbk.Close SaveChanges:=True
Application.ScreenUpdating = True
If bNotFound Then
MsgBox sh.Range("d48").Value & " not found"
End If

End Sub

--
Regards,
Tom Ogilvy



lothario wrote in message
...
Hi,

When the user clicks on a button I created (call it "update_a"), I
would like to:

1. Get the data from cell d48 (call it "index_a") and
cell g93 (call it "data_a") in the current sheet s1
in the current workbook w1.

2. Open another workbook w8 in the background.

3. In w8 go to sheet s6 and find the the "index_a" value
in column h.
4. If you cannot find "index_a" value in column h, then you
display a popup error message "index_a not found".

5. If you find "index_a" in h345 then assign g345 the
result of g345 - "data_a"

6. Close workbook w8 in the background.


Also, I would like to:
a. Ensure that workbook w8 is not visible while it
is being opened, updated and closed.

b. Interaction with workbook w8 is fast and completely
transparent to the user.


Please show me the VBA code to do all of the above.


Thanks.
Luther



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Transparently updating another workbook from the current workbook
 

Thanks guys.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/