Excel Gurus = want a macro in 1 workbook to get info from another workbook = Read please

[harry]

Hi,

i have 2 workbooks, 1 is password protected so i can't make other macro's on
it. Using excel 2002 btw + winxp all sp's etc.

This is want i want to do:

1. Password protected workbook has changing cell values when macros in it
are running. Call this workbook ="test.xls", it's in my "c:\mystuff"
directory
2. I want to be able to do this when cell A10 in "test.xls" in a worksheet
called "output" is = 10000 then
3. I want to automatically email once, using outlook express, certain cell
values from "test.xls" in the "output" worksheet.
4. since the workbook is locked i can't add other macro's to "test.xls", so
i thought ok i'll just make another workbook called "mail.xls" also in my
"c:\mystuff" directory and transfer the "test.xls\output" cell reference A10
and whatever other ones i need to email to the new workbook
"mail.xls\sheet1".

5. How i can i do this, what is the exact code that i can use to make it
happen?

6. This is what i've tried, the mail part works however when the value
changes to 10000 in test.xls and then also in mail.xls it doesn't work, ie
if i actually go into the mail.xls\sheet1 and do a change like delete or add
something then the macro will fire, otherwise it won't do anything; is
Worksheet_Change the right 1 to use to fire the macro when it's changed
automatically by a cell reference to another workbook test.xls?????

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10").Value = 10000# And Range("B2").Value <> "Contacted" Then
Call Mail_Text_in_Body
Range("B2").Value = "Contacted"
End If
End Sub

Sub Mail_Text_in_Body()
'In 2002 I can go to 600-700 characters
Dim Msg As String, URL As String
Dim Recipient As String, Subj As String, HLink As String

Recipient = "(e-mail address removed)"
Subj = "mail test"
Msg = Range("a6")


HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s", True
End Sub

7. so then i thought ok, mabye i should reference the 10000 directly from
the test.xls sheet but i don't know how to do that part? = this doesn't
work

If Workbooks("C:\mystuff\LottoStatisticsXLp.xls Output").Range("A10").Value
= 10000# Then




Thanks Gurus

 

[harry]

let me fix , the test.xls workbook ISN'T password protected but the Macro's
are password protected. hence can't add a macro to it

 

[Dave Peterson]

I think that if you can't get to the project, then you're only choice is to have
an application event lurking in the background checking every change you make to
every cell in every worksheet.

If it sees a change to a cell that's not in the correct workbook, or not on the
correct worksheet or not in A10, then it just quits.

If you think you like this idea, then
create a new workbook
alt-F11 to get to the VBE.
find your new workbook/project and select it.
then Insert|Class module from the menubar.

Paste this in that class module code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If LCase(Sh.Parent.FullName) _
<> LCase("C:\mystuff\LottoStatisticsXLp.xls") Then Exit Sub
If LCase(Sh.Name) <> LCase("output") Then Exit Sub

If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub

If Target.Value = 10000# _
And Range("B2").Value <> "Contacted" Then
Call Mail_Text_in_Body
Application.EnableEvents = False
Range("B2").Value = "Contacted"
Application.EnableEvents = True
End If

End Sub

Note that it's pretty specific about the name/location of the workbook. If you
wanted you could just look at the .name (and toss the drive/folder bits) like:

if lcase(sh.parent.name) <> lcase("lottostatisticsxlp.xls") then exit sub


Now doubleclick on the ThisWorkbook object. Paste this into the code window:

Option Explicit
Dim myWKSChange As Class1
Private Sub Workbook_Open()
Set myWKSChange = New Class1
Set myWKSChange.xlApp = Application
End Sub
Private Sub workbook_close()
Set myWKSChange.xlApp = Nothing
End Sub

This tells excel to look for changes in every worksheet.

Then add your code for mail_text_in_body to a General module.

Save this workbook, close it and reopen it (or just run the workbook_open code).

When I tested, I commented out the part that really sent the message:
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s", True


If you want to learn more about application events, then Chip Pearson has some
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm


======
Another alternative would be to go back to the developer and bribing him/her
with coffee(?) to unprotect the project so that you could add your code.

 

[harry]

wow, for an idiot like me that's alot, but i tried to follow it and after
compiling it got this error message=

for=
Dim myWKSChange As Class1

"compile error:
User-defined type not defined

 

[Dave Peterson]

When you inserted the Class module, what was it called?

(You did insert the class module, right? Not just module, not a userform,...)

If it wasn't named Class1, then click on it and hit F4 and rename it to Class1
in the properties window.

 

[harry]

how bout i email you the small excel file and you can see what i did?

 

[Dave Peterson]

How about I email you my test workbook.

If that's not ok, then delete it. (It's on it's way.)

how bout i email you the small excel file and you can see what i did?

 

[harry]

email it to here =
(e-mail address removed)
thanks

How about I email you my test workbook.

If that's not ok, then delete it. (It's on it's way.)

 

[Dave Peterson]

You've got two on the way, now.

email it to here =
(e-mail address removed)
thanks