Delete multiple columns from Excel Spreadsheet from VB.Net application?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Will
    #1

    Delete multiple columns from Excel Spreadsheet from VB.Net application?

    Can someone help with code to delete multiple columns from an excel
    spreadsheet? I know which columns I need to delete. The code below will
    delete a single column but I'm not sure how to delete multiple columns. I'm
    tried experimenting with Dim rg As Excel.Range = xlSheet.Columns ("B, D, G,
    K, L") but no joy. Thanks in advance


    Dim xlApp As Excel.Applicati on
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet

    xlApp = CreateObject("E xcel.Applicatio n")
    xlBook = xlApp.Workbooks .Open("D:\Augus t_Sales.xls")
    xlSheet = xlBook.Workshee ts(1)
    Dim rg As Excel.Range = xlSheet.Columns ("B")
    rg.Select()
    rg.Delete()
    xlBook.Save()
    xlApp.Quit()
    System.Runtime. InteropServices .Marshal.Releas eComObject(xlBo ok)
    System.Runtime. InteropServices .Marshal.Releas eComObject(xlAp p)
    xlSheet = Nothing
    xlBook = Nothing
    xlApp = Nothing

    Tags: xlsheet.column
    • James Hahn
      #2
      Re: Delete multiple columns from Excel Spreadsheet from VB.Net application?

      Is there some reason that you wouldn't just repeat

      Dim rg As Excel.Range = xlSheet.Columns ("B")
      rg.Select()
      rg.Delete()

      for the other columns, using the letters for those columns (working from
      right to left, of course)?

      "Will" <willedonNOSPAM @here.comwrote in message
      news:ul2a2qbJJH A.1556@TK2MSFTN GP03.phx.gbl...
      Can someone help with code to delete multiple columns from an excel
      spreadsheet? I know which columns I need to delete. The code below will
      delete a single column but I'm not sure how to delete multiple columns.
      I'm tried experimenting with Dim rg As Excel.Range = xlSheet.Columns ("B,
      D, G, K, L") but no joy. Thanks in advance
      >
      >
      Dim xlApp As Excel.Applicati on
      Dim xlBook As Excel.Workbook
      Dim xlSheet As Excel.Worksheet
      >
      xlApp = CreateObject("E xcel.Applicatio n")
      xlBook = xlApp.Workbooks .Open("D:\Augus t_Sales.xls")
      xlSheet = xlBook.Workshee ts(1)
      Dim rg As Excel.Range = xlSheet.Columns ("B")
      rg.Select()
      rg.Delete()
      xlBook.Save()
      xlApp.Quit()
      System.Runtime. InteropServices .Marshal.Releas eComObject(xlBo ok)
      System.Runtime. InteropServices .Marshal.Releas eComObject(xlAp p)
      xlSheet = Nothing
      xlBook = Nothing
      xlApp = Nothing

        Comment

        • Family Tree Mike
          #3
          Re: Delete multiple columns from Excel Spreadsheet from VB.Net application?

          That "could" work. The problem is that when you delete column "B", the
          columns to the right are moved left. You need to be aware of that when
          addressing the subsequent columns. A way arround that would be to delete
          columns from right to left, as L, K, G, D, B.


          "James Hahn" <jhahn@yahoo.co mwrote in message
          news:uDqtFjfJJH A.4936@TK2MSFTN GP04.phx.gbl...
          Is there some reason that you wouldn't just repeat
          >
          Dim rg As Excel.Range = xlSheet.Columns ("B")
          rg.Select()
          rg.Delete()
          >
          for the other columns, using the letters for those columns (working from
          right to left, of course)?
          >
          "Will" <willedonNOSPAM @here.comwrote in message
          news:ul2a2qbJJH A.1556@TK2MSFTN GP03.phx.gbl...
          >Can someone help with code to delete multiple columns from an excel
          >spreadsheet? I know which columns I need to delete. The code below will
          >delete a single column but I'm not sure how to delete multiple columns.
          >I'm tried experimenting with Dim rg As Excel.Range = xlSheet.Columns ("B,
          >D, G, K, L") but no joy. Thanks in advance
          >>
          >>
          > Dim xlApp As Excel.Applicati on
          > Dim xlBook As Excel.Workbook
          > Dim xlSheet As Excel.Worksheet
          >>
          > xlApp = CreateObject("E xcel.Applicatio n")
          > xlBook = xlApp.Workbooks .Open("D:\Augus t_Sales.xls")
          > xlSheet = xlBook.Workshee ts(1)
          > Dim rg As Excel.Range = xlSheet.Columns ("B")
          > rg.Select()
          > rg.Delete()
          > xlBook.Save()
          > xlApp.Quit()
          > System.Runtime. InteropServices .Marshal.Releas eComObject(xlBo ok)
          > System.Runtime. InteropServices .Marshal.Releas eComObject(xlAp p)
          > xlSheet = Nothing
          > xlBook = Nothing
          > xlApp = Nothing
          >

            Comment

            • James Hahn
              #4
              Re: Delete multiple columns from Excel Spreadsheet from VB.Net application?

              That's why I included in the original message the comment "(working from
              right to left, of course)?"


              "Family Tree Mike" <FamilyTreeMike @ThisOldHouse.c omwrote in message
              news:%23s$iu8hJ JHA.740@TK2MSFT NGP03.phx.gbl.. .
              That "could" work. The problem is that when you delete column "B", the
              columns to the right are moved left. You need to be aware of that when
              addressing the subsequent columns. A way arround that would be to delete
              columns from right to left, as L, K, G, D, B.
              >
              >
              "James Hahn" <jhahn@yahoo.co mwrote in message
              news:uDqtFjfJJH A.4936@TK2MSFTN GP04.phx.gbl...
              >Is there some reason that you wouldn't just repeat
              >>
              > Dim rg As Excel.Range = xlSheet.Columns ("B")
              > rg.Select()
              > rg.Delete()
              >>
              >for the other columns, using the letters for those columns (working from
              >right to left, of course)?
              >>
              >"Will" <willedonNOSPAM @here.comwrote in message
              >news:ul2a2qbJJ HA.1556@TK2MSFT NGP03.phx.gbl.. .
              >>Can someone help with code to delete multiple columns from an excel
              >>spreadsheet ? I know which columns I need to delete. The code below
              >>will delete a single column but I'm not sure how to delete multiple
              >>columns. I'm tried experimenting with Dim rg As Excel.Range =
              >>xlSheet.Colum ns("B, D, G, K, L") but no joy. Thanks in advance
              >>>
              >>>
              >> Dim xlApp As Excel.Applicati on
              >> Dim xlBook As Excel.Workbook
              >> Dim xlSheet As Excel.Worksheet
              >>>
              >> xlApp = CreateObject("E xcel.Applicatio n")
              >> xlBook = xlApp.Workbooks .Open("D:\Augus t_Sales.xls")
              >> xlSheet = xlBook.Workshee ts(1)
              >> Dim rg As Excel.Range = xlSheet.Columns ("B")
              >> rg.Select()
              >> rg.Delete()
              >> xlBook.Save()
              >> xlApp.Quit()
              >> System.Runtime. InteropServices .Marshal.Releas eComObject(xlBo ok)
              >> System.Runtime. InteropServices .Marshal.Releas eComObject(xlAp p)
              >> xlSheet = Nothing
              >> xlBook = Nothing
              >> xlApp = Nothing
              >>
              >

                Comment

                Previous template Next
                Working...