(Find) Function With ADODB Recordsets
Jul 23, 2013
I am wanting to find a row in a SQL database using VBA in excel. I know you can use find to find the row, but then how do you replace data on that row with the row I have in my spreadsheet?
I currently have:
Code:
If lastrow 0 Then For i = 3 To lastrow
recset.Find "Col2 = " & Sheets("Changes").Cells(2, i) 'find the value in B from B3 onwards (i)
' Now its found the row in the SQL, replace with values from that excel row (i)
Next i
End If
View 9 Replies
ADVERTISEMENT
Dec 17, 2012
I'm using the below code to add data to an access database and it's working fine, but I'm trying to figure out what code I would need to add a completely new field to the access database?
So maybe before the export, once connected to the DB, add the new field(s) to the table and then add export the data. I'd have to rewrite export bit to allow for any new fields that i've added, but I can do that.
It's just the actually command I need add the new field to the table. Sure it's just a simple couple of lines, but trying to find something that makes sense !!
Code:
Sub Update_data(strSite)
strDir = Worksheets("Parameters").Cells(2, 2)
strDB = Worksheets("Parameters").Cells(3, 2)
strTable = Worksheets("Parameters").Cells(4, 2)
' exports data from the active worksheet to a table in an Access database
[Code] .........
View 1 Replies
View Related
Feb 13, 2013
I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:
Arguments are of wrong type, are out of acceptable range, or are in confilict with one another
The essential part of the stored procedure looks like:
Code:
CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS
while the essential code calling it from VBA looks like:
Code:
Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String 'Stored Procedure name
[code]....
and in that rs.Open comes that error.
What have I done wrong, how do I fix it?
View 9 Replies
View Related
Oct 27, 2009
I have some code that will Kill a DB if it already exists, but I want to check if it exists and warn the user before this happens.
I am not that familiar with ADO, so I was fumbling through the Help topics trying to learn about ADO type names, etc. b/c i thought I could use something like:
If TypeName(MyDB) = "ADODB" Then . . .
but even if that ran, the argument in parentheses would be a string and not the actual DB object, so I am at a loss.
View 9 Replies
View Related
Oct 4, 2006
With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.
View 2 Replies
View Related
Oct 30, 2008
I am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:
1. Select an Excel Range (The table being Queried)
2. Provide an SQL Statement
3. Provide an Output range to print the results
After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following
Sub getData(SQL As String, outRange As Range)
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim outCell As Range
Set outCell = outRange.Range("a1")
Dim row As Integer
Dim col As Integer
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
cs = "DRIVER=Microsoft Excel Driver (*.xls);UID = admin;UserCommitSync = Yes;Threads = 3;SafeTransactions = 0;ReadOnly = 1;PageTimeout = 5;MaxScanRows = 8;MaxBufferSize = 2048;FIL=excel 8.0;DriverId = 790;DefaultDir=F:2008Cell PhoneCall Details;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name...............................
View 9 Replies
View Related
Mar 26, 2009
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
View 2 Replies
View Related
May 5, 2014
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
View 5 Replies
View Related
Dec 8, 2008
So I have 2 worksheets. One has a list of Player Names. The other has a list of Plays that any of these players could have been involved in (or they may not have been involved in).
I am trying to use the find function to find anyone of those player names from sheet 1 in each of the plays. My functions currently stands at
=FIND(('Dinas Roster'!$A$2:$A$21),I6,1)
Where Dinas Roster is the list of players and I6 is a particular play and would go to I7, I8....which are the plays,
This isn't working as I am getting all #Value. If I just do it for 1 Player (i.e. Dinas Roster'!$A$2) it works and returns a number on each line that player shows up in...
View 12 Replies
View Related
Mar 7, 2007
I need a find anywhere function.
Example
text to find is in ZZ1
area to find the text is A1:BB500
It could be anywhere in that area.
I don't want to do the find command 1000+ times for all the data i need to search for.
Vlookup is just column A, i need column A:BB
I have no idea what column or row it would be in.
basically, look for text from sheet2A1 anywhere in sheet1
View 9 Replies
View Related
Sep 28, 2006
I'm looking for something simple that will let me type in a 5 digit numeric value and then when it finds the associated value, will add +1 to the associated col/row.
Such as, type 11111 in the box, click the button...When it finds the data on say row 8, col A it will then add +1 to row 8, col G.
I'm not sure of the best way to do this...I'm not exactly new to excel, but it has been a VERY long time (10 years!) since I've used it in this aspect...Just trying to help a friend out.
View 9 Replies
View Related
Jun 12, 2007
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code.
Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
If Find = True And ActiveCell.Next.Next.Value <> "" Then
Find = Empty
Else
MsgBox "The currency " & local_currency & " does not exist in the currency list"
Sheets("Remarks").Select
End If
View 9 Replies
View Related
Jan 30, 2014
I want the create the following summation function (probably with the use of FIND).
You have 3 columns:
- Column A containing values; a, b, c, d, e
- Column B containing values: 1, 2, 3
- Column C containing ad randum values f.e. lengths
I want the achieve a summation of the lenghts with the following conditions 'a' in column A AND '1'in column B.
Apart of this a summation of the lengths with the conditions 'a' in A AND '2' in B.
[Code]....
View 2 Replies
View Related
Jan 8, 2009
In Sheet 2 i have a 1000 of data contains the birth date of following customers
The result i want in Sheet 1 is
particular on todays (Say on 27-11-2008) date how many customer are having birthday, supoose there 10, or 8 wahtever should show me the list.
I tried this formula
=INDEX(Sheet2!$B:B,MATCH($B$3,Sheet2!$H:$H,0))
but by this formula it only show one customers birthdate what i want if there 10 differenrt customer those same birthdate it should display all the 10 date and name in diff rows
View 12 Replies
View Related
May 11, 2009
Attached is a sample in which Column"B" contains Total IDs and Column"D" contains worked labour IDs. Now I want the IDs of labour who have not worked in Column"F".
View 11 Replies
View Related
Dec 20, 2009
Need a function that returns the days from the last week of a month?
View 3 Replies
View Related
Feb 16, 2010
I use a lot the function “Find” in Excel but the problem is that it takes a lot of time, so I’m searching for another function or code that can be faster than that, the Worksheets that I use in Excel contain thousands of sheets so it takes hours to execute the Macro.
View 10 Replies
View Related
Nov 4, 2005
I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to know if it is there.
View 9 Replies
View Related
May 8, 2012
I have a column of values (text) to choose from. I need to randomly assign them to names.
There are more values then names.
Each name has to have a value.
Each value has to be used only one time.
Not every value has to be used.
Column B contains names, column Q contains values.
Now. So far I've come up with below:
Code:
Sub randomize()
Dim Random As Integer[code].....
"Random" returns exactly the amount of values in column Q, so this works ok.
"i" counts iterations correctly, meaning as many times as names in column B.
I tried useing Match to rule out already used value but this code returns "unable to get Match property of the WorksheetFunction class" error.
View 6 Replies
View Related
Jan 7, 2013
I have a worksheet with dates on and have user form to display dates within a range. I have created some code, but the find function errors and says it cannot find this value on the sheet, but it is definatly there. This is my code: I have added an asterix to where it errors and says it cannot find the value
Code:
Private Sub SearchButton_Click()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim FoundFrom As Range
Dim FoundTo As Range
With Worksheets("Calender")
[code]....
View 2 Replies
View Related
Mar 3, 2010
In a database, i need to search and select the cell that contain a given string. If found, from that cell range, i want to grab some info using the offset command.
I'm trying to use the Range.Find function. I got an error on my formula
what: "IP_"
From cell ("T1)
Look in Whole cell
Look by Row
XlNext
[CODESub GetInfosFromData()
Dim st As Range
st = WorksheetFunction.Range.Find("IP_", Range("t1"), , xlWhole, xlByRows, xlNext, True)
If SG IsNothing then ' A match occured
' do the process
End Sub][/CODE]
how do i tell the subroutine to look for any subsequent match (Next)
View 9 Replies
View Related
Jul 1, 2005
I have two spreadsheets, which have over 10,000 entries and I am trying to compare them both for duplicates. I would like to know whether or not there is any VBA coding which will allow me to copy a cell from one spreadsheet and look for it in the other spreadsheet.
The major problem which I am facing is the information which I am trying to look for is constantly changing and therefore is more or less unknown to me. I am hoping for some VBA coding which will allow me to copy whatever is in cell A1 and find it in the other spreadsheet which I have, and then do the same for A2,A3 and so forth.
View 8 Replies
View Related
May 14, 2006
how to make the << and >> buttons as well as the find button work on this form. Here is the code for the >> (next Record) button. I think if I can understand how one of them is supposed to work I can do the rest. I have been trying for days to figure it out, and even with RoyUK's help I still can not get it to work....
View 9 Replies
View Related
Feb 2, 2007
I am having trouble with the following code. When I put in a value in C8 that is on the list being searched (A2:A27), the Answer is still coming up as false (ie, the find function isn't finding the variable in the list, though it is there). I'm guessing I'm using incorrect syntax somewhere.
Dim Answer As Boolean
Private Sub CalcBi_Click()
Dim Pledge, Edate, PR, PPA, EEA As Double
Dim Due, Chdate As Date
If Range("C6").Value = "" Or Range("C8").Value = "" Then
Exit Sub
Else
Pledge = Range("C6").Value
Edate = DateValue(Range("C8").Value)
Set rngschedule = Worksheets("Bi Weekly Schedule").Range("A2").Offset(Application.WorksheetFunction.Match(Range("C8"), Worksheets("Bi Weekly Schedule").Range("A2:A27"), 1), 0).....................
View 2 Replies
View Related
Feb 15, 2007
What I'm doing in this script is taking a whole bunch of variables and pasting them into a seperate sheet "Checks" which will construct a cashiers check. The problem is where I've highlighted the code in red. At that point I have copied the Vendor name to the clipboard - I then go to the "Vendor Info" page and search for that Vendor name. If that name exists on the page it works perfectly...moving one column to the right, grabbing the Address Line 1 and dropping it into the check, then going back and grabbing the Address Line 2 and dropping it into the check. The problem is when that Vendor name doesn't exist in the "Vendor Info" page...I want it to just paste two blank cells into the check (because I obviously don't have the address info for that Vendor)...but instead it gives me an error:
Run-Time error '91':
Object variable or With block variable not set
Sub Checks()
'
' Checks Macro
' Macro recorded 2/14/2007 by Derek Minner
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Selection.Copy
Sheets("Checks").Select
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
View 3 Replies
View Related
Mar 9, 2007
I am comparing two series of data, a daily series and a weekly series, to make calculations. The "F" column is daily data and the "I" is weekly.
The formula I am using is:
=IF(AND( SUM(I58:I62)<F62,I63>F63),B64,0)
The data I need to use in the "F" column in place of "F62" is always on a Friday.
I had taken "F62" which is the preceding working day's data. In fact I need to take the preceding Fridays data in column "F" as I need to compare the week to week trends. Due to bank holidays I cannot count back form F62 to get the data. There is always end of week data recorded on Fridays even though it may be a bank holiday.
I have the dates in the "H" column. Can a date reference be used in the formula to find the Friday data in column "F"? Or is there another solution to this problem?
View 9 Replies
View Related
Oct 18, 2007
I am writing VBA code that dissects a cell which always contains some alphanumeric characters a colon ":", some more characters, another colon ":" and some more characters.
For Example this is what is in my cell "Deposit:93121:Southern California"
what I need is to cut and paste everything that is located after the 2nd colon ":"
Say that my cell is D433 and in non-vba world I can chop off the first part of the cell to the left of the first colon ":" by using the following formula:
=MID(D433, FIND(":",D433)+1,50)
assuming there are less than 50 characters in my cell, which is a safe number, then I cut and pastespecial this cell as values into the same cell and repeat that same formula and voila, what remains in cell D433 is what I am looking for "Southern California"
how can I replicate this surgical process in VBA, is there a way to combine this process in one command on any given target cell?
View 3 Replies
View Related
Nov 25, 2012
I just want to replace the offset with find function. I have attached the sample file with the code. Just need a little change.
View 5 Replies
View Related
Aug 13, 2014
I'm in a workbook, and I want to look up a name. CTRLF or clicking on the binoculars both bring up the expected dialog box, but when I populate "Find What" and either hit enter, click Find All or Find Next, nothing happens.
The only thing I can think of is that I created a macro to function in one sheet only, then saved this workbook as .xlsm. But that doesn't seem right.
View 1 Replies
View Related
Dec 18, 2008
I know I have to use FIND and MID but I can't work out the right syntax: how do I extract Michael or Dave from the following strings:
F - Michael 8735
M - Dave 093
View 3 Replies
View Related
