Excel 2000 get external data

[Katy44]

Hi,

I have set up a data source and a query in Excel to get the result into Excel. I also want to pass in parameters that come from cells on the Excel sheet, and that works fine. However, is there a way to get these parameters to change, as a value would change if it's a relative value?
e.g. use value from this cell: A1
....drag down.... B1
C1 etc...

I hope this is clear

 

[DPlank]

Katy,

We'll need more information than that.

What are you trying to achieve with this? What data are you pulling? Where from? What database?



Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[SkipVought]

The parameter cell is a FIXED reference. However, it can contain a formula that can return different values. Or it can be assigned a value by a control or by a macro.

But thats the whole reason for a parameter query -- to supply the query criteria with different values.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Katy44]

Thank you both for your replies.
SkipVought I'll try to think if there is a way that I can use a formula to do what I want. All I basically want to achieve is to make it a relative (is that the right word) reference.

The set up is that we have a standard report in Excel. Down the side is a list of values. so A1..A125 (say) has a list of customer surnames. I want, in B1..B125 to fill inthe customer's first name for each customer. To do this in cell B1 I set up the query that pulls the data from the database and pass in a parameter of A1. This works fine. Now, when I copy the formula (for want of a better word) down, all the results are as if A1 was passed in. I can go into each one and change the parameter to point to the right cell, but this seems such an obvious thing to want to do to me that I thought there would be an easy way.

 

[SkipVought]

How volatile is your list in column A?

The paramter query is more suited to, for instance, displaying a report for a specified customer, or reporting invoice details.

What you want to do is more like a FUNCTION that returns a value based on a value, something like this...

Function GetNomen(sPN As String)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    cnn.Open Workbooks("Personal.xls").Sheets("sysParms").[A010PROD_ConnectString]
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT PM.Nomen "
    sSQL = sSQL & "FROM FPRPTSAR.Part_Master PM "
    sSQL = sSQL & "WHERE PM.PART_ID='" & sPN & "' "
    
'    Sheets("sysParms").Range("SQL_Code").Value = sSQL
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst
    GetNomen = rst("NOMEN")

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

This function returns a Nomenclature for a given Part Number from a database table.
[tt]
=GetNomen(A1)
[/tt]


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Katy44]

OK, I see how that would work and it seems a bit more like what we need, I'll give it a go thank you.

 

[Katy44]

I have written a function that I think will do what I want, but I just can't call it. To try it out I have created a simple function in a Module in the VB editor.

Public Function TestKate()
    TestKate = 2
End Function

When I put =TestKate() (or =Module1.TestKate()) in the spreadsheet it just comes back with #NAME?

Any ideas why this is happening?

 

[SkipVought]

1) it must be in a Module and NOT in a Sheet or Workbook Object in the Project Explorer in the VB Editor.

2) do you have the security level set to be able to run macros? Tools/Macro/Security

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[DPlank]

Tried this with testDave and it worked fine for me.

Public Function testDave()
testDave = 2
End Function

in a Module in the VBE and

=testDave()

in Excel returned a 2 to the cell.

#Name usually comes back when a function is not defined in the current workbook. Can't quite see why it is here.

Is this the only function in the workbook?

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[Katy44]

Thank you both for your help. I've just tried it again and it worked! Can't quite see what I did differently, but it must have been in the wrong place!
Sorry for wasting your time...now to get the real one to work!!

 

[DPlank]

No problem Katy. Give us a shout if you need any more help.



Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like