Load .txt file into ListView

[dboone25]

Good morning all. Have a query regarding the ListView and loading a.txt file straight into the ListView.

I have a userform with one button which opens the load file dialoge box but i am unsure how to show the contents of a text file into the ListView.

My code below is to select a file.

' Declare variables.Dim myFile As String
Dim FileName As Variant
Dim r As Range
Dim i As Long
Dim rc As Range
Dim rr As Long




' Select CSV file.
' Open dialog box title.
' Add title to open file dialogue box " Select .csv file to import


myFile = "TXT Files (*.txt),*.txt"
'Title = "Select .TXT File to Import"
FileName = Application.GetOpenFilename(Title:="Select .txt File to Import")
                                                                          
' Select and open the dialog box.
' If no file selected open dialog box.
If FileName = False Then
MsgBox "No File Selected"
Exit Sub
End If

Is it possible to then connect to a SQL Server which uses a text file from the ListView and output to sheet1.

I have written the connection and run a short sql query.

Private Sub Connectsqlserver()





    Dim Conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=Database1;" & _
                  "Initial Catalog=CommandTables;"
                  
    
    ' Create the Connection and Recordset objects.
    Set Conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    Conn.Open sConnString
    Set rs = Conn.Execute("SELECT * FROM TableInfo;")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If


    ' Clean up
    If CBool(Conn.State And adStateOpen) Then Conn.Close
    Set Conn = Nothing
    Set rs = Nothing
End Sub

Can this be done?

Reason why I would like to run an text file into ListView and then run the SQL script from ListView into Sheet1 as this will make it more user friendly to edit anyinformation found in ListView.

 

[Norie]

FileName is the variable that will hold the path and name for the file the user selects here.

FileName = Application.GetOpenFilename(Title:="Select .txt File to Import")

This code is totally unneeded.

  If Dir(sPath) = "" Then
        MsgBox "File was not found."
        Exit Sub
    End If
    Close

This code,

Open sPath For Input As #1
    Do Until EOF(1)
        Line Input #1, sTxt
        sText = sText & sTxt & vbLf
    Loop

    Close

should be changed to this.

Open FileName For Input As #1
    Do Until EOF(1)
        Line Input #1, sTxt
        sText = sText & sTxt & vbLf
    Loop

    Close #1

 

[dboone25]

Norie, fantastic...many many many thanks for your help with this...really appreciate this!!