I've been trying to open text files into excel using vba. I want to access any text, csv, xls file and import it fast by clicking one button (located on the top of "Dust Raw Data" and "VOC Raw Data" worksheets - see attached. I generated a vba code, but the issue is that when I run the code (press the import button - on the Dust Raw and VOC Raw sheets), the values containing numbers do not register and they are assumed as text instead of numbers. Additionally, I want to extract the time only from the VOC Raw Data sheet (using the PID txt file- see attached) to the "VOC table" sheet which must be in the format of hh:mm AM/PM, but instead; excel gives me as m/d/yyyy h:mm. Excel cannot understand what I`m trying to achieve so it copies the whole thing as Ex: 05/01/23 06:05:55 instead of 6:05 AM. Please let me know if I need to add more to my code and if so, what lines. TIA!!
(Note: I`m aware of power query, but I want to use vba in this particular case)
The VBA Code:
Sub ImportText3()
Dim ThisLine As Variant
Dim fileFilterPattern As String
Dim n As Long
' Just look at text files
'fileFilterPattern = "Text Files *.txt,*.txt"
fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv"
ThisLine = Application.GetOpenFilename(fileFilterPattern)
If ThisLine = False Then
MsgBox "No file selected."
Exit Sub
End If
Application.ScreenUpdating = False
Open ThisLine For Input As #1
' Look at each line of the file
Do Until EOF(1)
Line Input #1, ThisLine
' If the line contains something
If Len(ThisLine) > 0 Then
' Replace any commas with tabs (ASCII character 9)
ThisLine = Replace(ThisLine, ",", Chr(9))
' Divide string into an array by tab
ThisLine = Split(ThisLine, Chr(9))
' Copy array to a resized range
ActiveCell.Offset(n, 0).Resize(1, UBound(ThisLine) + 1).Value = ThisLine
End If
' Point to next cell below
n = n + 1
Loop
' Done so close the file for input
Close #1
Application.ScreenUpdating = True
End Sub
Bookmarks