PDA

View Full Version : User Form Code to Load Text Files



C.R.
11-05-2014, 11:10 AM
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.


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



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


Thanks in advance for any suggestions…


C.R.

C.R.
11-05-2014, 11:14 AM
Not sure why that code didn't post properly... Sorry...
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

Tom Jones
11-06-2014, 01:09 PM
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
11-06-2014, 01:17 PM
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.
11-24-2014, 12:58 PM
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
11-24-2014, 01:23 PM
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.
11-24-2014, 01:30 PM
Awesome. I will give this a shot.
Thank You!
C.R.