fun excel vba and r code examples 072314 (1)

Information about fun excel vba and r code examples 072314 (1)

Published on July 24, 2014

Author: matthewtaekwilli

Source: authorstream.com

Content

PowerPoint Presentation: F u n R c o d e usi n g t h e sq l d f packag e. W o r ki n g with d a t es w a s har d t o d o w i th i n t h e sq l d f packa g e at firs t un t i l I f o un d s o m e c o d e sn i pp ets o n the i n ter n et. Th e sq l d f p a c ka g e is n o t h e a vi l y d oc u me n t e d s o he r e is a litt l e e x a m p le to whet y o u r t a st e s . Th es e are jus t s o me ra n d o m co d es I created to c o n struct a re p o rt for t h e tel e c o m in d u st r y. Of c o u rse it will n o t g i v e y o u any real r esults i t’s j u st an ex a m p le o f fu n ct i o n al sc ri p t: c13a < - sq l df( " sel e ct p r o g r a m, c o un t( p r o j_ n o ) as c o un t s 1 f r o m all d ata where pr o j _p h ase = ' FY 1 5' and pr o j _s t atus = ' A c ti v e' a n d civi l _ v en d o r = ' G NE T' gro u p b y p r o g r a m") c13b < - sq l df(" s ele c t p ro g r am, c o un t( p r o j_ n o ) as c o un ts2 f r o m all d ata whe r e p r o j_ph ase = ' F Y 15' and p r o j _ sta t u s = ' A cti v e' a n d c i vi l _v en d o r = ' G NE T' and nACT 0 93 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c1 3 c < - sq l df("s e l ect p r o g r a m, c o un t( p r o j_ n o ) as c o un t s 3 f r o m all d ata where pr o j _p h ase = ' FY 1 5' and pr o j _s t atus = ' A c ti v e' a n d c i vi l _ v en d o r = ' G NE T' and nACT 0 94 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c13d < - sq l df(" s ele c t p ro g r am, c o un t( p r o j_ n o ) as c o un ts4 f r o m all d ata whe r e p r o j_ph ase = ' F Y 15' and p r o j _ sta t u s = ' A cti v e' a n d c i vi l _ v en d o r = ' G NE T' and nACT 0 95 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c1 3 e < - sq l df("s e l ect p r o g r am, c o un t( p r o j_ n o ) as c o un ts5 f r o m all d ata whe r e p r o j_ph ase = ' F Y 15' and p r o j _ sta t u s = ' A cti v e' a n d c i vi l _ v en d o r = ' G NE T' and nACT 1 06 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c13f < - sq l df( " sel e ct p r o g r a m, c o un t( p r o j_ n o ) as c o u n t s 6 f r o m all d ata where pr o j _p h ase = ' FY 1 5' and pr o j _s t atus = ' A c ti v e' a n d civi l _ v en d o r = ' G NE T' and nACT 1 25 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c13g < - sq l df(" s ele c t p ro g r a m, c o un t( p r o j_ n o ) as c o un t s 7 f r o m all d ata where pr o j _p h ase = ' FY 1 5' and pr o j _s t atus = ' A c ti v e' a n d civi l _ v en d o r = ' G NE T' and nACT 1 26 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c13h < - sq l df(" s ele c t p ro g r am, c o un t( p r o j_ n o ) as c o un ts8 f r o m all d ata whe r e p r o j_ph ase = ' F Y 15' and p r o j _ sta t u s = ' A cti v e' a n d c i vi l _ v en d o r = ' G NE T' and nACT 1 49 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c13i < - sq l df(" s ele c t p r o g r a m, c o un t( p r o j_ n o ) as c o un t s 9 f r o m all d ata where pr o j _p h ase = ' FY 1 5' and pr o j _s t atus = ' A c ti v e' a n d civi l _ v en d o r = ' G NE T' and nACT 1 55 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") c13j < - sq l df( " sel e ct p r o g r a m, c o un t ( p r o j_ n o ) as c o un t s 10 fr o m all d a t a w h ere p r o j_ph ase = ' FY 1 5' and pr o j _ s tat u s = ' A cti v e' a n d c i vi l _ v e nd o r = ' G NE T' and nACT 1 60 <= ' 2 0 1 4 - 05 - 23' g r o u p b y p r o g r a m") fi n al13 < - sq l df(" s elect a.*, b.c o un ts2 fr o m c 1 3a a l eft j o in c 13b b using(p r o g ra m ) " ) fi n al1 3 c <- sq l df(" s ele c t a. * , b.c o un t s 3 f r o m fin al 1 3 a l eft j o in c 13c b using(pr o g r a m )") fi n al1 3 d < - sq l df("s e l ect a. * , b.c o un t s 4 f r o m fin al 1 3c a left j o in c 13d b using(p r o g r am ) " ) fi n al1 3 e < - sq l df( " sel e ct a. * , b.c o un t s 5 f r o m fin al 1 3d a left j o in c 13e b using(pr og r am ) " ) fi n al1 3 f < - sq l df(" s elect a.*, b. c o un t s 6 fr o m fin al1 3 e a left j o in c 13f b using(p r o g r a m )") fi n al1 3 g < - sq l df("s e l ect a. * , b.c o un t s 7 f r o m fin al 1 3f a left j o in c 13g b using(p r o g r am ) " ) fi n al1 3 h < - sq l df("s e l ect a. * , b.c o un t s 8 f r o m fin al 1 3g a left j o in c 13h b using(p r o g r am ) " ) PowerPoint Presentation: fi n al1 3 i < - sq l df(" s elect a.*, b. c o un t s 9 fr o m fin al1 3 h a l eft j o in c 13i b usi n g(p r o g r a m )") fi n al1 3 j < - sq l df(" s elect a.*, b. c o un t s 10 f r o m fin al 1 3i a left j o in c 13j b using(p r o g r a m )") al ld ata$ n A C T 0 9 3 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 0 9 3 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 0 9 4 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 0 9 4 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 0 9 5 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 0 9 5 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 1 0 6 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 1 0 6 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 1 2 5 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 1 2 5 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 1 2 6 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 1 2 6 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 1 4 9 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 1 4 9 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 1 5 5 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 1 5 5 ) , f o r mat = "% m/ %d/ % Y " )) al ld ata$ n A C T 1 6 0 < - as.c h a r acter(as . Da t e(as.c h ar a cter ( al ld ata$A C T 1 6 0 ) , f o r mat = "% m/ %d/ % Y " )) H ere’s a fun ex a m p le o f usi n g sql within E x cel v b a. It c an be v ery u s eful w h en fa c ed with r ecurr in g re p o rts b ecause i t e s se n tial l y b oi l s d own to push i n g a bu tt o n t o r efresh t h e r e su l ts. I tho ugh t th i s was p artic u la r l y in t eresti n g b ecause i t i n te g r a tes sq l s t at e ments within l oo ps . P r e tty c o o l if y o u ask m e! A g ai n , th i s i s c o d e I d e ve l o p ed th a t will n ot a ctual l y g i ve y o u any k i n d o f produ c t; h o w e v er, it may b e u s ef u l as a refe r en c e: P ub l i c S u b Q u e r y W o rksh e e t( szSQ L As String , rg S ta r t A s Ra ng e, wb W o rk B o o k As S tri ng ) Dim rs D ata As AD O DB. R e c o r ds e t Dim szC o nn e ct As Strin g O n Err o r G o T o Err H a nd l e r A p p l i cat i o n.S tat usBa r = " Retri e vi n g dat a ..... " 'Se t u p t h e c o nn e ct i o n s t ri n g to excel - th i s w o r kb oo k sz C o nn ect = " P r o v i d er = Mic r o s o f t. J et . O L E D B.4. 0 ;" & _ " D a ta S o u rce = " & wbWo r k B o o k & " ; " & _ "Ex t en d ed Pro p e r ties = E xc e l 8 . 0 ;" S e t r s Data = N e w A D O D B .Rec o r d s et ' Run t h e qu ery as a d C m d T e xt r s Data . Ope n szS Q L , sz C o nn ect, adO p en F o rwar d O n ly, a d L o c kRea dOn l y, ad C m d T e xt 'Ch e c k if d a ta is r et u r n ed PowerPoint Presentation: Se l ect i o n.Cl ear C o n te n ts If N o t r s D a ta.EOF T h en ' if the r e c o r d s et c o n ta i n s dat a pu t t h em o n the w o rksh e et r g Sta r t.Co p y Fr o m R eco r dse t r s Data El s e M sg B o x " There' s n o r e c o r d s t h a t matc h e s t h e qu e ry !!", v bCr i tical En d If 'C l o s e c o nn e ct i o n r s Data.C l o se 'Clea n u p and get o u t S e t r s Data = N o th in g A p p l i cat i o n.S tat usBa r = F alse E xi t S u b ErrH a nd ler: ' an e r ror o c cu r ed in t h e SQ L -sta te ment M sg B o x " Y o u r q u ery c o u l d n ot b e e x ecute d , t h e SQ L -sta t e ment is i n c o rre c t." S e t r s Data = N o th in g A p p l i cat i o n.S tat usBa r = F alse En d Su b S u b clearstu f f() Dim i As In t eger Dim m As I n te g er Dim p As I n t eger F or i = 1 To 8 W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & i ).A ct i vate W o r kb o o ks ( "der p s l t e a u t o mated 0 1 2 9 1 4 .xl s m") .W o rksh e et s ( " 2 0 1 4 L T E" & i). R a ng e("C 3 : H 2 5").Se l ect Se l ect i o n.Cl ear C o n te n ts W o r kb o o ks ( "der p s l t e a u t o mated 0 1 2 9 1 4 .xl s m") .W o rksh e et s ( " 2 0 1 4 L T E" & i). R a ng e("O 3 : T 2 5 ").Sele c t Se l ect i o n.Cl ear C o n te n ts W o r kb o o ks ( "der p s l t e a u t o mated 0 1 2 9 1 4 .xl s m") .W o rksh e et s ( " 2 0 1 4 L T E" & i). R a ng e("A A 3 :A F 2 5 ").Sel e ct PowerPoint Presentation: N ext m W o r kb o o ks ( "der p s l t e a u t o mated 0 1 2 9 1 4 .xl s m") .W o rksh e et s ( " 2 0 1 4 L T E" & i). R a ng e("A M 3: A R 2 5 ").Sel e ct Se l ect i o n.Cl ear C o n te n ts W o r kb o o ks ( "der p s l t e a u t o mated 0 1 2 9 1 4 .xl s m") .W o rksh e et s ( " 2 0 1 4 L T E" & i). R a ng e("AY 3 : B D 2 5").Se l ect Se l ect i o n.Cl ear C o n te n ts W o r kb o o ks ( "der p s l t e a u t o mated 0 1 2 9 1 4 .xl s m") .W o rksh e et s ( " 2 0 1 4 L T E" & i). R a ng e("B K 3: B P 2 5"). S elect Se l ect i o n.Cl ear C o n te n ts N ext i Dim t h eda t e As Da t e thed a te = F o rm a t ( Date, "m m/ d d / y y y y") Dim dts ( 1 T o 5) As Strin g d ts(1) = t h edate - 14 d ts(2) = t h edate - 7 d ts(3) = t h edate d ts(4) = t h edate + 7 d ts(5) = t h edate + 14 'd ts(6) = thed a te + 2 1 F or m = 1 T o 8 W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ).A c ti v a t e F or i = 0 To 6 6 S t ep 1 2 W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ). C e l ls( 2 , 3 + i) = dts ( 1) W o rk b o o ks ( "der p s l t e a u to ma t ed 0 129 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ). C e l ls( 2 , 4 + i) = dts ( 2) W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ). C e l ls( 2 , 5 + i) = " Y T D % C o m p l ete A C T vs fc s t" W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ). C e l l s ( 2, 6 + i) = dts ( 3) W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ). C e l ls( 2 , 7 + i) = dts ( 4) W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ). C e l ls( 2 , 8 + i) = dts ( 5) N ext i PowerPoint Presentation: Dim intPl a ceO u tp u t As I n t e g er En d Su b S u b sqls u b() Dim i As I nt eger 'cl u s t er Dim j As I nt eger ' d a t es Dim k As I n te g er 'ms Dim l As In t eger ' m sf Dim m As I n te g er 'sh e ets 1 - 8 Dim a As I nt eger Dim b As I n t eger Dim o As I n te g er Dim p As I n t eger ' b l o ck 6 Dim t h eda t e As Da t e Dim t h en a me A s Inte g er Dim t h er A s Integ er Dim t h ec As I n te g er Dim s t r 1 As Strin g Dim s t r 2 As Strin g Se t udf 7s = W o r kb o o ks ( "d e r p s lte au t o ma t ed 0 1 2 9 1 4 . xls m " ). W o rksh e et s ( "S h ee t 1").Ran g e ( "A2 " ) Dim rc1 As I n te g er rc1 = W o rk b o o ks("d e r p s lte aut o ma t ed 0 1 2 9 1 4 .xl s m") . W o r k she e ts("S he et 1 ").Ra n g e("A 1 0 0 0 0 ").En d ( xlU p ). R o w - 1 Dim rgPla c eO u tp u t A s Ra n g e Dim s t S Q L strin g As S tri n g 'f i rst cell f or the o u t p u t o f the qu e r y ' t ext o f the c ell c o n tai n i n g t h e S Q L s t a t e ment PowerPoint Presentation: m sf ( 3) = " FC S T 0 0 9" Dim ms( 1 T o 2 3) As Strin g m s ( 1) = "A C T 0 0 4" m s ( 2) = "A C T 0 0 6" m s ( 3) = "A C T 0 0 9" m s ( 4) = "A C T 0 1 2" m s ( 5) = "A C T 0 1 4" m s ( 6) = "A C T 0 1 8" m s ( 7) = "A C T 0 2 7" m s ( 8) = "A C T 0 3 0" m s ( 9) = "A C T 0 4 5" m s ( 10) = "ACT 07 1" m s ( 11) = "ACT 08 81" m s ( 12) = "ACT 09 4" m s ( 13) = "ACT 09 5" m s ( 14) = "ACT 09 7" m s ( 15) = "ACT 10 3" m s ( 16) = "ACT 1 0 6" m s ( 17) = "ACT 10 71" m s ( 18) = "ACT 10 9" m s ( 19) = "ACT 12 6" m s ( 20) = "ACT 14 3" m s ( 21) = "ACT 14 9" m s ( 22) = "ACT 15 5" m s ( 23) = "ACT 16 0" Dim msf( 1 T o 2 3) As Strin g m sf ( 1) = " FC S T 0 0 4" m sf ( 2) = " FC S T 0 0 6" PowerPoint Presentation: m sf ( 4) = " FC S T 0 1 2" m sf ( 5) = " FC S T 0 1 4" m sf ( 6) = " FC S T 0 1 8" m sf ( 7) = " FC S T 0 2 7" m sf ( 8) = " FC S T 0 3 0" m sf ( 9) = " FC S T 0 4 5" m sf ( 10) = "FC S T 0 7 1 " m sf ( 11) = "FC S T 0 8 8 1" m sf ( 12) = "FC S T 0 9 4 " m sf ( 13) = "FC S T 0 9 5 " m sf ( 14) = "FC S T 0 9 7 " m sf ( 15) = "FC S T 1 0 3 " m sf ( 16) = "FC S T 1 0 6 " m sf ( 17) = "FC S T 1 0 7 1" m sf ( 18) = "FC S T 1 09" m sf ( 19) = "FC S T 1 2 6 " m sf ( 20) = "FC S T 1 4 3 " m sf ( 21) = "FC S T 1 4 9 " m sf ( 22) = "FC S T 1 5 5 " m sf ( 23) = "FC S T 1 6 0 " thed a te = F o rm a t ( Date, "m m/ d d / y y y y") Dim dts ( 1 T o 5) As Strin g d ts(1) = " #" & thed a te - 1 4 & " # " d ts(2) = " #" & thed a te - 7 & "#" d ts(3) = " #" & thed a te & " # " d ts(4) = " #" & thed a te + 7 & " # " d ts(5) = " #" & thed a te + 14 & " # " 'd ts(6) = " # " & t h eda t e + 2 1 & " #" PowerPoint Presentation: o = 1 p = 6 F or m = 1 T o 8 ' shee t s thec = 3 then a m e = 1 W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ).A c ti v a t e F o r i = o T o p ' tab l es W o rk b o o ks ( "der p s l t e a u to ma t ed 0 1 2 9 1 4 .xl s m") . W o r kshee t s( " 2 0 1 4 L T E" & m ). C e l ls( 2 , then a m e) = udf 7 s( i ) F or j = 1 T o 2 ' dts ther = 3 F or k = 1 T o 23 ' m s stSQ L s tri n g = " sele c t c o un t ( " & m s (k ) & " ) f r o m dataal l w h ere " & m s( k ) & " < = " & dts(j ) & " and udf 7 = " & " ' " & udf 7 s(i ) & "'" 'str1 & st r 2 Se t r g Pl a ceO u tp u t = W o r k b o o ks("der p s lte au t o m a ted 0 1 2 9 14 .xl s m " ). W o rksh e et s ("2 0 1 4L T E " & m ).Cells ( ther, t h ec) Quer y W o r kshe e t st S QL str i ng , rgPlac e Outpu t, Thi s W o rkb o o k.F u l lN ame ther = ther + 1 N ext k thec = t h ec + 1 N ext j F or j = 3 T o 3 ' dts ther = 3 F or k = 1 T o 23 ' m s stSQ L s tri n g = " sele c t c o un t ( " & m s (k ) & " ) / c o un t(" & m sf( k ) & ") f r o m dat a all w h ere ( " & m s (k ) & " <= " & d t s(j ) & " o r " & m s f (k ) & " <= " & dts(j) & ") a n d udf 7 = " & "'" & u d f 7 s(i ) & "'" 'str1 & str2 Se t r g Pl a ceO u tp u t = W o r k b o o ks("der p s lte au t o m a ted 0 1 2 9 14 .xl s m " ). W o rksh e et s ("2 0 1 4L T E " & m ).Cells ( ther, t h ec) Quer y W o r kshe e t st S QL str i ng , rgPlac e Outpu t, Thi s W o rkb o o k.F u l lN ame ther = ther + 1 N ext k thec = t h ec + 1 N ext j PowerPoint Presentation: F or j = 3 T o 5 ' dts ther = 3 F or l = 1 To 2 3 ' ms stSQ L s tri n g = " sele c t c o un t ( " & m s f (l ) & ") fr o m da t aa l l where " & m sf(l ) & " <= " & d ts( j ) & " and udf 7 = " & "'" & ud f 7 s(i ) & "'" 'str1 & s tr2 Se t r g Pl a ceO u tp u t = W o r k b o o ks("der p s lte au t o m a ted 0 1 2 9 14 .xl s m " ). W o rksh e et s ("2 0 1 4L T E " & m ).Cells ( ther, t h ec) Quer y W o r kshe e t st S QL str i ng , rgPlac e Outpu t, Thi s W o rkb o o k.F u l lN ame ther = ther + 1 N ext l thec = t h ec + 1 N ext j thec = t h ec + 6 then a m e = then a me + 12 N ext i o = o + 6 p = p + 6 N ext m En d Su b

Related presentations