VBA report formatting

[yookd]

Hey guys,

So I am working with HP Quality Center to extract data from a database to format into an excel spreadsheet. I extracted most of the data that I needed and used the "Record Macro" function to come up with something really simple. However, this is hard-coded for only one line of data and there could be multiple lines of data.

I'll try to explain how the data is initally put into the excel sheet:
Row 1, Col 1, 2, 3, etc. : Has all the headers (Person responsible, project, project ID, etc.)
Row 2, Col 1, 2, 3, etc. : Has all the data for each of the headers

I am creating an engineer report, so there will only be 1 person responsible, but multiple projects, which extend down sequentially in the next row.

How can I check to see if data/text exists in Row 2, Col 2 (checking if there are multiple projects)? I want a function that can increment a counter inside of it as well so that when I format it all the data nicely, I can just use that variable to establish new ranges so that it is not hard-coded.

I don't know if you guys can visualize this so if you have any confusion about how it looks, just let me know.

Thanks a lot!

[yookd]

Another question:

Is there any way that I can see the script that creates a workbook/sheet? I have a generated report that I use as a template, but maybe I can somehow extract the script from it?

[Bob Phillips]

Shows us your recorded macro and we can improve it.

[yookd]

Attached is a sample report (my report has some different headers/information, but same idea).

This is what I currently have that is hardcoded:


Sub QC_PostProcessing()
Dim xlSheet1 As Worksheet
Set xlSheet1 = ActiveWorkbook.Worksheets("Sheet1")
Dim DataRange As Range
'Dim currentRow As Integer 'Used for variable cell values
'currentRow = 3 'x set to 2 to iterate through rows starting from row 2
'Set DataRange = MainWorksheet.UsedRange
' Now that you have the data in DataRange you can process it.
'''''' This will not ask you if you want to overwrite existing file '''''
'''''' Set to True if you want to save previous file '''''
Application.DisplayAlerts = False
''''' Rename the sheet to "Engineer Report" '''''
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Engineer Report"
''''' Bold the headers '''''
Rows("1:1").Font.Bold = True
''''' Puts data into a specific column '''''
Rows("1:2").Cut
Rows("5:5").Select
ActiveSheet.Paste
Rows("6:6").Cut
Rows("7:7").Select
ActiveSheet.Paste
''''' Format data '''''
Range("C7").Select 'Get newly placed Summary
Columns("C:C").ColumnWidth = 46.43 'Set Column Width of Summary
Columns("B:B").ColumnWidth = 10.86 'Set Column Width of Bug ID
With Selection
.HorizontalAlignment = xlGeneral 'Format Summary
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").ColumnWidth = 11.57 'Get newly placed responsible engineer & set Column Width
Range("B7").Select 'Get Bug ID
With Selection
.HorizontalAlignment = xlLeft 'Format Bug ID
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("5:5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
''''' Headers '''''
Range("A6:L6").MergeCells = True
''''' Title '''''
Range("A1:L1").Select 'Merges and centers cells for title
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
.Font.Size = 14
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Time Report For " 'INPUT ENGINEER NAME
''''' Dates '''''
Range("D5").Select
ActiveCell.FormulaR1C1 = "Mon"
Range("E5").Select
ActiveCell.FormulaR1C1 = "Tues"
Range("F5").Select
ActiveCell.FormulaR1C1 = "Wed"
Range("G5").Select
ActiveCell.FormulaR1C1 = "Thurs"
Range("H5").Select
ActiveCell.FormulaR1C1 = "Fri"
Range("I5").Select
ActiveCell.FormulaR1C1 = "Sat"
Range("J5").Select
ActiveCell.FormulaR1C1 = "Sun"
Range("L5").Select
ActiveCell.FormulaR1C1 = "Total"
Range("L7:L7").Select
Selection.HorizontalAlignment = xlCenter
''''' Add borders to data '''''
'Windows("Engineer report.xlsx").Activate
'Windows("Test.xls").Activate
Range("A7:L7").Borders(xlDiagonalDown).LineStyle = xlNone
Range("A7:L7").Borders(xlDiagonalUp).LineStyle = xlNone
Range("A7:L7").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
''''' Spacing of Date columns '''''
Columns("G:J").ColumnWidth = 5.86
Columns("K:K").ColumnWidth = 2.0
Columns("L:L").ColumnWidth = 5.86
''''' Find sum of hours worked on one project '''''
Range("L7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-8]:RC[-2])"
End Sub

[Bob Phillips]

It would help to see the before sheet as well.

[yookd]

Attached is what the SQL query spits out onto the excel sheet without any post-processing.

I still need to figure out how to extract hours worked from the database so that info is not included in the spreadsheet as of now.

[yookd]

Does anyone have any idea?