Linking from Excel clone? 1

[Chotor]

Hi.

I have an application called PowerPlus Pro, which is basically Excel with plugins.
My ultimate goal is to read fields in Extra, lookup relevant values in PowerPlus Pro/Excel, and write those values back into Extra.

My first challenge is: How do make a connection between Extra and PPP?

 

[SkipVought]

Check out the GetObject Method. You can use the Google Search in Tek-Tips to find lots of info.

You can also check Extra Help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Chotor]

Thanks.

I can now connect to "Excel" and retrieve information.

Next step is LOOKUP.

In Excel, =LOOKUP("Canada", A5:A25, L5:L25) gives me what I want.

When I try to translate this into Extra, I get an error:

    Set LandRange = xlApp.activesheet.Range("A1:A25")
    Set PropRange = xlApp.activesheet.Range("L1:L25")

    lookup_value = xlApp.Application.WorksheetFunction.VLookup("Canada", LandRange, PropRange)

I get "Unable to get the Lookup property of the WorksheetFunction Class..."
Now, I understand partly what that means, but I don't know what to do with it!?

 

[SkipVought]

Your RANGE must include the entire range from A1:L25

    Set LandRange = xlApp.activesheet.Range("A1:L25")

    lookup_value = xlApp.Application.WorksheetFunction.VLookup("Canada", LandRange, xlApp.activesheet.Column(L1))

I would also recommend not using the ActiveSheet object. Rather, explicitly define the sheet of interest, like...

    Set LandRange = xlApp.[b]Sheets("YourSheetName")[/b].Range("A1:L25")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Chotor]

Thanks, Skip!

I am trying your code, but I get "no such property or method".

I've been playing around a bit and

    Set LandRange = xlApp.activesheet.Range("A1:A25")
    Set PropRange = xlApp.activesheet.Range("L1:L25")
    
    lookup_value = xlApp.Application.WorksheetFunction.Lookup("Canada", LandRange, PropRange)

gives no errors, but wrong result.

=VLOOKUP("Canada", A1:L25, 12, FALSE)

(12 for Column L). This gives the right result (I think) in Excel.

Is there a problem when column A contains text and column L contains numbers? I find I have major problems with LOOKUP/VLOOKUP even in Excel! Can't understand why such an easy request/formula is not working.


Thanks for the tip on activesheet. I will try to incorporate that.

 

[SkipVought]

Hi,

Should have looked closer at your code.

xlApp IS the Application...

    Set LandRange = xlApp.activesheet.Range("A1:L25")

    lookup_value = xlApp[s].Application[/s].WorksheetFunction.VLookup("Canada", LandRange, xlApp.activesheet.Column(L1))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Chotor]

Thanks, man! Got it working now.

For reference, I used

    lookup_value = xlApp.WorksheetFunction.VLookup("Canada", LandRange, 12, 0)

where 12 means Column L, and 0 means FALSE.

One learns something every day.

I'll be back with more questions shortly!