Hello everyone,
I have looked everywhere and tried everything including VLng, Val, and CDate but nothing seems to want to change column B (Which has the date information) to a date. When I manually check the data, I get the macro to put the txt file data into a sheet. I then use =isnumber() to check if the date is a number, which it is not. But if i click in the formula bar then click away it immediately converts it to the date format. I cant seem to replicate this in using vba unless i manually click on each cell.
I am trying to import a txt file to a sheet, text to column conversion the data and then have the date in column B formatted to a date format in excel. However, column B is just a String even after I attempt to do a text to column conversion.
Any help would be appreciated!
The txt file is here: https://filebin.net/nzqrndyi2wi3bruw/Alt280.txt?t=gbz2ebty
I have looked everywhere and tried everything including VLng, Val, and CDate but nothing seems to want to change column B (Which has the date information) to a date. When I manually check the data, I get the macro to put the txt file data into a sheet. I then use =isnumber() to check if the date is a number, which it is not. But if i click in the formula bar then click away it immediately converts it to the date format. I cant seem to replicate this in using vba unless i manually click on each cell.
I am trying to import a txt file to a sheet, text to column conversion the data and then have the date in column B formatted to a date format in excel. However, column B is just a String even after I attempt to do a text to column conversion.
Any help would be appreciated!
The txt file is here: https://filebin.net/nzqrndyi2wi3bruw/Alt280.txt?t=gbz2ebty
VBA Code:
Sub Macro4()
'
' Macro4 Macro
Dim myFile As String
Dim Text As String
Dim textLine As Variant
Dim i As Long
'Open the text file
myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt") ' Get the RAW Data file
'select the active sheet and clear its contents
Sheets("Paste Data Here").Select
ActiveSheet.Cells.ClearContents
ActiveSheet.Cells(1, 1).Select
'Loop to pull data from the txt document
Open myFile For Input As #1
Do Until EOF(1)
i = i + 1
Line Input #1, textLine
Range("A" & i).Value = textLine
Loop
Close #1
'Convert data based on a simicolon (;)
Columns(1).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 4), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
'Go back to database sheet
Sheets("Data").Select
End Sub