get a result of an sql into a field

Hi there

I would like to get a result of an sql execution (ms sql server) into aq 
filed.

example i A1 I have a ID number
 in A2 I would like to get the result of something like this 'select name 
from address where id=A1'

Does this exist in Excel ?

Thanks in advance


Ralf 


0
rmeuser (6)
9/24/2004 4:22:29 PM
excel 39879 articles. 2 followers. Follow

1 Replies
651 Views

Similar Articles

[PageSpeed] 45

Here is the sub i have written for loading an Sql Query into th
worksheet.

Parameters:
Server Name
DataBase Name
SQL Command
Target Sheet name
Column to begin from
Row to begin from

ex: CALL LoadData("MyServer","MyDataBase","Select UserName fro
TblNames",
"QueryData",4,2)

**You will need to add a reference to MS ActiveX Data objects from th
VBA Editor.

Sub LoadData(strServerName As String, strDbName As String
strSQLCommand As String, strSheetName As String, intBeginColumn a
Integer, intBeginRow As Integer)
Application.ScreenUpdating = False
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
objMyConn.ConnectionString = "Provider=SQLOLEDB.1;Integrate
Security=SSPI;Persist Security Info=True;Initial Catalog=" & strDbNam
& ";Data Source=" & strServerName & ";Use Procedure for Prepare=1;Aut
Translate=True;Packet Size=4096"
objMyConn.Open
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = strSQLCommand
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
ctr = 0
ThisWorkbook.Sheets(strSheetName).Select
For Each fld In objMyRecordset.Fields
ctr = ctr + 1
ThisWorkbook.Sheets(strSheetName).Cells(intBeginRow, intBeginColumn
ctr).Value = fld.Name
Next fld
ThisWorkbook.Sheets(strSheetName).Range("A" & intBeginRow 
1).CopyFromRecordset (objMyRecordset)
ThisWorkbook.Sheets(strSheetName).Cells.Select
Selection.Columns.AutoFit
ThisWorkbook.Sheets(strSheetName).Cells(intBeginRow
intBeginColumn).Select
End Su

--
bdcris

-----------------------------------------------------------------------
bdcrisp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=356
View this thread: http://www.excelforum.com/showthread.php?threadid=26323

0
9/24/2004 5:06:32 PM
Reply:

Similar Artilces:

How do I get hyperlinks, from previous version of excel, to work .
Since I have upgraded to a new computer, and Excel 2003, I cannot get my existing hyperlinks to work. Additionally, I can create hyperlinks, but when I click on them, I get a message that states that the specified file cannot be opened. ...

Getting Cashier ID as 0
Hi... I am getting cashier Id as 0 everytime ?? I am trying to display the Cashier Id on message box and everytime its displaying 0 :-(.... Please help me.. Vicky... :-) Show a sample of code you are using. Rob "Vicky" <Vicky@discussions.microsoft.com> wrote in message news:4ADB2328-B6A0-4DAA-A47B-34C954CBAE37@microsoft.com... > Hi... > > I am getting cashier Id as 0 everytime ?? I am trying to display the > Cashier Id on message box and everytime its displaying 0 :-(.... > > Please help me.. > > Vicky... :-) Hi Rob, Following is the c...

Add-in function sometimes gets called twice
I have been working on a new UDF for my personal add-in module. It is mostly working, but has recently started occasionally executing twice. I cannot figure out why this is happening. It is hard to trace because it doesn't happen every time. I only noticed it because I added a Msgbox statement and every once in awhile, I get two messages. Is there a way to tell where the UDF is being called? Which sheet and maybe which cell? The function is called from two different sheets in the same workbook. Is sheet2 recalculated if data on sheet1 changes? Maybe because of uncalculated c...

