Results 1 to 4 of 4

Thread: Span across 2nd row to get column date value

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Span across 2nd row to get column date value

    Hello:

    I am using Excel 2016 with VB.NET 2017.

    I am looking to span across row 2 and look for a date that matches the current date.

    In the code below, cdat is the current date (converted to a string)

    Not that I have to convert it to a string, but I think findRange is looking for that.
    Code:
                Dim findRange As Microsoft.Office.Interop.Excel.Range = sourceRange.Find(stringToSearch, miss, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, XlSearchOrder.xlByColumns, XlSearchDirection.xlNext, miss, miss, miss)
    In both cases, the date field is date, like this:
    Code:
    Dim searchdate As Date = DateTime.Now.ToShortDateString.ToString
    The problem may very well be that it is having trouble finding a date value for a string, though I have tried various conversions.

    The question is, what is the simplest approach?
    Secondly, is the code below correct to obtain a row of data, as perhaps this is the problem?
    Code:
                'get range to find string (row 2)
                Dim sourceRange As Microsoft.Office.Interop.Excel.Range = DirectCast(xlRange.Range("2:2"), Microsoft.Office.Interop.Excel.Range)
    

    Complete Code:
    Code:
        Public Sub FindAndCopy(ByVal cdat As String)
            Dim miss As Object = Type.Missing
            Dim oldCult As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
    
            Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
            xlApp.Visible = True
            xlApp.UserControl = True
            Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = Nothing
            Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
            Dim foundColumns As New List(Of Integer)
            Try
                'change full path of Excel file here:
                xlBook = DirectCast(xlApp.Workbooks.Open("\\w2012\users\ENGINEERING\Schedules\Drafting design Schedule\2018\Gantt Drafting Schedule 2018.xlsx", True, False, miss, "", miss,
                 False, miss, miss, True, miss, miss, miss, miss, miss), Microsoft.Office.Interop.Excel.Workbook)
    
                'get first sheet in the workbook
                xlSheet = DirectCast(xlBook.Worksheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
    
                'get used range of certain sheet
                Dim xlRange As Microsoft.Office.Interop.Excel.Range = DirectCast(xlSheet.UsedRange, Microsoft.Office.Interop.Excel.Range)
    
                'Dim rFound As Range
                'Dim rSearch As Range
                Dim sFirstAddress As String
    
                'set string to search:
                Dim stringToSearch As String = cdat
    
                'get range to find string (row 2)
                Dim sourceRange As Microsoft.Office.Interop.Excel.Range = DirectCast(xlRange.Range("2:2"), Microsoft.Office.Interop.Excel.Range)
    
                Dim findRange As Microsoft.Office.Interop.Excel.Range = sourceRange.Find(stringToSearch, miss, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, XlSearchOrder.xlByColumns, XlSearchDirection.xlNext, miss, miss, miss)
    
                'if stringToSearch wasn't found exit sub:
                If findRange Is Nothing Then
                    Console.WriteLine("String ""{0}"" was not found: ", stringToSearch)
                    Return
    
                Else
                    'store the address in a variable
                    sFirstAddress = findRange.Address
                    foundColumns.Add(findRange.Column)
                    Console.WriteLine("String ""{0}"" was found in cell: ""{1}""", stringToSearch, sFirstAddress)
    
                End If
    
            Catch ex As Exception
                Console.WriteLine(ex.Message & vbLf & ex.StackTrace)
    
            Finally
                ' xlBook.Save()
                ' xlBook.Close(True, miss)
                ' xlApp.Quit()
                ' releaseObject(xlBook)
                ' releaseObject(xlApp)
                ' System.Threading.Thread.CurrentThread.CurrentCulture = oldCult
    
            End Try

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Span across 2nd row to get column date value

    Code:
    Dim sourceRange As Microsoft.Office.Interop.Excel.Range = DirectCast(xlRange.Range("2:2"), Microsoft.Office.Interop.Excel.Range)
    
                Dim findRange As Microsoft.Office.Interop.Excel.Range = sourceRange.Find(stringToSearch, miss, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, XlSearchOrder.xlByColumns, XlSearchDirection.xlNext, miss, miss, miss)
    Not done vb.net much, so really a beginner. But the direct case source is only one cell?
    Or are you just testing one date to see if it works?
    Secondly is miss the same as null ?
    Third have you tried directly in VBA first to get that working then convert the vba code to .net?
    You can record a macro in excel of the action to get an idea. Perhaps that will point you in the direction you need.
    --
    Edit: extra thought
    You are searching for a date and passing a string - correct? Excel uses numbers for dates (change the formatting on the cell) Perhaps you need to search for the number of the date instead...?
    Last edited by Ecniv; Jul 27th, 2018 at 07:20 AM.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Span across 2nd row to get column date value

    As Ecniv suggests, you probably want to use the "number" for the date comparison. Here is an example that works in Excel (VBA):

    Code:
    Sub findDate()
        Dim ws As Worksheet
        Dim dtToday As Long
        Dim dtSheet As Long
        Dim lastCol As Integer
        Dim J As Integer
        
        Set ws = ActiveSheet
        With ws
            dtToday = Date
            lastCol = .Range("a2").End(xlToRight).Column
            For J = 1 To lastCol
                dtSheet = .Cells(2, J)
                If dtSheet = dtToday Then
                    MsgBox "Date found in column " & J
                    Exit For
                End If
            Next J
        End With
    End Sub

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Span across 2nd row to get column date value

    Hi ,

    here a sample for .Net..

    add a Listbox, Textbox and a Button to the form
    Code:
    Option Strict On
    Imports Excel
    
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim xlApp As Excel.Application
            Dim xlWorkbookRPO As Excel.Workbook
            Dim xlWorksheetRPO As Excel.Worksheet
            xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
            xlWorkbookRPO = xlApp.Workbooks.Open("C:\E99.xls")
            xlWorksheetRPO = CType(xlWorkbookRPO.Worksheets("Tabelle1"), Excel.Worksheet)
    
            'search in first column and return the cell(s) in Listbox that were found:
            'ListBox1.Items.Add(mSearch(CType(xlWorksheetRPO.Columns(1), Excel.Range), "").ToString & " found")
    
            'search in second row and return the cell(s) in Listbox that were found:
            ListBox1.Items.Add(mSearch(CType(xlWorksheetRPO.Rows(2), Excel.Range), "").ToString & " found")
            xlApp.Quit()
    
        End Sub
    
        Function mSearch(ByVal r As Excel.Range, ByVal value As String) As Integer
            Dim c As Excel.Range
            Dim firstAddress As String = String.Empty
            mSearch = 0
            c = r.Find(TextBox1.Text, LookIn:=Excel.XlFindLookIn.xlValues)
            Do
                If Not c Is Nothing Then
                    mSearch = mSearch + 1
                    If String.IsNullOrEmpty(firstAddress) Then firstAddress = c.Address
                    c = r.FindNext(c)
                    ListBox1.Items.Add(c.Address)
                Else
                    Exit Do
                End If
            Loop While c.Address <> firstAddress
            ListBox1.Items.Add("----------------")
        End Function
    End Class
    the found Items are added to the Listbox

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

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