Importing multiple text files to worksheets

[josnah]

I have 3 files in a the folder C:\SOA\

1.A0001-D
2.A0001-H
3.A0001-F

Files Ending with D contains Details, with H contains Header info & F
with Footer info.
A0001 is the account number

I will need to import all three files into 3 worksheets in one
workbook.

Question is how can I use the account no & path from the first file
imported to import the next two files?

Any help is greatly appreciated!

My recorded macro below:


Sub ImportSOA()
FNameD = Application.GetOpenFilename(FileFilter:="All Files (*.*),
*.*", Title:="Please select the Details file.")

If FNameD = False Then
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Application.DisplayAlerts = False
Workbooks.OpenText Filename:=FNameD, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:= _
Array(Array(0, 2), Array(7, 2), Array(37, 2), Array(45, 2),
Array(46, 2), Array(49, 2), _
Array(52, 2), Array(61, 2), Array(69, 1), Array(73, 4),
Array(81, 1), Array(92, 1), Array( _
101, 1), Array(112, 1), Array(123, 1), Array(134, 1),
Array(144, 1))
End If

Application.ScreenUpdating = False

ActiveSheet.Name = "DETAIL"

Sheets.Add Type:="Worksheet"
With ActiveSheet.QueryTables.Add(Connection:= _
* "TEXT;"C:\SOA\A0001-F", Destination:=Range("A2"))
..Name = "Footer"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..TextFilePromptOnRefresh = False
..TextFilePlatform = xlWindows
..TextFileStartRow = 1
..TextFileParseType = xlFixedWidth
..TextFileTextQualifier = xlTextQualifierDoubleQuote
..TextFileConsecutiveDelimiter = False
..TextFileTabDelimiter = True
..TextFileSemicolonDelimiter = False
..TextFileCommaDelimiter = False
..TextFileSpaceDelimiter = False
..TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
..TextFileFixedColumnWidths = Array(13, 13, 13, 13, 13, 13, 14,
12)
..Refresh BackgroundQuery:=False
End With

Sheets.Add Type:="Worksheet"
With ActiveSheet.QueryTables.Add(Connection:= _
* "TEXT;"C:\SOA\A0001-H", Destination:=Range("A2"))
..Name = "Header"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..TextFilePromptOnRefresh = False
..TextFilePlatform = xlWindows
..TextFileStartRow = 1
..TextFileParseType = xlFixedWidth
..TextFileTextQualifier = xlTextQualifierDoubleQuote
..TextFileConsecutiveDelimiter = False
..TextFileTabDelimiter = True
..TextFileSemicolonDelimiter = False
..TextFileCommaDelimiter = False
..TextFileSpaceDelimiter = False
..TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 2, 4, 2, 1,
1)
..TextFileFixedColumnWidths = Array(47, 30, 30, 30, 30, 3, 5,
13, 2, 20)
..Refresh BackgroundQuery:=False
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Guest]

Hi Josnah

I think your file names can be calculated with the below

FNameF = Left$(FNameD, Len(FNameD) - 1) & "F"
FNameH = Left$(FNameD, Len(FNameD) - 1) & "H"

 

[josnah]

Thanks very much Tony!
Somehow it didn't work with *-Len(FNameD) - 1-* but with Len*-(FNameD)
- 2-*!

Now I am wondering how I can make this macro loop and choose the next
account?

 

[Guest]

Hi

Try looking a Scripting.FileSystemObject in the help text this has a number
of tools that will help you work with files and directories.

 

[josnah]

Hi Tony,

I have figured out why it didn't work with Len(FNameD) - 1 cos the
FNameD ends with a "." and so will need to -2 chars instead of just 1.

Thanks again! Appreciate your help!