Returning a Value into Excel from SQL ADO connection

Hi All first post.

I am currently having a problem returning a value into a cell in excel
2000

I understand the ADO connection, and have checked my sqlSTR in query
analyser.

I use the following to dump to a cell.

Set rcset = New ADODB.Recordset
    rcset.Open sqlstr, dbmain, adOpenKeyset, adLockOptimistic
    Sheets("Sizing").Range("b19").CopyFromRecordset rcset

What I have used this to dump the whole record set out, but the sqlstr
I am using only has a specific value in it. I am currently getting
#value in my cell, and am wondering if the value coming out of the ADO
dump is actually text.

Is there anyway to specify the value coming out of the rcset is a value
and not text?

0
6/12/2006 5:40:13 AM
excel 39879 articles. 2 followers. Follow

2 Replies
692 Views

Similar Articles

[PageSpeed] 16

I would doubt that getting a text value would show as #VALUE in a cell,. it
would just show the value.

Why don't you add some logic in the code to step through the recordset
rather than copy it, and then debug it

    If Not oRS.EOF Then
        For i = 0 To oRS.fields.Count - 1
            Debug.Print oRS.fields.Item(i).Value
        Next i

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stopher" <chris.sommerville@zinifex.com> wrote in message
news:1150090813.292071.191260@m38g2000cwc.googlegroups.com...
> Hi All first post.
>
> I am currently having a problem returning a value into a cell in excel
> 2000
>
> I understand the ADO connection, and have checked my sqlSTR in query
> analyser.
>
> I use the following to dump to a cell.
>
> Set rcset = New ADODB.Recordset
>     rcset.Open sqlstr, dbmain, adOpenKeyset, adLockOptimistic
>     Sheets("Sizing").Range("b19").CopyFromRecordset rcset
>
> What I have used this to dump the whole record set out, but the sqlstr
> I am using only has a specific value in it. I am currently getting
> #value in my cell, and am wondering if the value coming out of the ADO
> dump is actually text.
>
> Is there anyway to specify the value coming out of the rcset is a value
> and not text?
>


0
bob.NGs1 (1661)
6/12/2006 7:35:02 AM

I sorted it out. Instead of referencing a cell in the CopyRecordset I
recalled the value for the function, so-

 Set rcset = New ADODB.Recordset
 rcset.Open sqlstr, dbmain, adOpenKeyset, adLockOptimistic
[INSERT FUNCTION HERE].CopyFromRecordset rcset

0
6/12/2006 9:44:35 PM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Excel
1. An Excel tabulation begins at Row 6 ; and then, Cell G6 is subject to Conditional Formatting as follows :- =AND(ROWS($G$6:G6)<=COUNTIF($B$6:$B$35,$B$6)+COUNTBLANK($G$6:G6),G6<>"") 2. Subsequently, the Conditional Formatting of Cell G6 is "Paint-Brushed" to the succeeding cells. 3. And there, a blank row is inserted over Row 6 ; Look at the Conditional Formatting again, it has changed as follows :- =AND(ROWS($A$1:$G$7)<=COUNTIF($B$7:$B$36,$B$7)+COUNTBLANK($A$1:$G$7),G6<>"") 4. Now, is that change justifiably outrageous ? Expected, or what ...

Zero-value Budget Column in FRx SP11
Hello all, We have just resolved an interesting problem that one of our customers was encountering after upgrading from FRx SP9 to SP11, that I'd like to share with you all, along with our resolution. Bear in mind, I am not recommending this resolution to you, so much as providing it to assist you with your own enquiries. The symptoms of the problem are as follows: A report contains a two columns, a budget column and an actuals column. Running the report on FRx SP9 produces the correct figures. Running the report on FRx SP11 produces correct actuals, but the budget figu...

Chart Values and X axis values from VBA
Hi, I'm creating a series of charts based on data in many adjacent columns based on Name defined Ranges. However, I have to hardcode the Workbook and Sheet names that are used for the source names. My piece of code is: With ChtObj.Chart.SeriesCollection(jbl) .Values = "=Book3.xls!Sheet1_COL_" & ColAry(z) .XValues = "=Book3.xls!Sheet1_Date" End With I want the code to handle the workbook & sheet names more dynamically to accept a variable for wbk name or sheet name (or number). I can't get any syntax to work. Experts Help! Thank you. Kohai ...

Description of Statistical functions in Excel 2002
Hello, I am looking for the English description of the statistical functions in Excel 2002 Could some one mail me the text of this help file in a doc file or show me a website where this is shown? The Help function has a page with a listing and description of all the statistical functions. I hope someone can help me. Best regards, You can copy and paste the text from Help into Word. Jerry Erik Gerets wrote: > Hello, > > I am looking for the English description of the statistical functions in > Excel 2002 > > Could some one mail me the text of this help file in a d...

Excel 2002 document not opening with a proper view in Excel 2003
On opening an Excel 2002 document, I do not get the actual text in column names or values. For example Name is showing as #NAME? and not "Kevin Depeter". You might want to widen the column. It might not have enough room to show the name. "taj" wrote: > > On opening an Excel 2002 document, I do not get the actual text in column > names or values. For example Name is showing as #NAME? and not "Kevin > Depeter". ...

