Archive through September 19, 2002
MoveCloseDeleteAdmin

TV ClubHouse: Archives: HELP!! Anyone know VBA?? Have Excel Question!!!: Archive through September 19, 2002

Zachsmom

Wednesday, September 18, 2002 - 10:07 pm EditMoveDeleteIP
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 EditMoveDeleteIP
I use Excel every day, but I'm clueless with VBA.

Zachsmom

Wednesday, September 18, 2002 - 10:16 pm EditMoveDeleteIP
((((((HUG))))))))) Thanks for responding though..:)

I am clueless (it seems) too..LOL

Hillbilly

Thursday, September 19, 2002 - 03:15 am EditMoveDeleteIP
Zachsmom

Go ahead and list your code...I'm sure someone can help.

Twiggyish

Thursday, September 19, 2002 - 06:03 am EditMoveDeleteIP
Try here:

http://www.excel-vba.com/

Zachsmom

Thursday, September 19, 2002 - 07:56 am EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
Hold on, I can help with integration.

Twiggyish

Thursday, September 19, 2002 - 10:07 am EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
image



HILLBILLY


I LOVE YOU!!!!

Hillbilly

Thursday, September 19, 2002 - 10:21 am EditMoveDeleteIP
Well...better see if it works first!..LoL

Twiggyish

Thursday, September 19, 2002 - 10:21 am EditMoveDeleteIP
wooohooo!!!

Zachsmom

Thursday, September 19, 2002 - 10:21 am EditMoveDeleteIP
It does!!!! Thats why I posted the above!!!!

Hillbilly

Thursday, September 19, 2002 - 10:22 am EditMoveDeleteIP
Great...you just left out telling the 'with' part.

Zachsmom

Thursday, September 19, 2002 - 10:22 am EditMoveDeleteIP
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 EditMoveDeleteIP
Twiggy and I make a purty good team!

Zachsmom

Thursday, September 19, 2002 - 10:24 am EditMoveDeleteIP
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 EditMoveDeleteIP
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 EditMoveDeleteIP
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.....:)