-
Jul 23rd, 2018, 02:39 PM
#1
Thread Starter
Fanatic Member
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
-
Jul 27th, 2018, 07:11 AM
#2
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.
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...
-
Jul 27th, 2018, 08:48 AM
#3
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
-
Jul 28th, 2018, 03:08 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|