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]

What exactly do you want to show in the ListView?

 

[dboone25]

The contents of a .txt file

 

[dboone25]

To open a .txt file and show the contents of the .txt file in ListView

 

[dboone25]

I have tried something like this:

Dim lCounts As LongDim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet


Dim LItem As ListItem
Dim lcount As Long


Set objExcel = New Excel.Application
objExcel.Application.Workbooks.Open ("TXT Files (*.txt),*.txt")
Set objWorksheet = objExcel.Application.Worksheets(1)


 With objExcel
    For lcount = 1 To objWorksheet.UsedRange.Rows.Count
        Set LItem = ListView1.ListItems.Add(, , .Cells(lcount, 1))
        LItem.SubItems(1) = .Cells(lcount, 2)
        LItem.SubItems(1) = .Cells(lcount, 3)
    Next
End With

It comes up with a run-time error '1004':

("TXT Files (*.txt),*.txt") could not be found.

 

[Norie]

Why would you use a listview to show a text file?

Does the file contain structured data?

 

[dboone25]

Trying to create a UserForm that a user can load a text file that contains a script which can be used to run and pull back data from an SQL database. This text file can be viewed in ListView and allow the user to edit the information as needed.

The .txt file will contain this information to be used to select information from SQL:

SELECT Table._no,       TableType.table_type_ref,
       Create._datetime,
       Create.op_seq,
       Create.card_no,
       System.sys_ref,
       SystemType.acq_ref,
       
FROM   TableType, Create, System, SystemType


WHERE  TableType.table_type_ref        =  Create.op_seq
AND     Create.op_seq           =  System.sys_ref



[CODE]




Once the text file is readable in the ListView can this be made to edit the information inside the ListView?

At the moment I cant seem to load the .txt file straight into the ListView.

 

[Norie]

Why don't you just use a textbox for the query?

Listview controls are really meant for structured data, for example the results of an SQL query.

 

[dboone25]

ahh right...yes!! Thank you...could you show me how to do this?

 

[Norie]

What's the name of the text file with the SQL query in it?