Click here to Skip to main content
15,941,071 members
Please Sign up or sign in to vote.
2.33/5 (3 votes)
See more:
Hello could someone have a look at this.
can this code be improved like put it all on 1 line or something, i think if i keep going like this my code could look a bit messy.
VB
ThisRow.C1 = CStr(MyExcel.ActiveCell.Value)
MyExcel.ActiveCell.Offset(0, 1).Activate()

ThisRow.C2 = CStr(MyExcel.ActiveCell.Value)
MyExcel.ActiveCell.Offset(0, 1).Activate()

ThisRow.C3 = CStr(MyExcel.ActiveCell.Value)
MyExcel.ActiveCell.Offset(0, 1).Activate()

ThisRow.C4 = CStr(MyExcel.ActiveCell.Value)
MyExcel.ActiveCell.Offset(0, 1).Activate()

ThisRow.C5 = CStr(MyExcel.ActiveCell.Value)
MyExcel.ActiveCell.Offset(0, 1).Activate()

ThisRow.C6 = CStr(MyExcel.ActiveCell.Value)

ExcelRowList.Add(ThisRow)
MyExcel.ActiveCell.Offset(1, -5).Activate()

I was thinking using a loop but i have never used a loop.
maby some sort of an Array with a loop i dont know.
thanks for your help.

*Update*
some more information.
Private Structure ExcelRows
       Dim C1 As String
       Dim C2 As String
       Dim C3 As String
       Dim C4 As String
       Dim C5 As String
       Dim C6 As String
   End Structure
   Private ExcelRowList As List(Of ExcelRows) = New List(Of ExcelRows)

   Private Sub Button15_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button15.Click
       Me.OpenFileDialog1.FileName = Nothing

       If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
           Me.TextBox3.Text = Me.OpenFileDialog1.FileName
       End If

       If GetInfo() = True Then

           For Each Xitem In ExcelRowList

               Dim lvitem As ListViewItem
               lvitem = Me.ListView1.Items.Add(Xitem.C1)
               lvitem.SubItems.AddRange(New String() {Xitem.C2, Xitem.C3, Xitem.C4, Xitem.C5, Xitem.C6})
           Next
       End If
   End Sub
   Private Function GetInfo() As Boolean
       Dim Completed As Boolean = False
       Dim MyExcel As New Excel.Application
       MyExcel.Workbooks.Open(Me.TextBox3.Text)
       MyExcel.Sheets("Sheet1").Activate()
       MyExcel.Range("A1").Activate()

       Dim ThisRow As New ExcelRows

       Do

           If MyExcel.ActiveCell.Value > Nothing Or MyExcel.ActiveCell.Text > Nothing Then

               ThisRow.C1 = CStr(MyExcel.ActiveCell.Value)
               MyExcel.ActiveCell.Offset(0, 1).Activate() ' move 1 column to the right

               ThisRow.C2 = CStr(MyExcel.ActiveCell.Value)
               MyExcel.ActiveCell.Offset(0, 1).Activate()

               ThisRow.C3 = CStr(MyExcel.ActiveCell.Value)
               MyExcel.ActiveCell.Offset(0, 1).Activate()

               ThisRow.C4 = CStr(MyExcel.ActiveCell.Value)
               MyExcel.ActiveCell.Offset(0, 1).Activate()

               ThisRow.C5 = CStr(MyExcel.ActiveCell.Value)
               MyExcel.ActiveCell.Offset(0, 1).Activate()

               ThisRow.C6 = CStr(MyExcel.ActiveCell.Value)

               ExcelRowList.Add(ThisRow)

               MyExcel.ActiveCell.Offset(1, -5).Activate() ' Move 1 Row down and 5 Columns to the left
           Else
               Completed = True
               Exit Do
           End If
       Loop

       MyExcel.Workbooks.Close()
       MyExcel = Nothing

       Return Completed
   End Function
Posted
Updated 13-Dec-11 18:46pm
v2

1 solution

I have no idea what your goal is, but from the looks of the codee, you can simply replace all this Activating garbage with a Copy and Paste operation. I don't have examples because I rarely ever do Office Interop.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900