Results 1 to 5 of 5

Thread: [RESOLVED] How to loop all used cells certain column in a sheet vb.

  1. #1

    Thread Starter
    Lively Member elmnas's Avatar
    Join Date
    Jul 2009
    Posts
    127

    Resolved [RESOLVED] How to loop all used cells certain column in a sheet vb.

    hello people,

    I have got following code so far.

    I wonder how I make my VBA loop into VB.


    here is my VB CODE
    Code:
            Dim xlApp As Excel.Application
            Dim xlSht As Excel.Worksheet
            Dim xlRng As Excel.Range
            xlApp = New Excel.Application
    
            xlApp.DisplayAlerts = False
            xlApp.Workbooks.Open(C:\test.xls)
            xlSht = xlApp.Sheets(1)
            xlRng = xlSht.Cells(1, 1) ' here is just some sample code to get any result
            MsgBox(xlRng.Value) ' just a msgbox to alert cell content (1,1)
    here is my vba loop I need to convert to vb
    Code:
      For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).row
           Cells(i, "A").Value
        Next i
    Could someone help me?

    Thank you in advance
    Last edited by elmnas; Dec 2nd, 2015 at 08:49 AM.

  2. #2
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: How to loop all used cells certain column in a sheet vb.

    Add a COM reference to "Microsoft Excel version Object Library" to your project and use this code:

    Code:
    Option Compare Binary
    Option Explicit On
    Option Infer Off
    Option Strict On
    
    Imports Microsoft.Office.Interop
    Imports System
    
    Public Module Module1
       Public Sub Main()
          Dim xlApp As New Excel.Application With {.DisplayAlerts = False}
          Dim xlSht As Excel.Worksheet = Nothing
          Dim xlRng As Excel.Range = Nothing
    
          xlApp.Workbooks.Open("D:\Test1.xls")
          xlSht = DirectCast(xlApp.Sheets(1), Excel.Worksheet)
          xlRng = DirectCast(xlSht.Cells(1, 1), Excel.Range)
          Console.WriteLine(xlRng.Value)
    
          For i As Integer = 1 To DirectCast(xlApp.ActiveSheet, Excel.Worksheet).UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
             Console.WriteLine(DirectCast(xlSht.Cells(i, "A"), Excel.Range).Value)
          Next i
    
          Excel.Quit()
       End Sub
    End Module
    Last edited by Peter Swinkels; Dec 2nd, 2015 at 01:06 PM.

  3. #3

    Thread Starter
    Lively Member elmnas's Avatar
    Join Date
    Jul 2009
    Posts
    127

    Re: How to loop all used cells certain column in a sheet vb.

    Quote Originally Posted by Peter Swinkels View Post
    Add a COM reference to "Microsoft Excel version Object Library" to your project and use this code:

    Code:
    Option Compare Binary
    Option Explicit On
    Option Infer Off
    Option Strict On
    
    Imports Microsoft.Office.Interop
    Imports System
    
    Public Module Module1
       Public Sub Main()
          Dim xlApp As New Excel.Application With {.DisplayAlerts = False}
          Dim xlSht As Excel.Worksheet = Nothing
          Dim xlRng As Excel.Range = Nothing
    
          xlApp.Workbooks.Open("D:\Test1.xls")
          xlSht = DirectCast(xlApp.Sheets(1), Excel.Worksheet)
          xlRng = DirectCast(xlSht.Cells(1, 1), Excel.Range)
          Console.WriteLine(xlRng.Value)
    
          For i As Integer = 1 To DirectCast(xlApp.ActiveSheet, Excel.Worksheet).UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
             Console.WriteLine(DirectCast(xlSht.Cells(i, "A"), Excel.Range).Value)
          Next i
    
          Excel.Quit()
       End Sub
    End Module
    Hi again

    Thank for for fast response I did as you told me to.


    Name:  2015-12-03 09_01_26-HAL_Statistics - Microsoft Visual Studio (Administrator).jpg
Views: 1086
Size:  22.3 KB

    Name:  2015-12-03 09_02_56-HAL_Statistics - Microsoft Visual Studio (Administrator).jpg
Views: 1122
Size:  66.8 KB

    I got this now...

    what is wrong?

    Thank you in advance.

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: How to loop all used cells certain column in a sheet vb.

    Quote Originally Posted by elmnas View Post
    Hi again

    Thank for for fast response I did as you told me to.


    Name:  2015-12-03 09_01_26-HAL_Statistics - Microsoft Visual Studio (Administrator).jpg
Views: 1086
Size:  22.3 KB

    Name:  2015-12-03 09_02_56-HAL_Statistics - Microsoft Visual Studio (Administrator).jpg
Views: 1122
Size:  66.8 KB

    I got this now...

    what is wrong?

    Thank you in advance.
    It is hard for me to see but it doesn't look like you added the suggested reference to me. Try posting code instead of screen prints. If someone was inclined to put the code into a project to help screen prints might make them change there mind:

    EDIT:

    Sorry...didn't see it was resolved. I wonder what the OP did to fix it
    Attached Images Attached Images  
    Please remember next time...elections matter!

  5. #5
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: How to loop all used cells certain column in a sheet vb.

    Quote Originally Posted by elmnas View Post
    Hi again

    Thank for for fast response I did as you told me to.


    Name:  2015-12-03 09_01_26-HAL_Statistics - Microsoft Visual Studio (Administrator).jpg
Views: 1086
Size:  22.3 KB

    Name:  2015-12-03 09_02_56-HAL_Statistics - Microsoft Visual Studio (Administrator).jpg
Views: 1122
Size:  66.8 KB

    I got this now...

    what is wrong?

    Thank you in advance.
    I've no idea, I did notice an error in my code: "Excel.Quit()" should be "xlApp.Quit()" Could you post the entire project that is causing these errors?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width