[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
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.
Re: How to loop all used cells certain column in a sheet vb.
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.
Re: How to loop all used cells certain column in a sheet vb.
Originally Posted by elmnas
Hi again
Thank for for fast response I did as you told me to.
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
Re: How to loop all used cells certain column in a sheet vb.
Originally Posted by elmnas
Hi again
Thank for for fast response I did as you told me to.
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?