Forum Discussion

renantropico's avatar
renantropico
Copper Contributor
Nov 27, 2019

Import txt VBA macro changing date format in random cells

Hello! Every time that I run a vba script to import an specific txt file, it changes the date format of some cells. If I do the import manually, it doesn't change anything. I'm from Brazil so ...
BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
alandorss's avatar
alandorss
Brass Contributor
Nov 27, 2019

renantropico Ohhh date formats. I changed my input language to german and what a pain! In anycase, have you had any luck with the format function? format(now(), "yyyy-MM-dd hh:mm:ss")

  • renantropico's avatar
    renantropico
    Copper Contributor
    Nov 28, 2019

    alandorss 

    Hey!
    Thank you for the help

    This works but I have more than 2000 lines to do it. It takes so long line by line.

    I did this:

    n = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To n
    Cells(i, 16) = Format(Cells(i, 15), "dd-mm-yyyy hh:mm:ss")
    Next i

     

    Any type to go faster?

    • alandorss's avatar
      alandorss
      Brass Contributor
      Nov 28, 2019

      Hi, I found this helps quite a bit. disable calculation and also screen updating. Run the disable before the code and then enable it after. Use caution as if you hit an error, your sheet will be in calculation manual. run the enablescreenupdating by itself to change it back

       

       

      Sub subDisableScreenUpdating()
    With Excel.Application
        .ScreenUpdating = FALSE
        .Calculation = Excel.xlCalculationManual
        .EnableEvents = FALSE
    End With
End Sub

Sub subEnableScreenUpdating()
    With Excel.Application
        .ScreenUpdating = TRUE
        .Calculation = Excel.xlCalculationAutomatic
        .EnableEvents = TRUE
    End With
End Sub

       

      Have a look but maybe there is a way to format a range vs a cell.

       

      • renantropico's avatar
        renantropico
        Copper Contributor
        Dec 02, 2019

        alandorss Thank you very much, this helped a lot with the long execution time.

  • alandorss's avatar
    alandorss
    Brass Contributor
    Nov 27, 2019

    The dirty way that I had to deal with this was by literally concatenating a bunch of strings forcing formatting to be the exact way I needed it to be.

    Dim strStartTime, strEndTime As String
    strStartTime = Month(rpStartTime) & "/" & Day(rpStartTime) & "/" & Year(rpStartTime)

    you could add in the minutes, hours, and seconds with colons need be.

Resources