SharePoint 2007 List export to Excel 2007

Question

Good Morning,

I exported the contents of a SharePoint list to an Excel worksheet; however, I noticed the colors and formatting that have been applied to the data. This was done automatically as a part of the import process. I want to make changes to the worksheet’s formatting using a Macro without Excel severing the connection to the SharePoint list when I save it. Will that be possible? If so, can you please spell out in step by step detail what I need to do to make this happen? Thank you in advance.

Regards.

Answer 1

The easiest way is to record a new macro and then edit the macro to be re-usable.

Here is some basic, in-ellegant code that I used for a similar need:

ActiveSheet.ListObjects(1).TableStyle = ""  'change table style
Selection.AutoFilter    'remove auto-filter
Columns("A:A").Select
Selection.Delete Shift:=xlShiftleft    'delete first column
Rows("1:4").Select
Selection.Delete Shift:=xlShiftUp
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
    ActiveCell.FormulaR1C1 = "Task" & Chr(10) & "Status"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Task" & Chr(10) & "Description"
    Selection.ColumnWidth = 30

Cells.Select    ' Select All
With Selection        'Format Table Cells
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Font.Size = 9
End With
Rows("1:1").Select        'Align Headers to Bottom Center
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .Font.Bold = True
End With

ActiveWorkbook.Worksheets.Select
ActiveWorkbook.Worksheets(1).Name = "ProjectSchedule-" & Date$      'ChangeTabName

Range("A1").Select     ' Place cursor at the top left