Thanks Socratis -- your suggestion worked like a champ! :)
=========================================
"Socratis" <Socratis[ at ]discussions.microsoft.com> wrote in message
news:FE9CE5BF-950E-4C7C-B422-F86A839EB40C[ at ]microsoft.com...
[Quoted Text] > Try this:
>
> Worksheets("Sheet1").Range("A1").value = ProdID
>
> Replace "Sheet1" w/whatever sheet is appropriate and "A1" with a cell w/in
> that ws.
>
> HTH
>
> Cheers,
> socratis
>
> "Doctorjones_md" wrote:
>
>> I'm wondering how BEST to accomplish this.
>>
>> I have a EXCEL workbook which populates data to, and retrieves data from
>> SQL
>> Server.
>>
>> I currently have VBA code which effectively sends the data from the EXCEL
>> worksheet to SQL Server tables -- no problem with this.
>>
>> I currently have VBA code which effectively retrieves data from SQL
>> Server
>> and displays that data back into the worksheet -- no problem with this
>> either.
>>
>> I'm currently using a Command Button (on-click event) and the code below
>> to
>> query SQL Server to determine the Max Version Number and add 1 to that
>> value. This value is then passed to a worksheet cell, and rolled up to
>> SQL
>> Server along with the rest of the data to be submitted -- no problem
>> with
>> this either.
>>
>> Here's my "GenerateVersionNumber" code ...
>> ===============================
>> Public Sub cmdGenerateVersionNumber_Click()
>> ' Create a connection object.
>>
>> Dim cnExcel As ADODB.Connection
>>
>> Set cnExcel = New ADODB.Connection
>>
>>
>>
>> ' Provide the connection string.
>>
>> Dim strConn As String
>>
>>
>>
>> 'Make Version the active sheet & Clear Data
>>
>> 'ThisWorkbook.Worksheets("Version").Range("A2:A150").Clear
>>
>>
>>
>> 'Use the SQL Server OLE DB Provider.
>>
>> strConn = "PROVIDER=SQLOLEDB;"
>>
>>
>>
>> 'Connect to the XXXX database on the XXXX Server.
>>
>> strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _
>>
>> "User Id=xxxx;" & _
>>
>> "Password=xxxx"
>>
>> 'Now open the connection.
>>
>> cnExcel.Open strConn
>>
>>
>>
>> On Error Resume Next
>>
>>
>>
>> ' Create a recordset object.
>>
>> Dim ProdID As String
>>
>> Dim sqlCommand As String
>>
>> Dim rsExcel As ADODB.Recordset
>>
>> Set rsExcel = New ADODB.Recordset
>>
>> ProdID = InputBox("Enter Product ID.")
>>
>>
>>
>> sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
>> [ProductID] = '" + ProdID + "'"
>>
>> With rsExcel
>>
>> ' Assign the Connection object.
>>
>> .ActiveConnection = cnExcel
>>
>> ' Extract the required records.
>>
>> .Open sqlCommand
>>
>> ' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)
>>
>> Sheet15.Range("D2").CopyFromRecordset rsExcel
>>
>>
>>
>> ' Tidy up
>>
>> .Close
>>
>> End With
>>
>>
>>
>> cnExcel.Close
>>
>> Set rsExcel = Nothing
>>
>> Set cnExcel = Nothing
>>
>> End Sub
>>
>> ==============================
>>
>> Here's what I'm tring to do ....
>>
>> I don't want the Sale Team to have to enter ProductID more than once.
>> ProductID is a field that needs to be entered on a worksheet, and it's
>> also
>> a Query Parameter which I send to SQL Server to generate my One-Up
>> Version
>> Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
>> not only query SQL Server with the ProductID parameter entered in the
>> Input
>> Box, but to also send the value entered in the Input Box to a particular
>> cell on a selected worksheet?
>>
>>
>>
>> Thanks in advance for any assistance/advice offered.
>>
>>
>>
>>
>>
|