Advertisement
bradam

VBScript

Oct 27th, 2014
179
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Const xlUnderlineStyleNone = -4142
  2. Const xlThemeColorLight1 = 2
  3. Const xlThemeFontMinor = 2
  4. Const xlThemeColorLight2 = 4
  5. Const xlCenter = -4108
  6. Const xlBottom = -4107
  7. Const xlContext = -5002
  8. Const xlNone = -4142
  9. Const xlEdgeBottom = 9
  10. Const xlDouble = -4119
  11. Const xlThick = 4
  12. Const xlGeneral = 1
  13.  
  14. Set xl = CreateObject("Excel.Application")
  15. xl.Visible = True  'set to False for production
  16.  
  17. Set wb = xl.Workbooks.Open("C:\Users\rbelenyesi\Desktop\NNG\WarRoom\as.xlsx")
  18.  
  19. ' DailyWarRoom Macro
  20.  
  21. ' Format
  22.    wb.Sheets(1).Range("H2:H35").NumberFormat = "m/d/yyyy"
  23.     wb.Sheets(1).Range("S2:S35").NumberFormat = "m/d/yyyy"
  24.     wb.Sheets(1).Range("T2:T33").NumberFormat = "m/d/yyyy"
  25.     wb.Sheets(1).Range("H2:H35").NumberFormat = "m/d/yyyy"
  26.    
  27.    
  28.     Application.CutCopyMode = False
  29.     With wb.Sheets("Sheet1").Range("A1:T1").Font
  30.         .Name = "Calibri"
  31.         .Size = 13
  32.         .Strikethrough = False
  33.         .Superscript = False
  34.         .Subscript = False
  35.         .OutlineFont = False
  36.         .Shadow = False
  37.         .Underline = xlUnderlineStyleNone
  38.         .ThemeColor = xlThemeColorLight1
  39.         .TintAndShade = 0
  40.         .ThemeFont = xlThemeFontMinor
  41.         .Bold = True
  42.         .ThemeColor = xlThemeColorLight2
  43.         .TintAndShade = 0
  44.     End With
  45.  
  46.    
  47.     With wb.Sheets("Sheet1").Range("A1:T1")
  48.         .HorizontalAlignment = xlCenter
  49.         .VerticalAlignment = xlBottom
  50.         .Orientation = 0
  51.         .AddIndent = False
  52.         .IndentLevel = 0
  53.         .ShrinkToFit = False
  54.         .ReadingOrder = xlContext
  55.         .MergeCells = False
  56.         .Borders(xlDiagonalDown).LineStyle = xlNone
  57.         .Borders(xlDiagonalUp).LineStyle = xlNone
  58.         .Borders(xlEdgeLeft).LineStyle = xlNone
  59.         .Borders(xlEdgeTop).LineStyle = xlNone
  60.     End With
  61.    
  62.  
  63.     With wb.Sheets("Sheet1").Range("A1:T1").Borders(xlEdgeBottom)
  64.         .LineStyle = xlDouble
  65.         .Color = -1389396
  66.         .TintAndShade = 0
  67.         .Weight = xlThick
  68.         .Borders(xlEdgeRight).LineStyle = xlNone
  69.         .Borders(xlInsideVertical).LineStyle = xlNone
  70.         .Borders(xlInsideHorizontal).LineStyle = xlNone
  71.     End With
  72.  
  73.  
  74.     With wb.Sheets("Sheet1").Range("A1:T1")
  75.         .HorizontalAlignment = xlGeneral
  76.         .Orientation = 0
  77.         .AddIndent = False
  78.         .IndentLevel = 0
  79.         .ShrinkToFit = False
  80.         .ReadingOrder = xlContext
  81.         .MergeCells = False
  82.     End With
  83.    
  84.     With wb.Sheets("Sheet1").Range("A1:T1")
  85.         .HorizontalAlignment = xlCenter
  86.         .Orientation = 0
  87.         .AddIndent = False
  88.         .IndentLevel = 0
  89.         .ShrinkToFit = False
  90.         .ReadingOrder = xlContext
  91.         .MergeCells = False
  92.     End With
  93.    
  94.     With wb.Sheets("Sheet1").Range("A1:T1")
  95.         .HorizontalAlignment = xlCenter
  96.         .VerticalAlignment = xlBottom
  97.         .Orientation = 0
  98.         .AddIndent = False
  99.         .IndentLevel = 0
  100.         .ShrinkToFit = False
  101.         .ReadingOrder = xlContext
  102.         .MergeCells = False
  103.     End With
  104.    
  105.     With wb.Sheets("Sheet1").Range("A1:T1")
  106.         .HorizontalAlignment = xlCenter
  107.         .VerticalAlignment = xlCenter
  108.         .Orientation = 0
  109.         .AddIndent = False
  110.         .IndentLevel = 0
  111.         .ShrinkToFit = False
  112.         .ReadingOrder = xlContext
  113.         .MergeCells = False
  114.     End With
  115.    
  116.     With wb.Sheets("Sheet1").Range("C:C")
  117.         .HorizontalAlignment = xlLeft
  118.         .VerticalAlignment = xlCenter
  119.         .WrapText = False
  120.         .Orientation = 0
  121.         .AddIndent = False
  122.         .IndentLevel = 0
  123.         .ShrinkToFit = False
  124.         .ReadingOrder = xlContext
  125.         .MergeCells = False
  126.     End With
  127.    
  128.     With wb.Sheets("Sheet1").Range("C1")
  129.         .HorizontalAlignment = xlCenter
  130.         .VerticalAlignment = xlCenter
  131.         .WrapText = False
  132.         .Orientation = 0
  133.         .AddIndent = False
  134.         .IndentLevel = 0
  135.         .ShrinkToFit = False
  136.         .ReadingOrder = xlContext
  137.         .MergeCells = False
  138.     End With
  139.    
  140.     wb.Sheets("Sheet1").Range("B1").value = "Ticket ID"
  141.    
  142.     wb.Sheets("Sheet1").Range("C1").value = "Title"
  143.  
  144.     wb.Sheets("Sheet1").Range("D1").value = "Ticket step"
  145.  
  146.     wb.Sheets("Sheet1").Range("E1").value = "Priority"
  147.    
  148.     wb.Sheets("Sheet1").Range("G1").value = "Note"
  149.  
  150.     wb.Sheets("Sheet1").Range("H1").value = "Reported on"  
  151.    
  152.     wb.Sheets("Sheet1").Range("I1").value = "Fix Planned at"
  153.  
  154.     wb.Sheets("Sheet1").Range("J1").value = "Mantis ID"
  155.  
  156.    
  157.     wb.Sheets("Sheet1").Columns("G:G").ColumnWidth = 40.71
  158.     wb.Sheets("Sheet1").Columns("J:J").ColumnWidth = 10.71
  159.     wb.Sheets("Sheet1").Range("F:F").EntireColumn.Hidden = True
  160.  
  161.     wb.Sheets("Sheet1").Range("N:Q").EntireColumn.Hidden = True
  162.    
  163.    
  164.     wb.Sheets("Sheet1").Range("K2")
  165.     Do Until IsEmpty(wb.Sheets("Sheet1").Range("K2"))
  166.         If wb.Sheets("Sheet1").Range("K2").Value = "closed" Then
  167.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(232, 232, 232)
  168.         End If
  169.    
  170.         If wb.Sheets("Sheet1").Range("K2").Value = "rejected" Then
  171.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(163, 132, 114)
  172.         End If
  173.        
  174.         If wb.Sheets("Sheet1").Range("K2").Value = "released" Then
  175.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(187, 187, 187)
  176.         End If
  177.        
  178.         If wb.Sheets("Sheet1").Range("K2").Value = "tested" Then
  179.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(170, 255, 170)
  180.         End If
  181.        
  182.         If wb.Sheets("Sheet1").Range("K2").Value = "devtested" Then
  183.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(188, 221, 74)
  184.         End If
  185.        
  186.         If wb.Sheets("Sheet1").Range("K2").Value = "integrated" Then
  187.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(44, 212, 127)
  188.         End If
  189.        
  190.         If wb.Sheets("Sheet1").Range("K2".Value) = "resolved" Then
  191.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(204, 238, 221)
  192.         End If
  193.        
  194.         If wb.Sheets("Sheet1").Range("K2").Value = "assigned" Then
  195.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(200, 200, 255)
  196.         End If
  197.        
  198.         If wb.Sheets("Sheet1").Range("K2").Value = "confirmed" Then
  199.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 255, 176)
  200.         End If
  201.        
  202.         If wb.Sheets("Sheet1").Range("K2").Value = "assignedtorepro" Then
  203.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(238, 152, 4)
  204.         End If
  205.        
  206.         If wb.Sheets("Sheet1").Range("K2").Value = "acknowledged" Then
  207.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 216, 80)
  208.         End If
  209.        
  210.         If wb.Sheets("Sheet1").Range("K2").Value = "rejectcandidate" Then
  211.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(246, 64, 81)
  212.         End If
  213.        
  214.         If wb.Sheets("Sheet1").Range("K2").Value = "feedback" Then
  215.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 80, 168)
  216.         End If
  217.        
  218.         If wb.Sheets("Sheet1").Range("K2").Value = "new" Then
  219.         wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 160, 160)
  220.         End If
  221. Loop
  222.  
  223.     wb.Sheets("Sheet1").Range("A1:T30").RowHeight=15
  224.  
  225.     ' Formatting notes
  226.     With wb.Sheets("Sheet1").Range("G2:G9")
  227.         .HorizontalAlignment = xlLeft
  228.         .VerticalAlignment = xlCenter
  229.         .WrapText = False
  230.         .Orientation = 0
  231.         .AddIndent = False
  232.         .IndentLevel = 0
  233.         .ShrinkToFit = False
  234.         .ReadingOrder = xlContext
  235.         .MergeCells = False
  236.     End With
  237.    
  238. wb.Save
  239. x1.Quit
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement