locked
automatically fill dates in excel RRS feed

  • Question

  • User32394187 posted

     hi, m searching for a way to automatically fill dates in the first column of an excel sheet, is there a way to do that?

    please note that i want dates to be formatted ("dd/mm/yyyy")

    Wednesday, October 20, 2010 6:26 AM

All replies

  • User1854529396 posted

    Hi

     Can you post your code


    Thanks

    G Dinakaran


    Wednesday, October 20, 2010 6:49 AM
  • User32394187 posted
        Protected Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim _dateS As String
            Dim _dateD As Date
            Dim i As Integer
    
            _dateS = TextBox1.Text
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Open("D:\LABORS.xlsx")
            xlWorkSheet = xlWorkBook.Worksheets(1)
    
            Dim slash1 As String
            Dim slash2 As String
            Dim day As String
            Dim month As String
            Dim year As String
    
            
            For i = 4 To 300
    
                xlWorkSheet.Cells(i, 1) = _dateS
                _dateS = xlWorkSheet.Cells(i, 1).value
    
                ' _dateD = ((Microsoft.VisualBasic.Mid(_dateS, 4, 3) & Microsoft.VisualBasic.Mid(_dateS, 1, 2) & Microsoft.VisualBasic.Mid(_dateS, 6, 5)))
                '  _dateS = _dateD.AddDays(1)
    
    
    
    
                slash1 = Microsoft.VisualBasic.Mid(_dateS, 2, 1)
    
    
                year = Microsoft.VisualBasic.Right(_dateS, 4)
    
    
    
                If slash1 = "/" Then
                    'without slash
                    month = "0" & Microsoft.VisualBasic.Mid(_dateS, 1, 1)
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 5, 1)
    
                    If slash2 = "/" Then
                        day = Microsoft.VisualBasic.Mid(_dateS, 3, 2)
                    End If
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 4, 1)
    
                    If slash2 = "/" Then
                        day = "0" & Microsoft.VisualBasic.Mid(_dateS, 3, 1)
                    End If
    
                End If
    
    
    
                slash1 = Microsoft.VisualBasic.Mid(_dateS, 3, 1)
    
                If slash1 = "/" Then
                    month = Microsoft.VisualBasic.Mid(_dateS, 1, 2)
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 5, 1)
    
                    If slash2 = "/" Then
                        day = "0" & Microsoft.VisualBasic.Mid(_dateS, 4, 1)
                    End If
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 6, 1)
    
                    If slash2 = "/" Then
                        day = Microsoft.VisualBasic.Mid(_dateS, 4, 2)
                    End If
    
                End If
                _dateD = day & "/" & month & "/" & year
                _dateS = _dateD.AddDays(1)
    
                slash1 = Microsoft.VisualBasic.Mid(_dateS, 3, 1)
    
                If slash1 = "/" Then
                    'without slash
                    month = "0" & Microsoft.VisualBasic.Mid(_dateS, 1, 1)
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 5, 1)
    
                    If slash2 = "/" Then
                        day = "0" & Microsoft.VisualBasic.Mid(_dateS, 4, 1)
                    End If
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 4, 1)
    
                    If slash2 = "/" Then
                        day = "0" & Microsoft.VisualBasic.Mid(_dateS, 3, 1)
                    End If
    
                End If
    
    
    
                slash1 = Microsoft.VisualBasic.Mid(_dateS, 2, 1)
    
                If slash1 = "/" Then
                    month = "0" & Microsoft.VisualBasic.Mid(_dateS, 1, 1)
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 5, 1)
    
                    If slash2 = "/" Then
                        day = Microsoft.VisualBasic.Mid(_dateS, 3, 2)
                    End If
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 6, 1)
    
                    If slash2 = "/" Then
                        day = Microsoft.VisualBasic.Mid(_dateS, 4, 2)
                    End If
    
                    slash2 = Microsoft.VisualBasic.Mid(_dateS, 4, 1)
                    If slash2 = "/" Then
                        day = "0" & Microsoft.VisualBasic.Mid(_dateS, 3, 1)
                    End If
    
                End If
    
                year = Microsoft.VisualBasic.Right(_dateS, 4)
                _dateS = day & "/" & month & "/" & year
    
    
            Next
    
    
            xlWorkBook.Save()
            xlWorkBook.Close()
            xlApp.Quit()
    
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
        End Sub


     

    Friday, October 22, 2010 4:02 AM