Get Hourly Data From Text File After Converting To Excel File

[CORAL]

i have a text file that i opend it with excel & when i work with it i recognized that all parts of this file is not in the same format and i faced with difficulty but what i want is to get one hour data from it with vb code as an example attached(final)


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA (https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA)
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg (https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg)
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839tUQl_92m vg (https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839tUQl_92m vg)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg.9isY3Ezhx4j9itQLuif2 6T (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg.9isY3Ezhx4j9itQLuif2 6T)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg.9irSL7x4Moh9itTRqL7d Qh (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg.9irSL7x4Moh9itTRqL7d Qh)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg.9iraombnLDb9itV80HDp Xc (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg.9iraombnLDb9itV80HDp Xc)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg.9is0FSoF2Wi9itWKEvGS Sq (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg.9is0FSoF2Wi9itWKEvGS Sq)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[alansidman]

See attached. Need to create a lookup sheet called "Times" Macro is called GetHour

[CORAL]

ok but how do you change my text file in to file 2012.xlsm?
thanks

[alansidman]

Opened the txt file in Excel and saved as 2012.xlsm

[CORAL]

i mean that how can i change the data format of text file in to your file 2012.xlsm data format? did you change it with code ?

[alansidman]

Line 50 in the code changes the format of column B from a date format to a number format so it can be compared to the table in Sheet "Times." Line 70 rounds the number value to three decimal places. You do understand that Excel stores Dates and Times as numbers and formats them only to look like familar dates and times. Dates are the integer portion of the number and times are the decimal portion of the number.

Here is a link to explain the whole concept: http://www.cpearson.com/excel/datetime.htm

I hope this helps you to understand the code better.



Option Explicit

Sub GetHour()
Dim w1 As Worksheet
10 Set w1 = Sheets("2012")
Dim w2 As Worksheet
20 Set w2 = Sheets("Times")
Dim lr As Long
30 lr = w1.Range("B" & Rows.Count).End(xlUp).Row
Dim i As Integer

40 Application.ScreenUpdating = False
50 w1.Range("B1:B" & lr).NumberFormat = "0.000"

60 For i = 1 To lr
70 w1.Range("B" & i).Value = Round(w1.Range("B" & i), 3)
80 Next i

90 For i = lr To 1 Step -1
Dim Res As Variant
100 On Error Resume Next
110 Err.Clear
120 Res = Application.WorksheetFunction.VLookup(w1.Range("B" & i), w2.Range("A2:A23"), 1, False)
130 If Err.Number <> 0 Then
140 w1.Range("B" & i).EntireRow.Delete
150 End If
160 Next i
170 Application.ScreenUpdating = True
End Sub

[CORAL]

ok i should apologize for confuzing you and now i explain it more i have attached an excel file that the first step is opening 2012.2.txt then its format (column A ) changed in to sheet2 data format (A & B) afther that the sheet3 is result (with code) {NOTE :i dont need to 0.000 data format} all the best thanks

[alansidman]

I have no idea what you are asking me to do. Please explain step by step what you want in clear concise English.