-
2 Attachment(s)
Get Hourly Data From Text File After Converting To Excel File
-
1 Attachment(s)
See attached. Need to create a lookup sheet called "Times" Macro is called GetHour
-
ok but how do you change my text file in to file 2012.xlsm?
thanks
-
Opened the txt file in Excel and saved as 2012.xlsm
-
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 ?
-
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.
Code:
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
-
1 Attachment(s)
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
-
I have no idea what you are asking me to do. Please explain step by step what you want in clear concise English.
