Hi F,
Thanks for looking at the thread.
This is the extracted code which from the time of opening the excel workbook. The other code part is relevant to what happens in Access first.
Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
Dim wsSheet As Worksheet
xlapp.Application.ScreenUpdating = False
With xlapp
.Visible = True
.Workbooks.Open CurrentProject.Path & "\" & strFileName & ".xls"
For Each wsSheet In xlapp.Worksheets
wsSheet.Rows("1:5").Insert Shift:=xlDown
wsSheet.Range("b2").Value = "Casualty Team:"
wsSheet.Range("b3").Value = "Policies selected between:"
wsSheet.Range("b4").Value = "As at:"
wsSheet.Range("c2").Value = wsSheet.Range("A7").Value
wsSheet.Range("e4").Value = "Total Number of policies selected"
wsSheet.Range("f4").FormulaR1C1 = "=COUNT(C[-2])"
wsSheet.Range("c4") = Date
wsSheet.Columns("A:A").ClearContents
wsSheet.Columns("A:I").AutoFit
wsSheet.PageSetup.CenterHeader = wsSheet.Range("c2").Value
wsSheet.PageSetup.LeftFooter = "Printed: &D at &T"
wsSheet.PageSetup.Orientation = xlLandscape
wsSheet.PageSetup.Draft = False
wsSheet.PageSetup.PaperSize = xlPaperA4
wsSheet.PageSetup.Zoom = 100
wsSheet.PageSetup.PrintTitleRows = "$2:$6"
wsSheet.Range("b2:b4,e2:e4").Interior.ThemeColor = xlThemeColorDark1 '.TintAndShade = -0.149998474074526
wsSheet.Range("b2:b4,e2:e4").Interior.TintAndShade = -0.149998474074526 '
wsSheet.Range("d6").HorizontalAlignment = xlRight
wsSheet.Range("b2:f4").Borders.LineStyle = xlContinuous
wsSheet.Range("b2:f4").BorderAround xlDouble, xlThick
wsSheet.Columns("B:f").Font.Name = "Calibri"
Next wsSheet
xlapp.Application.ScreenUpdating = True
End With
Dim iCnt As Integer
Dim i As Integer
iCnt = xlapp.Application.Sheets.Count
For i = 1 To iCnt
xlapp.Application.Sheets(i).Name = "Peer Review " & i
Next