Home

Import txt VBA macro changing date format in random cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1036987%22%20slang%3D%22en-US%22%3EImport%20txt%20VBA%20macro%20changing%20date%20format%20in%20random%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1036987%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3CBR%20%2F%3E%3CBR%20%2F%3EEvery%20time%20that%20I%20run%20a%20vba%20script%20to%20import%20an%20specific%20txt%20file%2C%20it%20changes%20the%20date%20format%20of%20some%20cells.%3C%2FP%3E%3CP%3EIf%20I%20do%20the%20import%20manually%2C%20it%20doesn't%20change%20anything.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI'm%20from%20Brazil%20so%20we%20use%20dd%2Fmm%2Fyyyy%20format.%20The%20txt%20file%20is%20using%20the%20brazil's%20format.%20But%20somehow%20when%20I%20use%20the%20VBA%20code%2C%20some%20cells%20import%20randomly%20with%20the%20different%20format.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E(Left%20image)%20Using%20the%20VBA%20script%3C%2FP%3E%3CP%3E(Right%20image)%20Importing%20manually%20(File%26gt%3Bopen%26gt%3Bsearch%20for%20the%20.txt%26gt%3Betc)%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20277px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159229iF121C65FB3FBB978%2Fimage-dimensions%2F277x510%3Fv%3D1.0%22%20width%3D%22277%22%20height%3D%22510%22%20alt%3D%22excel1.JPG%22%20title%3D%22excel1.JPG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EUsing%20VBA%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20338px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159230i63A67B95A6E585D6%2Fimage-dimensions%2F338x502%3Fv%3D1.0%22%20width%3D%22338%22%20height%3D%22502%22%20alt%3D%22excel2.JPG%22%20title%3D%22excel2.JPG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EManually%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20already%20tried%20with%20another%20scripts%20but%20the%20problem%20stays.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1036987%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1037018%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20txt%20VBA%20macro%20changing%20date%20format%20in%20random%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1037018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F471077%22%20target%3D%22_blank%22%3E%40renantropico%3C%2FA%3E%26nbsp%3BOhhh%20date%20formats.%20I%20changed%20my%20input%20language%20to%20german%20and%20what%20a%20pain!%20In%20anycase%2C%20have%20you%20had%20any%20luck%20with%20the%20format%20function%3F%26nbsp%3B%3CSPAN%3Eformat(now()%2C%20%22yyyy-MM-dd%20hh%3Amm%3Ass%22)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1037021%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20txt%20VBA%20macro%20changing%20date%20format%20in%20random%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1037021%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20dirty%20way%20that%20I%20had%20to%20deal%20with%20this%20was%20by%20literally%20concatenating%20a%20bunch%20of%20strings%20forcing%20formatting%20to%20be%20the%20exact%20way%20I%20needed%20it%20to%20be.%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20strStartTime%2C%20strEndTime%20As%20String%3CBR%20%2F%3EstrStartTime%20%3D%20Month(rpStartTime)%20%26amp%3B%20%22%2F%22%20%26amp%3B%20Day(rpStartTime)%20%26amp%3B%20%22%2F%22%20%26amp%3B%20Year(rpStartTime)%3CBR%20%2F%3E%3CBR%20%2F%3Eyou%20could%20add%20in%20the%20minutes%2C%20hours%2C%20and%20seconds%20with%20colons%20need%20be.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1038188%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20txt%20VBA%20macro%20changing%20date%20format%20in%20random%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F470919%22%20target%3D%22_blank%22%3E%40alandorss%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHey!%3CBR%20%2F%3EThank%20you%20for%20the%20help%3C%2FP%3E%3CP%3EThis%20works%20but%20I%20have%20more%20than%202000%20lines%20to%20do%20it.%20It%20takes%20so%20long%20line%20by%20line.%3C%2FP%3E%3CP%3EI%20did%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3En%20%3D%20Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%3C%2FP%3E%3CP%3EFor%20i%20%3D%201%20To%20n%3CBR%20%2F%3ECells(i%2C%2016)%20%3D%20Format(Cells(i%2C%2015)%2C%20%22dd-mm-yyyy%20hh%3Amm%3Ass%22)%3CBR%20%2F%3ENext%20i%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20type%20to%20go%20faster%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1038303%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20txt%20VBA%20macro%20changing%20date%20format%20in%20random%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038303%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20found%20this%20helps%20quite%20a%20bit.%20disable%20calculation%20and%20also%20screen%20updating.%20Run%20the%20disable%20before%20the%20code%20and%20then%20enable%20it%20after.%20Use%20caution%20as%20if%20you%20hit%20an%20error%2C%20your%20sheet%20will%20be%20in%20calculation%20manual.%20run%20the%20enablescreenupdating%20by%20itself%20to%20change%20it%20back%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20subDisableScreenUpdating()%0A%20%20%20%20With%20Excel.Application%0A%20%20%20%20%20%20%20%20.ScreenUpdating%20%3D%20FALSE%0A%20%20%20%20%20%20%20%20.Calculation%20%3D%20Excel.xlCalculationManual%0A%20%20%20%20%20%20%20%20.EnableEvents%20%3D%20FALSE%0A%20%20%20%20End%20With%0AEnd%20Sub%0A%0ASub%20subEnableScreenUpdating()%0A%20%20%20%20With%20Excel.Application%0A%20%20%20%20%20%20%20%20.ScreenUpdating%20%3D%20TRUE%0A%20%20%20%20%20%20%20%20.Calculation%20%3D%20Excel.xlCalculationAutomatic%0A%20%20%20%20%20%20%20%20.EnableEvents%20%3D%20TRUE%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20look%20but%20maybe%20there%20is%20a%20way%20to%20format%20a%20range%20vs%20a%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
renantropico
New Contributor

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 we use dd/mm/yyyy format. The txt file is using the brazil's format. But somehow when I use the VBA code, some cells import randomly with the different format.

(Left image) Using the VBA script

(Right image) Importing manually (File>open>search for the .txt>etc)
excel1.JPGUsing VBAexcel2.JPGManually

 

I already tried with another scripts but the problem stays.

Thank you for the help!

5 Replies

@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")

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.

@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?

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.

 

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

Related Conversations
How to export Office 365 mailbox to PST?
alainajodi in Office 365 on
8 Replies
vba sending email w/ attachment
katrina bethea in Excel on
7 Replies
VBA - Splitting worksheet by Dept
Zia Siddique in Excel on
12 Replies
Userform Listbox HELP
R3d3mpt10n in Word on
1 Replies
'Calling' a function from within a Word document
PMHunt1955 in Word on
2 Replies