| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
Thanks Socratis -- your suggestion worked like a champ!
 ========================================= "Socratis" <(E-Mail Removed)> wrote in message news:FE9CE5BF-950E-4C7C-B422-(E-Mail Removed)... > 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. >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Doctorjones_md | Microsoft Excel Discussion | 1 | 27th Jun 2007 04:14 PM |
| Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Doctorjones_md | Microsoft Excel Programming | 3 | 27th Jun 2007 04:14 PM |
| Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Doctorjones_md | Microsoft Excel Misc | 3 | 27th Jun 2007 04:14 PM |
| code to input date after diff. cell selected | =?Utf-8?B?S2VsemluYQ==?= | Microsoft Excel Programming | 2 | 22nd Nov 2006 05:45 AM |
| Parameter query: how to display all if nothing is entered | =?Utf-8?B?fnNwb29reWd+?= | Microsoft Access Queries | 1 | 28th Feb 2006 02:24 AM |
