ODBC Lookup To Populate Variable

Apr 14, 2006

I have no problem setting ODBC connections in vba, that return the results to a selected Range destination, but short of then setting txtVariable = Range("A1") is there any way to directly tell the ODBC connection to set the result of the query to txtVariable?

View 3 Replies


ADVERTISEMENT

Excel 2010 :: ODBC Connection String With Multiple Variable (WHERE) Requirements

Dec 29, 2013

With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.

This is a two part process:

Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")

Here is part one

Code:

Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable

[code]...

Part one works perfectly.

Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.

I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.

As follows:

In particular: *E3 from Part 1*, etc.

Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)

Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.

1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.

2. Create a nested if table and

Change

Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"

to the value of that nested if table

The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.

3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.

What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.

View 5 Replies View Related

Double Lookup (lookup Variable Row And A Variable Column)

Mar 27, 2009

I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.

Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?

Or in the attached file, I would like to say A and 15 and get A15.

View 3 Replies View Related

Lookup Value On Another Tab And Then Populate

Jun 16, 2014

demo.xlsx

I have a huge inventory sheet and I'd like to maintain values, lifespans and other information on one "Data" tab while the main inventory sheet populates those values automatically. Is this done with Lookup? A demo sheet is attached here.

View 1 Replies View Related

Populate Variable With Combobox Value

May 17, 2006

I'm looking to change the value of a variable using the selected value of a combobox. I've used the following

Dim DatabaseVal As String

'then on selecting my option from the combo box: "JVU" or "VPU" are the only two options.

DatabaseVal = cmbDbase.Value

When I try and use the variable 'DatabaseVal' to populate a value in another workbook (the workbook contains a reference to the add-in running the above macro), the cell just gets populated with a blank value. I'm sure I'm doing something stupid, but can't see what.

View 3 Replies View Related

Lookup A Value In One Sheet And Populate Another

Jul 21, 2006

I have 2 sheets in my workbook "Master Sheet" and "Weekly". The "Master Sheet" contains Parcel Numbers and the groups they belong to, this information will rarely change. The "Weekly" sheet contains data that is pulled from a report weekly. It has 4 columns, the "Parcel Number", the "Parcel Name" the "Availability" of that parcel, and the "Group" the parcel belongs to.

I want to create a macro assigned to a button then when pressed looks at the number in the "Weekly" sheet and searches for it in the "Master Sheet" and populates the corresponding "Group" cell in the "Weekly" sheet with the value it finds in the "Master Sheet". I have attached a sheet that shows an example of what I need.

View 3 Replies View Related

Using Array Variable To Populate Range

Dec 17, 2008

I'm trying to populate a worksheet using arrays.

View 14 Replies View Related

VBA Auto Populate Cell Through Lookup

Mar 25, 2014

I would like to enter a name in E11, and then have G11 populate the name of the company that person belongs to, from a different sheet.

If the person is new, the company name entered into G11 should create a new column on the Companies sheet.

I've attached a dummy sheet which should make it more clear.

DummyCompanyPopulate.xlsx

View 9 Replies View Related

Lookup Based On Criteria And Populate?

Jan 15, 2012

I have the following in sheet 1:

Sheet1 *BCDEFGHIJKL1NameLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA2AMMB $ * * * 5.76 $ * * * *17,361.71 12.8861.633 $ * * * * * *7.05 $ * * * * * 5.30 10%1.20.42%3CIMB $ * * * 7.27 $ * * * *54,036.27 14.8372.156 $ * * * * * *9.01 $ * * * * * 6.56 3%20.61%4RHBCAP $ * * * 7.31 $ * * * *16,022.91 11.0761.435 $ * * * * *10.40 $ * * * * * 6.53 6%3.20.83%5HLFG $ * * 11.70 $ * * * *12,120.91 7.2581.576 $ * * * * *13.74 $ * * * * * 8.44 4%1.711%

I have the following in sheet 2:

Sheet2 *BCDEFGHIJKL2Parameters**********3Last Done**********4Market Cap (M)**********5PE**********6Price/NAV**********7High, 52 Wks**********8Low, 52 Wks**********9Revenue Growth**********10Current Ratio**********11Debt to Equity**********12ROA**********13***********14StockLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA15***********

