This topic contains 3 replies, has 2 voices, and was last updated by
July 10, 2018 at 1:01 pm #104180ParticipantTopics: 1Replies: 1Points: 0Rank: Member
I have a master spreadsheet of everyone in the companies phone bill and this is sorted by cost code, Each month I create 29 files for each cost code an email these to each manager.
I have gotten to the point where i can create all the files and format them correctly automatically, I am stuck on how to extract the data out of the master spreadsheet based upon the value of the cost code. For Example, My file ends in the managers name "DM" and I want to be able to copy all of the rows that have cost code of "DM" and past them in to the "DM" file.
Any help or guidance would be brilliant.
July 10, 2018 at 2:14 pm #104185ParticipantTopics: 5Replies: 15Points: 0Rank: Member
can we please see some code?
July 11, 2018 at 6:59 am #104249ParticipantTopics: 1Replies: 1Points: 0Rank: Member
$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()
$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.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
Thats all my code, so far,Thank you
July 11, 2018 at 7:46 am #104252ParticipantTopics: 5Replies: 15Points: 0Rank: Member
If I were you, I won't reinvent the wheel.
Use ImportExcel Module by Doug Finke from PowerShellGallery
After you Install module use the cmdlet Import-Excel to extract data out of excel
get-help Import-Excel -ShowWindow
To populate data back in each excel file based on your DM, use some of the Cmdlets that ImportExcel Module offers you
Here is an example
$FilePath = "C:\SomeExcelFile.xlsx" $Excel = Open-ExcelPackage -Path $FilePath $Sheet = $Excel.Workbook.Worksheets["Sheet1"] Set-Format -Address $Sheet1.Cells["A1"] -Value "Rows" Set-Format -Address $Sheet1.Cells["B1"] -Value "Values" Close-ExcelPackage $Excel
I hope this helps
The topic ‘Copy Cells based on Value in Excel’ is closed to new replies.