Is there a way I can automatically run a macro which formats a spreadsheet upon opening a spreadsheet.
I have database which exports a query to an Excel File, but each time I export the file i have to manually go into the spreadsheet and format the thing. Would be great if i could do the export and the spreadsheet be automatically formatted open opening.
Namliam The Mailman is absolutely right.
The code to modify is very powerful to do formats.
for example: Once your recordset is transfered and Column B is in sorted order.
Lets say there are one to many rows the same in Column B. So, the first instance should be bolded, the repeated ones should be turned to metalic gray - this makes it very readible, plus excel can filter out all the gray ones if needed with a filter.
My Excel object is named ObjXL The intMaxRecordCount is the number of rows returned rom the recordset. The intRowPosition is the cell where the record set begins in Excel (e.g. row 5)
This code starts at row 5 and very quickly looks at the cell above, if the cell above is different, it Bolds that row for B through D for that row.
If the cell is the same, it formats the cell to light metalic gray for B through D in the current row.
It loops all the way down the recordset.
In between - it can also look at (if value of this row in column F > 100 then set the background to Green) kind of options.
It can really dress up an otherwise drab set of data.
Code:
With ObjXL.ActiveWorkbook.ActiveSheet
'objxl.ActiveWorkbook.ActiveSheet
For i = intRowPos To intMaxRecordCount + intRowPos
If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
.Range(.Cells(i, "B"), .Cells(i, "D")).Font.FontStyle = "Bold"
.Cells(i, 33).Value = .Cells(i, 3).Value
Else
.Range(.Cells(i, "B"), .Cells(i, "D")).Font.ColorIndex = 16 'metalic gray
End If
Next i
End With
Now it is your turn, explain what the line
.Cells(i, 33).Value = .Cells(i, 3).Value
does, and what it would be used for.
