Outlook startup marco to refresh values in excel workbook

[kuraitori]

Good Morning Everyone,
I've checked the forums and most Excel-Outlook macros concern opening outlook from Excel and not vice-versa

I am using Outlook and Excel 2002,
I have the startup code for outlook so that every time I startup, outlook asks me if I've had my coffee yet

Please Login or Register  to view this content.

But for the life of me, I cannot seem to get it to open a particular workbook of mine



Here is the code I have thus far

Please Login or Register  to view this content.

Any pointers? It reads Usertype not defined at the first line of code.

[DonkeyOte]

Go to Tools -> References -- scroll through and ensure you "tick" the Microsoft Excel object library which will be v10 for 2002.

Then try again.

[Leith Ross]

Hello kuraitori,

It is probablely due to how you are accessing Excel with the GetObject function. If the pathname is a zero-length string (""), GetObject returns a new object instance of the specified type. If the pathname argument is omitted, GetObject returns a currently active object of the specified type. If no object of the specified type exists, an error occurs.

Since you have no pathname specified, Windows is looking for a running instance of Excel. If there is no running instance, an error will occur. To always be sure you have an application object to attach to , use either of the methods below:

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[DonkeyOte]

There are a few other issues in the code itself also, ie given the way you're using exlwb and exlSheet perhaps:

Please Login or Register  to view this content.

you may also want to save & close the file ?

Please Login or Register  to view this content.

and remember to release at the end

Please Login or Register  to view this content.

[DonkeyOte]

Hello kuraitori,

It is probablely due to how you are accessing Excel with the GetObject function. If the pathname is a zero-length string (""), GetObject returns a new object instance of the specified type.

Thanks Leith - for some reason I didn't know that... I guess because I'm lazy and don't read enough :-)

[kuraitori]

Thank you, I've implimented the changes,
New code is thus:

Please Login or Register  to view this content.

still generates an error though

Please Login or Register  to view this content.

Syntax error

Please advise

[Leith Ross]

Hello kuraitori,

Sometimes Excel, Word, and Outlook don't behave properly when late bound unless the application is made visible. Try this...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[DonkeyOte]

As mentioned in a prior post think about how you're defining the exlwb and exlSheet variables - I don't think in this instance visibility is the issue rather the issue lies with the 2 variables mentioned.

See if the revision below does what you want as:

Please Login or Register  to view this content.

You were also defining the worksheet using an incorrect variable (ie exlwkbk as opposed to the create variable which is exlwb)

I hope that helps.

[kuraitori]

Thank you guy,
Leith, your code generates a syntax error with the

Please Login or Register  to view this content.

DonkeyOte,
Yours seems to work, there is a complication however with what I am trying to acheive. The file it refreshing the query of becomes read-only when I try to open it up to confirm the changes, it also has saving complications, asking me if I want to save a copy of (and it is the 2nd instance of the workbook).

I think what I will do is have that workbook activate and then have a command button to update,save, and close that workbook from within excel...I would think that would be less problematic.

Thank you both very much for your help though, maybe I'll tinker with it and find out what the issues are.

[Leith Ross]

Hello Kuraitori,

I didn't enclose the Open arguments in parenthesis and forgot the Set statement. That line should be...

Please Login or Register  to view this content.

Sincerely,
Leith Ross