Peter Chan
asked on
Fill with background color
Hi,
I can see the background color of the cells is like the attached. How to put the same background color to other cells, using VBA codes?
t991.png
I can see the background color of the cells is like the attached. How to put the same background color to other cells, using VBA codes?
t991.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?
only from checking the recordset property (rowcount) after the action;
only from checking the recordset property (rowcount) after the action;
ASKER
what should be the syntax of rowcount? Thanks
recordset.recordcount:
http://www.w3schools.com/asp/prop_rs_recordcount.asp
http://www.w3schools.com/asp/prop_rs_recordcount.asp
ASKER
Hi,
I'm retrieving records from AS 400, using these
I don't know why cnt in above does return "-1", while I can see there are 18 records retrieved already.
I'm retrieving records from AS 400, using these
Dim cn As ADODB.Connection
Dim strCNT As String, strSQL As String, strSQL2 As String, strSQL3 As String, strSQL4 As String, strSQL5 As String, strSQL6 As String
Dim cnt As Integer, rw As Integer
Dim rs4 As ADODB.Recordset
Dim rs5 As ADODB.Recordset
Dim rs6 As ADODB.Recordset
strCNT = IS5ConnectionString()
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionTimeout = SQL_TIMEOUT
cn.Open strCNT
strSQL4 = "SELECT SAAB,SAAN,SAAS,SAAPP,SABRNM,c.scact,SAPOD,SAPBR,SAPSQ,SAVFR,SADRF,SATCD,SACCY,SAAMA FROM " & HOST_LIB_EQ & "." & TBL_FM & " a," & HOST_LIB_EQ & "." & "scpf c where length(ltrim(rtrim(cast(SAPOD as char(12)))))=7 and substring(ltrim(rtrim(cast(SAPOD as char(12)))),2,6)='150130' and ltrim(rtrim(SACCY))='USD' and ltrim(rtrim(a.saan))=ltrim(rtrim(c.scan)) and ltrim(rtrim(a.saas))=ltrim(rtrim(c.scas)) and ltrim(rtrim(SAPBR))='@@AR'"
Set rs4 = CreateObject("ADODB.Recordset")
rs4.Open strSQL4, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
Worksheets("EQ System Gen FX").Cells.ClearFormats
Worksheets("EQ System Gen FX").Cells(2, 1).Value = "Overnight Position Revaluation"
Worksheets("EQ System Gen FX").Range("A5").CopyFromRecordset rs4
cnt = rs4.RecordCount
...
I don't know why cnt in above does return "-1", while I can see there are 18 records retrieved already.
I guess this is due to:
adOpenForwardOnly
which may have the effect of not filling that property
I don't see any other solution ...
adOpenForwardOnly
which may have the effect of not filling that property
I don't see any other solution ...
ASKER
Thanks. One last thing, what to assign to the cell if I expect to bracket the amount, when its value is negative?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks. I put these
but it is showing
(-9278769.0)
which is confusing, right?
Range("N" & (11 + cnt + cnt2)).NumberFormat = "#.##0;(-#.##0)"
Range("N" & (11 + cnt + cnt2)).Formula = "=Sum(N" & (11 + cnt) & ":N" & (10 + cnt + cnt2) & ")"
...
but it is showing
(-9278769.0)
which is confusing, right?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER