Data from ListBox to Text File

4one4

New Member
Joined
Dec 3, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I am looking for a solution which would help me to copy data from a Userform's ListBox to a text flow. To make it a little bit complicated I would like to transfer just rows which are selected in the ListBox.

So far, got the design and button commands, but when I try to copy the data to the text file I am struggling with the definition of the range (Selected rows in the ListBox).

Any help would be much appreciated. Thank you

[Option Explicit

Private Sub Delet_Record_Button_Click()

Dim i As Integer

For i = 1 To Range("A65356").End(xlUp).Row + 1
If ListBox1.Selected(i) Then
Rows(i + 1).Select
Selection.Delete
End If
Next i

End Sub


Private Sub Save_Records_Button_Click()

Dim wks As Worksheet
Dim AddNew As Range
Set wks = Sheet1

Set AddNew = wks.Range("A65356").End(xlUp).Offset(1, 0)

AddNew.Offset(0, 0).Value = "U01"
AddNew.Offset(0, 1).Value = TextBox1.Text
AddNew.Offset(0, 2).Value = TextBox2.Text
AddNew.Offset(0, 3).Value = TextBox3.Text
AddNew.Offset(0, 4).Value = TextBox4.Text
AddNew.Offset(0, 5).Value = TextBox5.Text
AddNew.Offset(0, 6).Value = TextBox6.Text
AddNew.Offset(0, 7).Value = TextBox7.Text
AddNew.Offset(0, 8).Value = TextBox8.Text
AddNew.Offset(0, 9).Value = TextBox9.Text
AddNew.Offset(0, 10).Value = TextBox10.Text
AddNew.Offset(0, 11).Value = TextBox11.Text
AddNew.Offset(0, 12).Value = TextBox12.Text

ListBox1.ColumnCount = 13

ListBox1.RowSource = "B1:M65356"

End Sub


Private Sub Clear_Form_Button_Click()

Dim iControl As Control

For Each iControl In Me.Controls

If iControl.Name Like "Text*" Then iControl = vbNullString

Next

End Sub

Private Sub Exit_Button_Click()

Dim iExit As VbMsgBoxResult

iExit = MsgBox("Do you want to Exit?", vbQuestion + vbYesNo, "Data Entry Form")

If iExit = vbYes Then

Unload Me

End If

End Sub

Private Sub Generate_Read_Flow_Button_Click()

Dim myFile As String
Dim rng As Range
Dim cellValue As Variant
Dim i As Integer
Dim j As Integer
Dim wks As Worksheet
Set wks = Sheet1

myFile = Application.DefaultFilePath & "\PNxxxxxx.UMR"

Set rng = ?????

Open myFile For Output As #1

For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count

cellValue = rng.Cells(i, j).Value

If j = rng.Columns.Count Then
Print #1, cellValue
Else
Print #1, cellValue,
End If

Next j
Next i

Close #1

MsgBox ("Flow Created & Saved")

End Sub]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
Do you want to save data from the listbox or data from a worksheet based on what's selected in the listbox?
 

4one4

New Member
Joined
Dec 3, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Do you want to save data from the listbox or data from a worksheet based on what's selected in the listbox?
Thank you for the quick response. The priority would be from the list box.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
Perhaps you could adapt this, it writes a comma delimited file containing the data from the selected rows in a listbox.
VBA Code:
Private Sub Generate_Read_Flow_Button_Click()
Dim strPath As String
Dim arrData As Variant
Dim arrDataOut As Variant
Dim cnt As Long
Dim idxRow As Long
Dim idxCol As Long

    strPath = "C:\Test\MyFile.UMR"
    
    With Me.ListBox1
        ReDim arrDataOut(1 To .ListCount)
        For idxRow = 0 To .ListCount - 1
            If Me.ListBox1.Selected(idxRow) Then
                cnt = cnt + 1
                arrData = Application.Index(.List, idxRow + 1, 0)
                arrDataOut(cnt) = Join(arrData, ",")
            End If
        Next idxRow
    End With
    
    If cnt > 0 Then
        ReDim Preserve arrDataOut(1 To cnt)
        Open strPath For Output As #1
            Print #1, Join(arrDataOut, vbCrLf)
        Close #1
    End If
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,132,973
Messages
5,656,179
Members
418,287
Latest member
reba557

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top