Map/Link an Excel cell to VFP field value

[yustevef]

I've learned how to build a .DLL from VFP (9.0) that accepts a customer # and return the name of the customer. And I know how to register the .DLL. But I'm having a hard time with VB code syntax in setting up the macro in Excel to accomplish my goal.
The objective is for each cell under the Customer name column on the sheet to show the VFP value that corresponds to the customer # under the Customer # column on the sheet. The name cells are only for display, no editing or posting back to the VFP table. Is this doable ?
Any help is deeply appreciated.

Steve

 

[Mike Lewis]

Hi Steve,

I'm not an expert in VB, but I think code similar to the following might get you started.

Assume your VFP project is Getlib, and this contains a class called GetData.

Assume also that GetData has a method called GetCustomer(). It accepts the customer number as a parameter and returns the customer name.

In Excel's VBA editor, insert a module. Click on the module window and insert a procedure. Type code similar to the following in the procedure window:

Set oTest = CREATEOBJECT("Getlib.GetData")
Range("A2").Value = oTest.GetCustomer(Range("A1").Value)

This will take the customer number in cell A1, and insert the name in cell A2.

I hope this will get you started.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[yustevef]

Hi Mike,
Thanks for the reply.
I understand the code. My questions are:
1. would this code automatically populate cell A2 ? Does the user have to do something ?
2. How do I apply this code to all cells under column A ?
Regards,

Steve

 

[pthalacker]

1. would this code automatically populate cell A2 ?

Yes

Does the user have to do something ?

If the code is in a macro, something has to invoke the macro. I assume that would be the user.

2. How do I apply this code to all cells under column A ?

I am no expert in VBA either, but I assume it would use a FOR loop to cycle through all the cells in a range such as A2:A22

pamela

 

[Mike Lewis]

Steve,

Pamela has given you the same answers that I would have.

My code was only intended to give you the general idea. I'm not able to write the whole thing for you. You'll obviously need to put the second line (not the whole thing) into some form of loop.

I suggest you read up on the basic syntax of VBA, or alternatively ask for more details in a VBA-related forum.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[yustevef]

Pam & Mike,
Thanks for all your input and help.
I don't want to sound to be a lazy person; I've gone through the Excel help and googled all I can on this subject without success. And that is how I came across an earlier thread by Mike and led me to this forum.
I think I can figure out the VBA syntax on my own but it is the part that deals with Excel that really gets me. For example, what is the syntax to designate the current cell ? In Mike's example Range("A2").Value gives me cell A2; would Range("RC").Value give me the current cell ? I tried but it does not work.
What I'm trying to accomplish is to formulate/define or otherwise mark a cell (and all cells under a given column) so that the corresponding VFP field value (customer name in this case) will show automatically, without user intervention preferably. Is this possible ?

Thanks again

Steve in Houston

 

[TamarGranor]

Check out the ActiveCell property.

Tamar