1)I need to create a formula to populate all stocks that satisfies the parameters that I will input in cell C3 to C12 in sheet 2 based on the data in sheet 1.

2)The stocks should then be populated in cell B15 downwards in sheet 2. If there are 20 stocks that satisfies the parameters in cell C3 to C12 in sheet 2, then all 20 stocks should be populated in cell B15 downwards in sheet 2.

3) The respective details of the stocks should also be populated in cell B15 to L15 for all stocks that satisfy the parameters entered in cell C3 to c12 in sheet 2.

View 4 Replies View Related

Lookup & Populate Correlating Data

Jan 13, 2007

Based on what the area code that is in column H, column I should populate what STATE the area code correlates to, which is on sheet labeled AREA Codes, in column A (area code)and B (State). Workbook contains several sheets - initial sheet contains Column H which is populated with area code information. Column I requires the formula to populate the state which is displayed on a sheet labeled Area Codes. The Area Codes sheet contains column A which is populated with area codes and Column B which is populated with the corresponding State...............

View 5 Replies View Related

Lookup Populate Another Field With Information Already On Sheet

Aug 14, 2014

I've been stuck with this issue for a few days now. I'm trying to populate another field with information already on the sheet. The data is demo data.

I've tried =VLOOKUP(C2,D55:E55,2,0) however it only returned 0

View 2 Replies View Related

Mixing ADO And ODBC

Dec 30, 2009

I've got an Excel document I've built which uses multiple ODBC QueryTables

View 3 Replies View Related

Connect To Access Using ODBC

Apr 17, 2009

how to do this with Java but not VBA, I have the capability of understanding it I just need to know where to look because I can't seem to find any solid information on the net from my searches. Does anyone have any websites, other threads or anything they could direct me to for this kind of thing.

I was contemplating putting this in the Access thread but I want to connect to Access through Excel so I think it fits both.

My plan is to use a macro to send specific cells to five seperate tables in an Access database using VBA and SQL.

View 9 Replies View Related

Multi-Variable Lookup Table

Jun 12, 2014

I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.

Screen shot 2014-06-12 at 12.36.47 PM.png

View 1 Replies View Related

Lookup Function For Variable Amounts

Apr 10, 2007

I have a worksheet where I only want to pull over details for rows that have a amount listed in the variance column. Is there some type of macro I can create to perfrom this for me? There will be many rows that I do not need to pull, but for the ones that have an amount listed in variance, I will want to pull over certain cells from those rows.

Is there any way excel can do this for me?

View 3 Replies View Related

ODBC Connection To An Oracle Database

Nov 14, 2009

I am currently querying data from an Oracle database through MS Access and then passing it to Excel for the user to work. Not all users have MS Access so I would like to automate the data pull from Excel

Database information:

The DNS is: a150
The Tables are:

MFE.Items
MFE.Vendors

Sample SQL string would be:

View 2 Replies View Related

ODBC Query Returns To Textbox?

Oct 20, 2011

I would like an ODBC query to return the reults to a textbox on my userform.

View 1 Replies View Related

API Call To Get List Of ODBC DSNs

Mar 25, 2002

Does anyone have an API that will allow me to get a list of ODBC data sources defined in the Windows ODBC Control Panel?

View 9 Replies View Related

VBA Set ODBC Connection String For Query

Jul 25, 2008

My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?

The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.

Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).

View 9 Replies View Related

Index/match/vlookup And ODBC SQL

May 7, 2009

I want to create a formula that will use a database table as a lookup for index/match and vlookup type functions.

I have my ODBC connection connecting to a mysql database and am successfully able to run sql queries through excel.

Is there a way to get index/match/vlookup formulas to use the database table as the lookup range?

View 9 Replies View Related

Use VLOOKUP Function In Macro With Variable Name For Lookup Value

Feb 7, 2014

I used VLookup function in macro. Lookup value is a variable name. I don't know how to use variable name inside of VLOOKUP function.

Use VLOOKUP function in macro with Variable Name for Lookup value

Function CC(CName As String)

ActiveCell.FormulaR1C1 = "=VLOOKUP(cname,'[Structure File.xlsx]Sheet1'!C1:C3,2,FALSE)"

