macro

i need a macro to do the following.

- from my workbook go to c-drive and floder name Debtors.
- from there open each text file and place into my workbook by this
description, eg Balancing Segment: 20 (so each text file will have
different Balancing Segment number)
- in my workbook i can number each sheet eg from 1, 2, 3....... to 87

You didn't say how or where to find the 'Balancing Segment: 20' type of
information, so I can't get down to that level of operation. But what I've
provided below is a sort of "proof of concept" to show how to get to the
folder and identify all .txt files in it and read them.

To use the code just to see if it's part of a good start: open a new
workbook, change one of the sheet's names to TextData. Then press
[Alt]+[F11] to open the VB Editor and then use Insert -- Module to create a
new code module. Copy the code below and paste it into the new code module.
Close the VB Editor.

Use Tools -- Macro -- Macros to choose this macro and click the [Run]
button. It'll read the contents of the text files into the TextData sheet.
But that's all it does.

Before we can go any further, we need to know how to identify the Balancing
Segment information and act on that. If you want, get in touch with me via
email at (remove spaces)
Help From @JLatham Site. com
and attach a copy of at least one of the .txt files to the email and remind
me in the email of what you need to do.

Here's the code:

Sub ImportFromTextFiles()
'full path to the text files
Const basicPath = "C:\Debtors\"
Dim copyToSheetName As String
Dim textFileName As String
Dim buffNum As Integer
Dim textFileRow As String

'get a text filename
textFileName = Dir$(basicPath & "*.txt")
Do While textFileName ""
buffNum = FreeFile()
Open basicPath & textFileName For Input As #buffNum
'read the text file line by line
Do While Not EOF(buffNum)
Line Input #buffNum, textFileRow
'this is where you would
'figure out which sheet to put the
'data onto and do that
'but I'm not sure how to do that
'based on the limited information
'you've provided at this point
'
'for now, I'm just writing
'the name of the file to col A
'and each row from the file to col B
'on a worksheet named "TextData"
copyToSheetName = "TextData"
Worksheets(copyToSheetName).Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0) = _
textFileName
Worksheets(copyToSheetName).Range("A" & _
Rows.Count).End(xlUp).Offset(0, 1) = _
textFileRow
Loop
'close the file
Close #buffNum
'get next text filename
textFileName = Dir$()
Loop
MsgBox "All files processed"
End Sub


"Rohit" wrote:

i need a macro to do the following.

- from my workbook go to c-drive and floder name Debtors.
- from there open each text file and place into my workbook by this
description, eg Balancing Segment: 20 (so each text file will have
different Balancing Segment number)
- in my workbook i can number each sheet eg from 1, 2, 3....... to 87