Forum Discussion

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

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

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
Copper Contributor
Dec 02, 2019
alandorss Thank you very much, this helped a lot with the long execution time.