Windows 7 Excel Macro

Discussion in 'Programming and Scripting' started by rc0213, Oct 13, 2016.

  1. rc0213

    rc0213 New Member

    Joined:
    Oct 13, 2016
    Messages:
    6
    Likes Received:
    0
    Hi,

    I am trying to extract information from one program, then import it into Excel. Well, the format it is extracted is not the format that is what I need. So, I have been manually deleting all the unneeded rows. My question is there a macro or C-Plus program that can delete these rows for me?

    The rows I want to delete will not have data in the first column, where the ones I want to keep do. What is the easiest way to doing this, macro, C-Plus, etc.? And, if it is a macro, is there a macro you recommend? Or, can someone create this for me since I am not a programmer.

    Thank you for all your help!
     
  2. Neemobeer

    Neemobeer Windows Forum Team
    Staff Member

    Joined:
    Jul 4, 2015
    Messages:
    2,418
    Likes Received:
    366
    I can you with this in a bit, a powershell script could take care of this easily.
     
  3. Neemobeer

    Neemobeer Windows Forum Team
    Staff Member

    Joined:
    Jul 4, 2015
    Messages:
    2,418
    Likes Received:
    366
    Save this as a .ps1 file, open a powershell prompt and load it . <Path to this file>
    Then type Remove-Rows -FilePath "Path to excel file"

    Code:
    Function Remove-Rows
    {
        param (
        [string]$FilePath)
    
        $excel = New-Object -ComObject Excel.Application
        $excel.Visible = $false
        $workbook = $excel.Workbooks.Open($FilePath)
        $worksheet = $workbook.ActiveSheet()
        $rowCount = ($worksheet.UsedRange.Rows).Count
    
        
    
        for($row = 1;$row -lt $rowCount; $row++)
        {
           if(($($worksheet.Cells.Item($row,1).value2) -eq $null) -or ($($worksheet.Cells.Item($row,1).value2) -eq ""))
           {
                $deleteThis = $worksheet.Cells.Item($row,1).EntireRow
                $deleteThis.Delete()
                Write-Host "Delete this row: $row"
                $row--
           }
        }
        $workbook.Save()
        $excel.Workbooks.Close()
    }
     
    rc0213 likes this.
  4. rc0213

    rc0213 New Member

    Joined:
    Oct 13, 2016
    Messages:
    6
    Likes Received:
    0
    Thanks! The problem I have now is that the company disabled PowerShell on the system. I appreciate your help though.
     
  5. Neemobeer

    Neemobeer Windows Forum Team
    Staff Member

    Joined:
    Jul 4, 2015
    Messages:
    2,418
    Likes Received:
    366
    If you have admin rights, you can open an elevated powershell prompt and type Set-ExecutionPolicy -ExecutionPolicy Bypass to run scripts in the current window.
     
  6. rc0213

    rc0213 New Member

    Joined:
    Oct 13, 2016
    Messages:
    6
    Likes Received:
    0
    Thanks for all your help.
     

Share This Page

Loading...