Form Controls unavailable to query field expression
Hi All - This is a follow-up post to Subject: ‘Pass form parameters to query expression’ dated 1/3/08. Given: 1. Stand-alone Access 2003 mdb database. 2. Custom dialog form with two textboxes for user input (latitude & longitude). 3. The btnOK on the form sets Me.Visible = False, but does not close the form. 4. A stored Select query named based on an underlying table. 5. An expression in the stored query that uses fully qualified references to the latitude and longitude textbox controls on the form. The expression is: Delta: Abs(Abs([Latitude])-Abs(Forms!frmParameters!tbxLat))+Abs(A...

Export to DBIII from SQL Server 2000 DTS package
I am having a difficult time exporting a large table in SQL server (2GB) to a dBase DBIII .dbf file via a DTS package in SQL Server 2000. I've looked through many articles in Google groups and elsewhere about this and I'm still having trouble. When I use the standard Microsoft DBIII datasource the export works fine up until 255MB where I get the message "Cannot update; Table is currently locked". This KB article http://support.microsoft.com/default.aspx?scid=kb;EN-US;238431 mentions using the Microsoft FoxPro VFP Driver to access the DBIII files which I downloaded and instal...

FindRecord2K works perfectly, how to display results in my form
I downloaded the findrecord2k search form. I would like to add a command button that takes those results that are generated from the query and displays them in my form. There are several command buttons available, would it be easier to modify an event to include displaying the search results in my form instead of the unbound control? ...

Outlook can't get mail
I have Outlook 2000 and XP Home Edition. I can open Outlook and it will stay open -- until it tries to receive email. Then I get an error message that says it has encountered an problem and must close down, we're sorry for the inconvenience. I can reopen it and no problem -- until the email starts to come in again and then it closes with the same error message. My ISP is Charter and the email is a POP3 server. Any suggestions? Run scanpst.exe against your pst-file and try again. If this fails to solve the issue locale outcmd.dat and rename if to .old -- Robert Sparnaaij [MVP-Outl...

SQL database
Hi, I have just converted an access database and placed it in the SQL database for CRM with all relationships and data i had from the old database. The records are all in place in the correct database (companyname_MSCRM) but i still can not seem to get any of the data into CRM itself... what am i doing wrong? How did you get the data in there from the Access Database? You need to use an import application (SDK) that utilizes the CRM security service so that the CRM specific security attributes are added to the record. Jason "dave" <anonymous@discussions.microsoft.com&...

How do I display day, month and year in separate fields?
From a date field, I need to get the values for day, month and year respectively put into 3 different fields. E.g, if you have the date 24-12-2007 (December 24, 2007) I need 3 fields displayed as follows: Day: 24 Month: 12 Year: 2007 Does someone have a suggestion to how I can solve this problem? take a look at http://www.microsoft.com/dynamics/crm/using/sales/birthdaylist.mspx#EDB best regards "Thomas H. Bech" wrote: > From a date field, I need to get the values for day, month and year > respectively put into 3 different fields. > > E.g, if you have the date...

Sure there's a simple answer to this but I can't get my head aroun
I would like to have an "announcements" memo field in my database. This would show a message for example such as "Please note that all staff can go home 2 hours early today" (yeah, right). I want managers to be able to edit this text in a simple seperate form. It needs to display the SAME message on all of my records. I have set up a new table called Management but I can't seem to link it to my existing form. Help please! A form can have only one record source. One way would be to have a subform on every form that would show the record, but that is pretty cl...

SQL Update to a SOP table
Hello all. I wonder if it is possible and correcto to run a TSQL update against the table SOP30200 in GP in order to change the slprsnid (salespersonid) in just some transactions. Posted transactions. I tried to use PSTL but that tool update the whole table and I need just the transactions of the current year. I really appreciate any comment about this matter. emiranda Hi Emiranda, There may be several issues with this approach. What need are you satisfying by updating the salesperson id on posted invoices? The salesperson ID also appears on the sop line, so tha...

Change "FROM" field in OWA
I had someone ask me if it is possible to be able to change who a message is FROM in owa. Their organization has several different domain names and depending on the message would like to change the domain name its from (like in Outlook). Any ideas? Thanks. Khan OWA doesn't let you do that. Haven't heard of any third-party add-ons that let you do that either. -- Bharat Suneja MCSE, MCT -------------------------------- "Khan Hollis" <kvhollis@hotmail.com> wrote in message news:%237XWv5O0FHA.3124@TK2MSFTNGP12.phx.gbl... >I had someone ask me if it is poss...

Getting rid of MSN messenger
Hi: I have a simple question. Unless I need it for outlook (which I do need), can I get rid of the Windows Messenger. Everytime I load Outlook, the damn thing goings into my taskbar and doesn't disappear even after I close Outlook. Thanks Uninstall it, it's not an essential part of Outlook even though it does have uses. "Id10 Terror" <noone@nowhere.com> wrote in message news:umCmIE$1FHA.3000@TK2MSFTNGP12.phx.gbl... > Hi: > > I have a simple question. Unless I need it for outlook (which I do need), > can I get rid of the Windows Messenger. E...

m_strFilter version of SQL query?
How is the following done with an m_strFilter in MFC: (RID is a double or int, '1' is a String) SELECT * FROM [dbo].[ADB] WHERE CAST(RID AS CHAR)='1' (this works in SQL) OR SELECT * FROM [dbo].[ADB] WHERE RID=1 (this works in SQL) SELECT * FROM [dbo].[ADB] WHERE RID='1' (this works in SQL) I've tried strings like any of the above but none work in mfc. Thanks. "CharlesC" <Charles@bscinforma.com> wrote in message news:eqg$%23dREIHA.3980@TK2MSFTNGP03.phx.gbl... > How is the following done with an m_strFilter in MFC: > (RID is ...

how to import the address book/contact list into the "'TO" field for sending ema
How do i set up Outlook to send/receive email, by using my Outlook contact list? When I press the contact icon, it comes up w/ all the many faxes and emails I have sent for the last year.... something is not set correctly, and I do not know where to find these settings. Why is there not a full "how to" web site/manuel for all of Outlook??? There are 2 ways....very simple. 1. Just select (highlight) the contact icon and then select Actions>New Message To Contact 2. Double click on the contact and perform the same steps as above..ie.. Actions>... >-----Origina...

SQL 2008 and SRS
I am using SQL 2005 with CRM 4.0 on one box and have to move CRM and SQL to seperate boxes. The SQL box will have SQL 2008 Standard. The consultant who will be doing the SQL work reminded me to install SRS. Is SRS included with SQL 2008 Standard? Thanks, John SQL Server Reporting Services (SSRS) is part of 2008 Standard Edition. RLF "John" <bsmith@aol.com> wrote in message news:%23DpI1bfcKHA.808@TK2MSFTNGP02.phx.gbl... >I am using SQL 2005 with CRM 4.0 on one box and have to move CRM and SQL to >seperate boxes. The SQL box will have SQL 2...

remove "getting started" from excel 2003
Hi all. Whenever i open excel 2003, i could see the "getting started" box on the right side of excel application. How do i remove it permanently? I have another question. When i open 1 of my excel document there is a green triangle on every cell of 1 column. How can i remove the green triangles? Thks in advance. Both can be resolved from the Tools>Options dialog box. On the View tab, uncheck Startup Task Pane in the "Show" section On the Error Checking tab, uncheck the Enable background error checkin in the "Settings" section. note: the green triangle...

select statement results msgbox
I would like to do a select count statement and display the results in a msgbox. Is that possible? ex SQL = " Select count(StoreNum) " & _ "FROM GBPEXCFMASTERFLIFLC INNER JOIN TRANSFER_MEMBER " & _ "ON GBPEXCFMASTERFLIFLC.StoreNum = TRANSFER_MEMBER.CMCUNO " & _ "WHERE (((GBPEXCFMASTERFLIFLC.StoreNum)<>'0266' " & _ "And (GBPEXCFMASTERFLIFLC.StoreNum)<>'0673' " & _ "And (GBPEXCFMASTERFLIFLC.StoreNum)<>'0306' " & _ ...

Expression comparing fields from 2 forms
Can I palce a text box in a form with an expression to compare 2 fields? one from the main form and one from a subform? The following is not working =[VehiclesDayMiles:Opening MilesT]-[VehiclesDayMilesLastByGroupQ: LastOfClosing Miles] Thanks Quinto -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200801/1 On Thu, 17 Jan 2008 04:29:44 GMT, "quinto via AccessMonster.com" <u35571@uwe> wrote: >Can I palce a text box in a form with an expression to compare 2 fields? one >from the main form and one from a subform? The fo...

PDF conversions often result in corrupted photos
I use Microsoft Publisher 2003. I frequently encounter problems with the PDF file conversion (I use full version Adobe Acrobat). I don't know if the problem is with Adobe or with Publisher. Frequently, photos that look just fine in Publisher are corrupted in the PDF file (e.g., pixel squares show, picture looks like there is a fine line going thru it horizontically). To work around it, I have to go back into the Publisher file, delete the photo, re-insert the SAME phone and reconvert that page to PDF--this resolves the problem 99% of the time. However, I don't understand why it ...

scam getting past filter
I am using 2007 beta, I have junk mail filter set to high, do not display links etc etc, I have all updates but I still get Nigerian scam and cialis mail into my main inbox, I also get a lot of mail with no address in the To box, how can I stop these unwanted mails? "T5" <noanswer@hotmail.com> wrote in message news:OZmd5ygvGHA.5084@TK2MSFTNGP04.phx.gbl... >I am using 2007 beta, I have junk mail filter set to high, do not display > links etc etc, I have all updates but I still get Nigerian scam and cialis > mail into my main inbox, I also get a lot of mail with no ...

error 29528 installing SQL 2005 SP2/3 on 2003 server DC (!)
hello, i ran into the error 29825 while installing SQL 2005 SP2 and 3. the server is a 2003 x86 but is also a Domain Controller! i found the link to this article which seems to work for many people. http://support.microsoft.com/kb/925976 however, i would first like to inform whether this will also work for me since my sql is installed on a Domain Controller (which i know is not recommended) and so no local groups/users are available. should i delete the registry values or change them to sID's from domain accounts? if so, which? or if the above article will not work, are the...

Auto-Numbering Fields
In the Auto-Numbering Category under Settings, you can set the format for auto-numbering for 6 entities. And, when I go into Customization, Case, I do in fact, find an attribute named "Case Number" which is all set up to use the auto- numbering system. However, I am not finding one of these pre-designated fields in the attribute list of my "Order" entity. And the "Order" entity is one of the 6 listed in the Auto-Numbering section. How do I set this up if the capability is already there? Hi, Auto number field in order entity is named as "ordernumber"...

Combo box to display something else if field is blank
Hi I have a combo box in my form which uses the following query: SELECT [CustomerID], [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & " " & [LastName] However, if the both the firstname and lastname are blank in the Customer Table I need it to show the BillingAddress instead. Is this possible? My line of thinking is something like this: SELECT [CustomerID], [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & " " & [LastName]; IFBLANK SELECT [CustomerID], [BillingA...

Colour change depending on result
Hi all, Is it possible to make the true result of an IF statement, say, green and the false result, say, red? I would like to be able to do this without using VBa s I have no experience of using it. Simon Use conditional formatting See http://www.contextures.com/xlCondFormat01.html -- __________________________________ HTH Bob "SimoninParis" <SimoninParis@discussions.microsoft.com> wrote in message news:1B2E3999-A68E-4015-A109-B4B55D4A2CC7@microsoft.com... > Hi all, > > Is it possible to make the true result of an IF statement, say, green and > the false ...