Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.