ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> ODBC General Issues >> Using MS Query In Excel
http://www.istechforum.com/YaBB.pl?num=1372881941

Message started by MikeT on 07/03/13 at 13:05:41

Title: Using MS Query In Excel
Post by MikeT on 07/03/13 at 13:05:41

Alright...We are trying to figure a way out to have an excel worksheet that when you click on a cell and type in a part number (BKIC_PROD_CODE), then the next column could generate the Description. How do we do this? I have been able to create a MS Query with a parameter value on PROD CODE to let you specify the part number and then pull the related description also.
It would be cool to just fill out a table with part numbers then refresh and have the descriptions populate. Anyone have any ideas?

Title: Re: Using MS Query In Excel
Post by BurrKing_Mfg on 07/03/13 at 14:35:40

I used a lookup - It works great. We use them for many inventory related task.

I have a scheduled task to update inventory every evening. Then when that data is accessed in excel in the morning they can get good data.

I'm not sure if I can explain, but maybe by looking at the file you can get an idea. It's an OBDC connection that starts everything off.

Title: Re: Using MS Query In Excel
Post by Kelloggs on 07/04/13 at 14:43:29

Option Explicit
'--------------------------------------------------------
'--------------------------------------------------------
Public Const odbc_dba As String = "EVO"
Public conn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public cmd As New ADODB.Command
'-------------------------------------------------------

Public Function sku_desc(the_sku As String) As String
Dim var_sql As String
conn.CursorLocation = adUseClient
conn.Open "provider=MSDASQL;dsn=" & odbc_dba

var_sql = "SELECT BKIC_PROD_DESC FROM BKICMSTR WHERE BKIC_PROD_CODE = '" & the_sku & "'"
rs.Open var_sql, conn, adOpenStatic
sku_desc = Trim(rs("BKIC_PROD_DESC"))

rs.Close
conn.Close
End Function

Public Sub test_my_function()
Sheets("Sheet1").Range("A1").Value = sku_desc("MY SKU ID")
End Sub

:P

Kelloggs