End Function

How to use CName variable inside of Vlookup function.

View 3 Replies View Related

Excel 2010 :: Lookup From Variable Table

Mar 13, 2012

I need to return a value dependent on a few criteria. Type will be selected via a drop down and then it must lookup the same type in the top row and then the value which will be between two values and then return the grade on the far left.

GradeG4M5M6TypeValueGradeA
160016501800M5850CB600700650780900950C7008007809109501100D8009009101040
10001250E90010001040117010501400F100011001170130011001550G>1100>1300>1150

I am using Excel 2010 on Win 7

View 6 Replies View Related

Lookup Based On Variable/Changing Sheet Name

May 23, 2008

I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across:

The following is the way my spreadsheet's first page is set up:

ID Apr-08 Mar-08 Feb-08
1
2
3

The column labels depend on another table, which changes monthly.
The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this:

ID $ amount Date paid
1
2
3

I have another table upon which the na

I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table?

This is essentially what my formula looks like right now for cell A2:
=VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE)

If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.

View 9 Replies View Related

Multiple Dropdown List With ODBC Connection

Jul 15, 2014

I'm trying to create a dropdown list on my "job master" sheet with reference to cells in an ODBC connection where a position "description" will give me a list of names. Then I want to be able to select another list of names individually with reference to the name I Just selected.

Staff
Description
Foreman

[Code].....

*I was able to do this using lists and indirect list (Personnel Sheet) but it doesn't work with my ODBC connection. I attached the excel file I am using.

View 1 Replies View Related

Connecting To A Database Without The Local Machines Odbc

May 4, 2007

I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..

When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..

View 9 Replies View Related

Index / Match Lookup With Variable Indexed Column

Jul 23, 2014

I'm trying to perform an index / match lookup that can have a dynamic indexed column.

Sheet1 has the master table and sheet2 is where I'm putting the formula. The table on sheet1 has a RecordID field that uniquely identifies each record and that must be typed into Sheet2 [RecordID] column and then matched to sheet one to identify the record

Code:
MATCH([RecordID],Table1[RecordID],0)

The column headers on Sheet1 are used as data validation for the column [Field] on sheet2. The value of the Field column on sheet two should determine the the column on sheet1 to index.

This is what I have so far:

Code:
=INDEX(MATCH([Field],Sheet1!A:AQ),MATCH([RecordID],Table1[RecordID],0))

View 7 Replies View Related

Index Match Lookup, Variable Return Column

Sep 3, 2009

I would imagine that the title of this thread made little sense, so let me try to explain:

Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.

So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.

View 9 Replies View Related

Login And Password Prompt Prior To ODBC Connection?

Apr 23, 2013

i currently have an excel based application with VBA code. I'm trying to add some controls around the app by prompting user for login and password credentials prior to ODBC connection at runtime. So when application is launched, prompt appears, user enters login and password, if correct, it goes on to verify user has ODBC connection at runtime.

View 2 Replies View Related

ODBC :: Importing External Data From An Oracle 9i Database

Mar 18, 2008

I'm having some issues importing external data from an Oracle 9i database.
Here is what is happening:

I am initially able to import data from the table I want to into Excel.
I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.

If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.

Also, if I attempt to establish another connection following the "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.

I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.

I also use this data source in Microsoft Access without any problems.

View 9 Replies View Related

Excel 2010 :: Assigning Name To Range Received By ODBC Query

Apr 29, 2014

I have been working on some ODBC queries in Excel 2010.

First, I recorded them using Excel's Record Macro function, to get an idea of what sort of source data I would need. Then, I rewrote them into something a little more intelligible.

Pulling out all the sensitive info, I want to know how to add the .ListObject.DisplayName property back onto the results of the query. I tried doing it intuitively, but it didn't work. It was in there when I recorded the macro, but I can't seem to figure out where to put it back into the re-written code. Other parts of the code depend on the results of the query being a 'named field'.

[Code] .....

Also, where would I find out what all that stuff in Cnnect means? DBA, APA, EXC, FEN, etc are all just assignments, and I might like to change some to make this run a bit quicker, if I knew what they meant.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved