[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

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

---

Quote:

---

Originally Posted by Peter Swinkels

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.


Attachment 132823

Attachment 132827

I got this now...

what is wrong?

Thank you in advance.

Quote:

---

Originally Posted by elmnas

Hi again

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


Attachment 132823

Attachment 132827

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 :confused:

Quote:

---

Originally Posted by elmnas

Hi again

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


Attachment 132823

Attachment 132827

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?