Date and Day in Excel
I have 12 Sheets, each representing each month of the year...Jan, feb......Dec. I need Date and Day in 1st and 2nd colum of each sheet representing that month. Need to know how to formulate this...Thanks in advance for any help.. Hi try the following formula to get the first day of the current year for this montn: =DATEVALUE("1-" & RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND ("]",CELL("filename",A1),1)) & "-" & TEXT(TODAY(),"YYYY") If you put this in cell A1 you could use the following formula in B...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Excel Colours
Hi all Each Excel workbook can hold a palette of 56 colours, why is it tha the colour picker tool on the Formatting toolbar will only show 4 colours (on a 5 x 8 grid)? Thanks for anyone supplying enlightenment! DJ -- DJ ----------------------------------------------------------------------- DJB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2122 View this thread: http://www.excelforum.com/showthread.php?threadid=38983 I think it's the same reason hotdogs are sold in packages of 10, but hotdog buns come in packages of 8. Who knows why? But you could r...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Formula Does not work on excel 2003
This formula does not work in excel 2003. Does anyone know how to convert it to the correct formula for excel 2003. It works perfectly on my system with 2007. But when I put it on a system with 2003 it returns nothing. What it is supposed to do is pull data from a sheet name in the format dd.mm.yy. Sometimes the sheet is not there so should return nothing or 0. =IF(ISERROR(INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")),"",INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")) Your formula work...

Turn off gridline in partial Excel worksheet
I know how to turn off gridlines for an entire worksheet, but is it possible to turn off for just a portion of a sheet? I am pasting a link into PowerPoint of a partial worksheet, but it brings across the gridlines. Cannot just copy a picture, as I need the dynamic link for updating the data. Thanks for any ideas! Hi, it's impossible. You can only change formatting of selected cells to white color. "Queen Glitter Bunny" wrote: > I know how to turn off gridlines for an entire worksheet, but is it possible > to turn off for just a portion of a sheet?...

How do you sort in Excel by 1, 1a, 1b, 1c, ...2, 2a?
I want to sort a list of numbers in excel but I want 1A to be in between 1 & 2. Hos do I do that? I tried setting up a custom list, but no luck. To get the sort order you want all the items must be text. For example, select A1:A3. Use Format, Cells and select Text as the number format. Enter 1, 2, 1a in the cells and then click the Sort button on the Standard toolbar. The order should sort to 1, 1a, 2. -- Jim Rech Excel MVP "MChapAcct" <MChapAcct@discussions.microsoft.com> wrote in message news:2A7B3324-66D0-4DA0-A339-55FA29F3FD1A@microsoft.com... |I want to so...

.NET equivalent to XSLT value-of select
This seems like it should be really easy, but I cannot seem to make it work. I am trying to retrieve the text value of an element named "child2Element" from an XML file in a .NET (v 1.1) with an XPath expression. In an XSLT document I would use <xsl:value-of select="rootElement/child1Element[@childId='110']/child2Element"/> (and this does work fine in a transform). But I cannot seem to find the right method or object in .NET. There seem to be a number of classes that can use XPath, but I can't figure out which one I have to use to get just the te...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Program Error -- EXCEL.exe has generated errors and will be closed by Windows.
Program Error -- EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. [OK] I get this message almost everytime I open Excel now. I have Office Standard 2003 SR-1 and have all the patches from Windows Update. I have not made any changes to Excel or Office in the last several months -- no patches, etc. I ran the Help...Detect & Repair... feature and this still happens. It's about 8/10 times I try to open Excel I get this message now. Any advice??? THanks! Hi Pablo! Close down and re-boot. Empty your Wind...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Deleting a note keeps the value in the NOTEINDX field
I just discovered that if you delete a note from a document (in my case, a cash receipts document), the system will delete the actual note record from the SY03900 table but will leave the original NOTEINDX value on the document record intact. Is this normal behavior? I would think the system should clear out the value in the document record since the note no longer exists. Referential integrity! -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 Yes, this is normal for GP. The record is created the first time the user clicks the note icon and "bookmarks...

Cannot connect Outlook 2003 to Exchange
I have a Windows XP PC, fully patched with Office 2003 connecting to a SBS2003 network. Everything has been working fine until this week. Now Outlook is unable to connect to Exchange, apparently with no cause. I have created new user profiles, uninstalled and re-installed office into a clean folder, removed the PC from the domain and re-instated it, stopped and restarted all the Exchange services all to no avail. I have also removed all the outlook profiles and created new ones. The only anomaly I have is that when I create a new Outlook profile and specify the server (which i did by IP) and t...

How do I print labels from an Excel spreadsheet #2
My wife has a simple spreadsheet that contains the following columns: Artist Title Medium Price She wants to print individual labels for each art piece. How do we do this? Put the labels Artist, Title, Medium, and Price in row 1, each in a separate column. You can use edit/paste special, transpose to rearrange your data. Then use the file as the data source for mail merge in MS Word. In Word, select Tools/Mail Merge and follow the directions. Regards, Michael "Scott" wrote: > My wife has a simple spreadsheet that contains the following columns: > Artist > Title >...

Reference cell values from other sheets in a function
I am trying to do something that seems like it would be quite simple, and yet I cannot get it to work. I have some data in separate work sheets (one sheet for each month), and a yearly summary sheet. I have a DAVERAGE function in each month. I am trying to take that DAVERAGE on each monthly sheet and make an AVERAGE function in the year summary page referencing each DAVERAGE cell on the monthly sheets. I enter =AVERAGE( into the cell and click over to the other sheets where I need to pull the info from. I click on the DAVERAGE cell that I need to reference in the yearly AVERAGE fu...

Bringing Word doc into Excel
Need to bring or import a word doc containing tables into excel. How does one go about doing this??? Any help is greatly appreciated ~ Thanks in advance. Select all the cells in the Word Table, copy, and then paste into an Excel spreadsheet. HTH "Gary" wrote: > Need to bring or import a word doc containing tables into > excel. How does one go about doing this??? > > Any help is greatly appreciated ~ Thanks in advance. > ...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...