Copy Cells based on Value in Excel

This topic contains 3 replies, has 2 voices, and was last updated by  Codernator 1 week, 1 day ago.

  • Author
    Posts
  • #104180

    Steven Pullan
    Participant

    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.

  • #104185

    Codernator
    Participant

    can we please see some code?

  • #104249

    Steven Pullan
    Participant

    $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

    • #104252

      Codernator
      Participant

      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

You must be logged in to reply to this topic.