The below sub i created to populate a listbox using ADO to read a text file as Access and SQL were unavailable.
I needed 2 columns from the txt file and one would differ per user so i used the persons computer username as the column name i would need (so it would work for anybody in the table).
The first column is a date column and the second is the users column where they enter a reason for leave on that date.
The Select statement asks for the date column and users column where there were any entries. Otherwise it'd give you all 365 rows (dates) in the Leave text file.
Thought i'd put it on here as i couldnt find this anywhere but managed to stumble my wat through it using various websites and For Dummies books.
Hope someone finds this useful. Cheers - Mike
Sub txtPopulateLeaveListADO()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long, i As Integer
Dim oConn As Object, oRS As Object, oFSObj As Object
Dim strMyStuff As String
strMyStuff = Environ$("Username")
' 'Get a text file name using file open and link it. Otherwise use 2 public constants 1) FilePath + 2) FileName for strFullPath
' strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
strFullPath = 1)FilePath & 2)FileName
' If strFullPath = "False" Then Exit Sub 'User pressed Cancel on the open file dialog
'This gives us a full path name e.g. C:\temp\folder\file.txt
'We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.path
strFilename = oFSObj.GetFile(strFullPath).Name
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set oRS = CreateObject("ADODB.RECORDSET")
'Now actually open the text file and import into Excel
oRS.Open "SELECT Date, [" & strMyStuff & "] FROM [" & strFilename & "] WHERE [" & strMyStuff & "] IS NOT NULL", oConn, 3, 1, 1
While Not oRS.EOF
With Userform1.ListBox1
.clear
.ColumnHeads = False
.ColumnCount = 2
.ColumnWidths = "160;170"
.Column = oRS.GetRows
For i = 0 To .ListCount - 1
.List(i, 0) = (Format(.List(i, 0), "DD/MM/YYYY"))
Next i
End With
Wend
oRS.Close
oConn.Close
End Sub
I needed 2 columns from the txt file and one would differ per user so i used the persons computer username as the column name i would need (so it would work for anybody in the table).
The first column is a date column and the second is the users column where they enter a reason for leave on that date.
The Select statement asks for the date column and users column where there were any entries. Otherwise it'd give you all 365 rows (dates) in the Leave text file.
Thought i'd put it on here as i couldnt find this anywhere but managed to stumble my wat through it using various websites and For Dummies books.
Hope someone finds this useful. Cheers - Mike
Sub txtPopulateLeaveListADO()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long, i As Integer
Dim oConn As Object, oRS As Object, oFSObj As Object
Dim strMyStuff As String
strMyStuff = Environ$("Username")
' 'Get a text file name using file open and link it. Otherwise use 2 public constants 1) FilePath + 2) FileName for strFullPath
' strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
strFullPath = 1)FilePath & 2)FileName
' If strFullPath = "False" Then Exit Sub 'User pressed Cancel on the open file dialog
'This gives us a full path name e.g. C:\temp\folder\file.txt
'We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.path
strFilename = oFSObj.GetFile(strFullPath).Name
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set oRS = CreateObject("ADODB.RECORDSET")
'Now actually open the text file and import into Excel
oRS.Open "SELECT Date, [" & strMyStuff & "] FROM [" & strFilename & "] WHERE [" & strMyStuff & "] IS NOT NULL", oConn, 3, 1, 1
While Not oRS.EOF
With Userform1.ListBox1
.clear
.ColumnHeads = False
.ColumnCount = 2
.ColumnWidths = "160;170"
.Column = oRS.GetRows
For i = 0 To .ListCount - 1
.List(i, 0) = (Format(.List(i, 0), "DD/MM/YYYY"))
Next i
End With
Wend
oRS.Close
oConn.Close
End Sub
