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

[ADG]

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

[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?

[ADG]

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?
>
>
> --
> josnah
> ------------------------------------------------------------------------
> josnah's Profile: http://www.excelforum.com/member.php...fo&userid=6334
> View this thread: http://www.excelforum.com/showthread...hreadid=548074
>
>

[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!