Importing multiple text files to worksheets
 

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? :confused:

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


--
josnah
------------------------------------------------------------------------
josnah's Profile: http://www.excelforum.com/member.php...fo&userid=6334
View this thread: http://www.excelforum.com/showthread...hreadid=548074

Importing multiple text files to worksheets
 

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"
--
Tony Green


"josnah" wrote:


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? :confused:

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


--
josnah
------------------------------------------------------------------------
josnah's Profile: http://www.excelforum.com/member.php...fo&userid=6334
View this thread: http://www.excelforum.com/showthread...hreadid=548074

Importing multiple text files to worksheets
 

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? :confused:


--
josnah
------------------------------------------------------------------------
josnah's Profile: http://www.excelforum.com/member.php...fo&userid=6334
View this thread: http://www.excelforum.com/showthread...hreadid=548074

Importing multiple text files to worksheets
 

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.

--
Tony Green


"josnah" wrote:


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? :confused:


--
josnah
------------------------------------------------------------------------
josnah's Profile: http://www.excelforum.com/member.php...fo&userid=6334
View this thread: http://www.excelforum.com/showthread...hreadid=548074

Importing multiple text files to worksheets
 

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!


--
josnah
------------------------------------------------------------------------
josnah's Profile: http://www.excelforum.com/member.php...fo&userid=6334
View this thread: http://www.excelforum.com/showthread...hreadid=548074