Zachsmom | Wednesday, September 18, 2002 - 10:07 pm     I need help with an excel procedure. I will list the code if anyone has experience with VBA. Thanks!!!!!!!! |
Weinermr | Wednesday, September 18, 2002 - 10:10 pm     I use Excel every day, but I'm clueless with VBA. |
Zachsmom | Wednesday, September 18, 2002 - 10:16 pm     ((((((HUG))))))))) Thanks for responding though.. I am clueless (it seems) too..LOL |
Hillbilly | Thursday, September 19, 2002 - 03:15 am     Zachsmom Go ahead and list your code...I'm sure someone can help. |
Twiggyish | Thursday, September 19, 2002 - 06:03 am     Try here: http://www.excel-vba.com/ |
Zachsmom | Thursday, September 19, 2002 - 07:56 am     Okay Hillbilly..I'll post it..hopefully someone can help I am trying to populate 2 cells in a row from a SQL server database. When I run the program I only get 1 result. Private Sub InTreasury() Dim cnnSQL As ADODB.Connection Dim cmdSQL As ADODB.Command Dim rstSQL As ADODB.Recordset Dim lngTotalChecks As Long Dim lngTotalAmount As Long Dim dtTotalChecks As Date Dim rng As Range ' On Error Resume Next Set cnnSQL = New ADODB.Connection cnnSQL.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=prjMonitoring;Data Source=MARK" cnnSQL.Open 'Get Monitoring results Set cmdSQL = New ADODB.Command cmdSQL.ActiveConnection = cnnSQL cmdSQL.CommandType = adCmdText cmdSQL.CommandText = "SELECT totalFaceValues,totalFaceValueAmount,convert(nvarchar,dateAdd,1) as dateAdd " & _ "from Monitoring " & _ "where monitoringtypeID = 4" & _ "order by dateAdd" cmdSQL.Execute Set rstSQL = New ADODB.Recordset Set rstSQL.Source = cmdSQL rstSQL.Open 'If Not rstSQL.EOF And Not rstSQL.BOF Then lngTotalAmount = rstSQL.Fields("totalFaceValueAmount").Value lngTotalChecks = rstSQL.Fields("totalFaceValues").Value dtTotalChecks = rstSQL.Fields("dateAdd").Value 'End If Set rng = Range("C4:C314").Cells Dim i As Integer For i = 1 To rng.Cells.Count If rng(i).Cells.Value = dtTotalChecks Then rng(i).Offset(0, 3).Value = lngTotalChecks End If Next i For i = 1 To rng.Cells.Count If rng(i).Cells.Value = dtTotalChecks Then rng(i).Offset(0, 4).Value = FormatCurrency(lngTotalAmount, 2) End If Next i End Sub When running the query through Query analyzer these are the results totalFaceValues totalFaceValueAmount dateAdd --------------- -------------------- ------------- 334 6352882 08/30/02 1183 17439466 09/04/02 1135 16505701 09/05/02 711 12762174 09/06/02 644 11634293 09/16/02 (5 row(s) affected) The only row that is assigns any value is for 8/30/02 if I take out the "order by" clause then another date is assigned..Problem..only 1 row is being assigned!!! Thank you to anyone that can help!!!! |
Zachsmom | Thursday, September 19, 2002 - 07:58 am     grrrrrrrrrrrrr formatting for this messageboard kinda screwed up the code lines.. in the querry there are 3 columns you can kinda tell by the space between.. |
Twiggyish | Thursday, September 19, 2002 - 08:14 am     Zach, are you using an OLAP? This man is an expert: http://www.excel-vba.com/vba-excel-consultant.htm You can call him. I'm not sure how much he charges, but it can save you time and effort. |
Zachsmom | Thursday, September 19, 2002 - 08:25 am     Thanks Twiggy..I've been to his website before I posted here..I just tell my boss that I am looking into finding the solution and I have a day to browse the internet and post on TVCH all day..lol.. if worse comes to worse by the end of the day I'll call him.. but I am sure someone...somewhere knows the answer and it's probably 1 little line of code.. |
Twiggyish | Thursday, September 19, 2002 - 09:28 am     What exactly is your boss having you do? I know populate two cells.. but for what reason? Perhaps, we can find a solution. (I teach Excel for common use and Access *not SQL*) |
Zachsmom | Thursday, September 19, 2002 - 09:50 am     In a nutshell.. I don't know how much you know about databases so I'll keep it very simple.. All of our data is keep in SQLServer (bummer..LOL).. We have a program that enters all transaction into the database.. I have a table called monitoring that is a collection(summary) of all transaction.. I have an excel workbook that has about 10 different sheets that I need to get the values from the monitoring table (each worksheet represents 1 type of transaction that occurs in our program..but they are all the same)..one column in the spreetsheet(s) is a date colum..I am comparing the value of the excel date column with the value of the date column in my record set..I am then listing the values in an empty cell(s) ( a count column and an amout column).. My problem is I am only getting the value from 1 record..I think my Loop statement is incorrect because it's not going to the next recordset..I tried .MoveNext but that doesn't work!!! |
Twiggyish | Thursday, September 19, 2002 - 10:02 am     Hold on, I can help with integration. |
Twiggyish | Thursday, September 19, 2002 - 10:07 am     I work with Access Databases, specifically for use with web pages. Did you run an advanced filter?
|
Hillbilly | Thursday, September 19, 2002 - 10:12 am     Private Sub InTreasury() Dim cnnSQL As ADODB.Connection Dim cmdSQL As ADODB.Command Dim rstSQL As ADODB.Recordset Dim lngTotalChecks As Long Dim lngTotalAmount As Long Dim dtTotalChecks As Date Dim rng As Range ' On Error Resume Next Set cnnSQL = New ADODB.Connection cnnSQL.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=prjMonitoring;Data Source=MARK" cnnSQL.Open 'Get Monitoring results Set cmdSQL = New ADODB.Command cmdSQL.ActiveConnection = cnnSQL cmdSQL.CommandType = adCmdText cmdSQL.CommandText = "SELECT totalFaceValues,totalFaceValueAmount,convert(nvarchar,dateAdd,1) as dateAdd " & _ "from Monitoring " & _ "where monitoringtypeID = 4" & _ "order by dateAdd" cmdSQL.Execute Set rstSQL = New ADODB.Recordset Set rstSQL.Source = cmdSQL rstSQL.Open With rstSQL Do While Not .EOF and Not .BOF lngTotalAmount = rstSQL.Fields("totalFaceValueAmount").Value lngTotalChecks = rstSQL.Fields("totalFaceValues").Value dtTotalChecks = rstSQL.Fields("dateAdd").Value Set rng = Range("C4:C314").Cells Dim i As Integer For i = 1 To rng.Cells.Count If rng(i).Cells.Value = dtTotalChecks Then rng(i).Offset(0, 3).Value = lngTotalChecks rng(i).Offset(0, 4).Value = FormatCurrency(lngTotalAmount, 2) End If Next i .MoveNext Loop End With End Sub |
Zachsmom | Thursday, September 19, 2002 - 10:12 am     no..I didn't think I needed to..(?) I get 1 result.. let me look at advanced filter.. btw..thank you for your help!! |
Zachsmom | Thursday, September 19, 2002 - 10:19 am     HILLBILLY I LOVE YOU!!!! |
Hillbilly | Thursday, September 19, 2002 - 10:21 am     Well...better see if it works first!..LoL |
Twiggyish | Thursday, September 19, 2002 - 10:21 am     wooohooo!!! |
Zachsmom | Thursday, September 19, 2002 - 10:21 am     It does!!!! Thats why I posted the above!!!! |
Hillbilly | Thursday, September 19, 2002 - 10:22 am     Great...you just left out telling the 'with' part. |
Zachsmom | Thursday, September 19, 2002 - 10:22 am     Twiggy!! I REALLY appreciate all the time you took out of your day to help me too!! It means a lot to me..I mean it..a lot!!! |
Hillbilly | Thursday, September 19, 2002 - 10:23 am     Twiggy and I make a purty good team! |
Zachsmom | Thursday, September 19, 2002 - 10:24 am     isn't it always the small stuff??LOL.. one good thing about bb almost being over with is that I might have my concentration back!!! LOL |
Zachsmom | Thursday, September 19, 2002 - 10:26 am     Yes you do!! I hope I can return the favor to BOTH of you someday!!! hmmm..looks like I have the rest of the day to play!! LOL..shhhhhhhhhh don't tell the boss.. |
Whit4you | Thursday, September 19, 2002 - 06:22 pm     Gee and I was gonna give you the answer! (hehe just kidding! I've been outta the loop since the . prompt DBase) This thread sure makes me miss the good old days though..... |