ilogic to vb.net help

[Anonymous]

looking for a bit of help folks in translating a piece of illogic to vb.net.

I'm sure this is relatively easy for most, but I'm going round in circles.. The code is to look up an excel column (A) and find a string (textbox1.text) and then once found, write data to that row.

I have something that is working - sort of - but seems to take longer than illogic.

can anyone help in translating the code below?

any thanks in advance guys for any help with this

'Define the first row with data
RowStart = 7

'Define the amount of skipped rows
RowEnd = 1000

'Find the first empty cell
For countA = RowStart To (RowEnd * 0.01) + RowStart
'If it's not blank jump to cell + 1000. E.g. search cell A2 and then A102 etc.
If Not String.IsNullOrEmpty(GoExcel.CellValue("A" & RowEnd)) Then
'Add another 100 to searched cells
RowEnd = RowEnd + 1000
Else
'Else if it's empty exit loop
Exit For
End If
Next
End If
'Search for the file name in column A
For oRow = RowStart To RowEnd
If (GoExcel.CellValue("A" & oRow)) = oDocNumber Then
GoExcel.CellValue("C" & oRow) = DateString

[frederic.vandenplas]

Hi,

I did not tested it, but it will point you in the right direction.

Do not forget to add a reference to the Microsoft Excel XX.0 Object Library (COM)

For Excel 2007 it's located here:

C:\windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Try
            Dim oExcelApp As Excel.Application = GetObject(, "Excel.Application")
            
            Dim oWorkbook As Excel.Workbook = oExcelApp.ActiveWorkbook
            Dim oSheet As Excel.Worksheet = oWorkbook.ActiveSheet
            'Define the first row with data
            Dim RowStart As Integer = 7
            'Define the amount of skipped rows
            Dim RowEnd As Integer = 1000

            Dim oDocNumber As Integer
            'Find the first empty cell
            For countA = RowStart To (RowEnd * 0.01) + RowStart
                'If it's not blank jump to cell + 1000. E.g. search cell A2 and then A102 etc.
                If Not String.IsNullOrEmpty(oSheet.Cells(1, RowEnd).text) Then
                    'Add another 100 to searched cells
                    RowEnd = RowEnd + 1000
                Else
                    'Else if it's empty exit loop
                    Exit For
                End If
            Next
            'Search for the file name in column A
            For oRow = RowStart To RowEnd
                If oSheet.Cells(1, oRow) = oDocNumber Then
                    oSheet.Cells(3, oRow) = DateString
                End If
            Next oRow

        Catch ex As Exception

        End Try
    End Sub
End Class

[Anonymous]

Hi Frederic

many thanks for your reply. I couldn't get it to work unfortunately, but have an adaptation of the code that I am working on. While it finds the relevant row and updates the appropriate fields, the program takes 30 seconds to run before the user can continue...

Ilogic does this in a fraction of the time... I'm missing something, somewhere obviously.

Could you have a look and see if there is anything obvious?

many thanks!!

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim RowStart As Integer = 1
Dim RowEnd As Integer = 500
Dim oRow As Integer
xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False

xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
xlWorkSheet = xlWorkBook.Worksheets("Sheet1")

Try

Dim oSheet As Excel.Worksheet = xlWorkBook.ActiveSheet
'Find the first empty cell
For countA = RowStart To (RowEnd * 0.01) + RowStart
'If it's not blank jump to cell + 1000. E.g. search cell A2 and then A102 etc.
If Not String.IsNullOrEmpty(oSheet.Cells(1, RowEnd).text) Then
'Add another 100 to searched cells
RowEnd = RowEnd + 500
Else
'Else if it's empty exit loop
Exit For
End If
Next

'Search for the file name in column A
For oRow = RowStart To RowEnd
'check if the cell value matches the search string.
If xlWorkSheet.Cells(oRow, 1).value = (TextBox2.Text) Then
TextBox12.Text = xlWorkSheet.Cells(oRow, 2).Value
TextBox7.Text = xlWorkSheet.Cells(oRow, 3).Value
End If

Next 'oRow

Catch ex As Exception

End Try

[frederic.vandenplas]

Hi,

Can you provide the excel?

[Anonymous]

File attached, it is only a temp file for testing with a handful of numbers.

thanks for any light you can shine on it 🙂

[MechMachineMan · Accepted solution]

Try this!

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
'Modified by MechMachineMan to include built in excel functions as the app actually gets connected to.

   Dim xlApp As Excel.Application
   Dim xlWorkBook As Excel.Workbook
   Dim xlWorkSheet As Excel.Worksheet
   Dim RowStart As Integer = 1
   Dim oRow As Integer
   Dim oAPopulatedCOLUMN As Integer
   
   oAPopulatedCOLUMN = 1 'Set this value to correspond to a column that is full with data. This will be used to find the last row instantly.
   xlApp = New Excel.Application
   xlApp.Visible = False
   xlApp.DisplayAlerts = False
Try
   xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)

   ' The workbook will only work for sheet 1. alternative line for active sheet is below:
   ' xlWorksheet = xlWorkBook.ActiveSheet
   xlWorkSheet = xlWorkBook.Worksheets("Sheet1")

   oRowEnd = xlWorksheet.Cells(xlWorksheet.Rows.Count, oAPopulatedCOLUMN).End(XlDirection.xlUp).Row
   For oRow = RowStart To RowEnd
      'check if the cell value matches the search string.
      If xlWorkSheet.Cells(oRow, 1).value = (TextBox2.Text) Then
         TextBox12.Text = xlWorkSheet.Cells(oRow, 2).Value
         TextBox7.Text = xlWorkSheet.Cells(oRow, 3).Value
      End If
   Next 'oRow
Catch
   MsgBox("Error in excel features!")
End try

   xlApp.Visible = True
   xlApp.DisplayAlerts = True
End Sub

[Anonymous]

Cheers Justin,

one minor point is that I need to declare XLDirection

what should I declare it as?

[MechMachineMan]

https://msdn.microsoft.com/en-us/library/office/ff820880.aspx

I'm assuming you must have your references added to the Miscroft.Excel in the project already?

You might be able to get away with using just xlUp and not needing the enumerator there.

Depending on the environment, sometimes it gets fussy without it, sometimes it gets fussy with it.

[Anonymous]

hi

yes I have Imports Excel = Microsoft.Office.Interop.Excel assigned

off home now, but will have a look later tonight

thanks !

[MechMachineMan]

If nothing else, it should work without error if you use the constant value
rather than the name ( -4162 vs xlDirection.xlUp)

[Anonymous]

thanks for your help justin and Frederic

I got this to work last night but instead of trying to define a set of rows to search through and then adding a few more if the value isn't there etc, I decided to look for the last row used in the entire sheet, and then used that as the search range....

all works well and nice and quick too

cheers

[adam.nagy]

Hi,

Are you all set now? 🙂

If so could you mark the answers that helped you the most as a "Solution"?

You can mark multiple answers as well if needed.

Thank you,