User Form Code to Load Text Files

[C.R.]

Hello,


I have a user form that is used to select worksheets to print.


I am trying to adapt it to load text files for worksheets that are selected in the user form.
This is for a workbook that is used for sales reporting at the end of each workday for our company.
There are 50 SAP text files that are loaded to this workbook.
Sometimes, a user may forget to run one or more of the reports.
So the thought is, with the user form, the user could just run the missing report in SAP, and launch the user form to load it.
Since each worksheet loads a different text file maybe a Case Statement? Not sure.


[VBA]Private Sub CommandButton2_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then


'|||||||||||||||||||||||||||||||||||||||||||||


Sheets(ListBox1.List(iloop - 1, 0)).PrintOut '(Change this part to load text files)


'|||||||||||||||||||||||||||||||||||||||||||||


ListBox1.Selected(iloop - 1) = False
End If
Next


'ActiveWorkbook.Connections("test_data").Refresh


End Sub
[/VBA]


I cannot seem to get this to work. A sample Workbook is attached.


Thanks in advance for any suggestions…


C.R.

[C.R.]

Not sure why that code didn't post properly... Sorry...
[VBA]Private Sub CommandButton2_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then


'|||||||||||||||||||||||||||||||||||||||||||||


Sheets(ListBox1.List(iloop - 1, 0)).PrintOut '(Change this part to load text files)


'|||||||||||||||||||||||||||||||||||||||||||||


ListBox1.Selected(iloop - 1) = False
End If
Next


'ActiveWorkbook.Connections("test_data").Refresh


End Sub
[/VBA]

[Tom Jones]

aThis is the code:

Private Sub CheckBox1_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 ListBox1.Selected(iloop - 1) = CheckBox1.Value
Next
End Sub
Private Sub CommandButton1_Click()
    Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
   
   '|||||||||||||||||||||||||||||||||||||||||||||
   Sheets(ListBox1.List(iloop - 1, 0)).PrintOut '(Change this part to load text files)
   '|||||||||||||||||||||||||||||||||||||||||||||
   
   ListBox1.Selected(iloop - 1) = False
 End If
 Next
 
 'ActiveWorkbook.Connections("test_data").Refresh
 
 End Sub
Private Sub UserForm_Initialize()
Dim sSheet
    For Each sSheet In Sheets(Array("Z00", "Z01", "Z02", "Z03", "Z04", "Z05", "Z06", "Z07", _
                                    "Z08", "Z09", "Z10"))
            ListBox1.AddItem sSheet.Name
    Next sSheet
End Sub

[westconn1]

There are 50 SAP text files that are loaded to this workbook.

what do you mean by loaded? imported into a worksheet? or what?
what is the criteria for selecting from the 50 files for each worksheet?
does the name of the text file match the name of the worksheet?
are all the text files in the same folder?

please supply more information

[C.R.]

westconn1,

Apologies for my bad form for not responding sooner, and my lack of clarity. Life took a left turn.
Yes, Loading means importing to a worksheet.
Criteria: If the text file creation date does not match today's date, select that file on the user form (had not even thought of a criteria for this.)
Text files do not match worksheet names.
All of the text files are in the same folder.

Many thanks,

C.R.

P.S. Tom Jones... thanks for properly posting the code.

[westconn1]

you can use DIR or FSO to return all files with some extension (.txt) from a specific folder, check the date, open the file and read the contents, parse into the excel sheet on the next empty row (or leave a blank row between files)

this is a basic example, will get all text files from a folder

myPath = "c:\temp\"
fname = Dir(myPath & "*.txt")
Do While Len(fname) > 0
    If Not FileDateTime(myPath & fname) > Date Then
        f = FreeFile
        Open myPath & fname For Input As f
            flines = Split(Input(LOF(f), #f), vbNewLine)
            Close f
        nextrow = Cells(Rows.Count, 1).End(xlUp).Row + 1 ' assumes activesheet
        For l = 0 To UBound(flines) - 1
            'parse the lines into excel worksheet as required
            Cells(nextrow, 1).Value = flines(l)  ' put file line into single cell,  change to suit
            nextrow = nextrow + 1
        Next
    End If
    fname = Dir
Loop

change paths etc to suit, specify target worksheet

[C.R.]

Awesome. I will give this a shot.
Thank You!
C.R.