Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing code
Two questions
The below works is everything needed? What does the last "with" statement really do?? Sub GetValues() Dim strDate As String strDate = Format(shtInput.Range("flyerdate"), "yyyy-mm-dd") With ActiveWorkbook.Connections("Query from DB2P").ODBCConnection .BackgroundQuery = False .CommandText = "SELECT TAYVPHIS_0.PERF_AS_OF_DT, " & _ "TAYVPHIS_0.PROD_NUM, " & _ "TAYVPHIS_0.INV_MED_CD, " & _ "TAYVPHIS_0.SUR_CHRG_IND, " & _ "TAYVPHIS_0.PERF_SINCE_INCEP, " & _ "TAYVPHIS_0.PERF_SINCE_INCLU, " & _ "TAYVPHIS_0.PERF_10_YR, " & _ "TAYVPHIS_0.PERF_5_YR, " & _ "TAYVPHIS_0.PERF_1_YR, " & _ "TAYVPHIS_0.PERF_3_MO" & Chr(13) & "" & Chr(10) & _ "FROM PRDDB2.TAYVPHIS TAYVPHIS_0" & Chr(13) & "" & Chr(10) & _ "WHERE (TAYVPHIS_0.PERF_AS_OF_DT={d '" & strDate & "' })" .CommandType = xlCmdSql .Connection = _ "ODBC;DSN=XXXX;UID=XXXXXX;IpAddress=XXXXXX;TcpPort =446;Location=xxxxxx;" .RefreshOnFileOpen = False .SavePassword = False .SourceConnectionFile = "" .SourceDataFile = "" .ServerCredentialsMethod = xlCredentialsMethodIntegrated .AlwaysUseConnectionFile = False End With With ActiveWorkbook.Connections("Query from DB2P") .Name = "Query from DB2P" .Description = "" End With ActiveWorkbook.Connections("Query from DB2P").Refresh End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing code
I haven't looked at the material for your first question, but to quickly answer your second question... the With statement is providing the object that the .Name and .Description (notice the "dots" in front of them) refers back to (which is the main purpose of the With statement... it relieves you of having to continually type the object over and over again for each property or method referencing that object). -- Rick (MVP - Excel) "Brad" wrote in message ... Two questions The below works is everything needed? What does the last "with" statement really do?? Sub GetValues() Dim strDate As String strDate = Format(shtInput.Range("flyerdate"), "yyyy-mm-dd") With ActiveWorkbook.Connections("Query from DB2P").ODBCConnection .BackgroundQuery = False .CommandText = "SELECT TAYVPHIS_0.PERF_AS_OF_DT, " & _ "TAYVPHIS_0.PROD_NUM, " & _ "TAYVPHIS_0.INV_MED_CD, " & _ "TAYVPHIS_0.SUR_CHRG_IND, " & _ "TAYVPHIS_0.PERF_SINCE_INCEP, " & _ "TAYVPHIS_0.PERF_SINCE_INCLU, " & _ "TAYVPHIS_0.PERF_10_YR, " & _ "TAYVPHIS_0.PERF_5_YR, " & _ "TAYVPHIS_0.PERF_1_YR, " & _ "TAYVPHIS_0.PERF_3_MO" & Chr(13) & "" & Chr(10) & _ "FROM PRDDB2.TAYVPHIS TAYVPHIS_0" & Chr(13) & "" & Chr(10) & _ "WHERE (TAYVPHIS_0.PERF_AS_OF_DT={d '" & strDate & "' })" .CommandType = xlCmdSql .Connection = _ "ODBC;DSN=XXXX;UID=XXXXXX;IpAddress=XXXXXX;TcpPort =446;Location=xxxxxx;" .RefreshOnFileOpen = False .SavePassword = False .SourceConnectionFile = "" .SourceDataFile = "" .ServerCredentialsMethod = xlCredentialsMethodIntegrated .AlwaysUseConnectionFile = False End With With ActiveWorkbook.Connections("Query from DB2P") .Name = "Query from DB2P" .Description = "" End With ActiveWorkbook.Connections("Query from DB2P").Refresh End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condensing VBA code | Excel Discussion (Misc queries) | |||
Condensing working code | Excel Programming | |||
Condensing Code | Excel Programming | |||
Need help condensing with-end with code | Excel Programming | |||
Trouble Condensing Code | Excel Programming |