$excel = New-Object -ComObject excel.application
$month = Get-Date -UFormat “%b-%Y”
$excel.visible = $False
$outputpath = “C:\Steve\Mobile Scripts\Powershell\JP Mobile Phone Summary - $month - D3.xlsx”
Creates Workbook, Removes the extra tabs and Names the current Tab
$workbook = $excel.Workbooks.Add()
$workbook.Worksheets.Item(3).Delete()
$workbook.Worksheets.Item(2).Delete()
$uregwksht= $workbook.Worksheets.Item(1)
$uregwksht.Name = ‘VCO Billing’
#This section formats the whole workbook
$range = $excel.Range(‘A1’,‘AK2500’)
$range.Font.Size = 8
$range.Font.Name = ‘Arial’
$range.Font.ColorIndex = 1
$range.HorizontalAlignment = -4131
$range.Borders.Color = 1
$range.Borders.Weight = 2
$range.Font.Bold = $False
$excel.Rows.Item(“2:2”).Select()
$excel.ActiveWindow.FreezePanes = $true
#I’ve added the column headers here for the mobile report
$uregwksht.Cells.Item(1,1) = ‘Account’
$uregwksht.Cells.Item(1,2) = ‘Sub-account’
$uregwksht.Cells.Item(1,3) = ‘Allocation code 1’
$uregwksht.Cells.Item(1,4) = ‘Allocation code 2’
$uregwksht.Cells.Item(1,5) = ‘Allocation code 3’
$uregwksht.Cells.Item(1,6) = ‘Allocation code 4’
$uregwksht.Cells.Item(1,7) = ‘Phone number’
$uregwksht.Cells.Item(1,8) = ‘User name’
$uregwksht.Cells.Item(1,9) = ‘Recurring charges’
$uregwksht.Cells.Item(1,10) = ‘Recurring credits’
$uregwksht.Cells.Item(1,11) = ‘Usage charges’
$uregwksht.Cells.Item(1,12) = ‘Usage credits’
$uregwksht.Cells.Item(1,13) = ‘Other charges’
$uregwksht.Cells.Item(1,14) = ‘Other credits’
$uregwksht.Cells.Item(1,15) = ‘Total charge (ex VAT)’
$uregwksht.Cells.Item(1,16) = ‘VAT zero rated (tax code 1) - eligible for’
$uregwksht.Cells.Item(1,17) = ‘VAT exempt (tax code 2) - eligible for’
$uregwksht.Cells.Item(1,18) = ‘VAT standard rate (tax code 3) - eligible for’
$uregwksht.Cells.Item(1,19) = ‘VAT out of scope (tax code 4) - eligible for’
$uregwksht.Cells.Item(1,20) = ‘Calls while in the UK (cost)’
$uregwksht.Cells.Item(1,21) = ‘Calls while in the UK (volume)’
$uregwksht.Cells.Item(1,22) = ‘Calls while in the UK (duration)’
$uregwksht.Cells.Item(1,23) = ‘Roamed calls (cost)’
$uregwksht.Cells.Item(1,24) = ‘Roamed calls (volume)’
$uregwksht.Cells.Item(1,25) = ‘Roamed calls (duration)’
$uregwksht.Cells.Item(1,26) = ‘Messaging while in the UK (cost)’
$uregwksht.Cells.Item(1,27) = ‘Messaging while in the UK (volume)’
$uregwksht.Cells.Item(1,28) = ‘Roamed messaging (cost)’
$uregwksht.Cells.Item(1,29) = ‘Roamed messaging (volume)’
$uregwksht.Cells.Item(1,30) = ‘Mobile browsing & data while in the UK (cost)’
$uregwksht.Cells.Item(1,31) = ‘Mobile browsing & data while in the UK (data volume)’
$uregwksht.Cells.Item(1,32) = ‘Roamed mobile browsing & data (cost)’
$uregwksht.Cells.Item(1,33) = ‘Roamed mobile browsing & data (data volume)’
$uregwksht.Cells.Item(1,34) = ‘Purchases (cost)’
$uregwksht.Cells.Item(1,35) = ‘Purchases (volume)’
$uregwksht.Cells.Item(1,36) = ‘Price plan code’
$uregwksht.Cells.Item(1,37) = ‘Price plan description’
Adjusts the column width so all data’s properly visible
$usedRange = $uregwksht.UsedRange
$usedRange.EntireColumn.AutoFit() | Out-Null
#Save the file
$workbook.SaveAs($outputpath)
$excel.Quit()
Thats all my code, so far,Thank you