Using Office 2003 and Windows XP; and using using VBA from MS-Excel;

1) There will be several (between 1 and 10) fixed width text files in a
specific folder, each formatted the same way; the files range from a few kb
to 100kb in size.

2) I need a program that will sequentially open each text file and copy the
contents;

3) The contents of each separate text file, along with all formatting, must
be transferred undisturbed into a new fixed width text file, appending the
contents of each text file into a new SINGLE merged text file;

I can already code the part where the list of files is captured and each may
be opened and closed. I need help with the part where the data is copied from
the text file and then written to the new single merge file. Can someone
please post some example code that would help me get started?

Should I use FileSystemObject? Or something else? Suggestions? Help?

Thanks much in advance for your assistance.

Adapt this:

Sub a()
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array("c:\File1.txt", "c:\File2.txt")
Open "c:\file3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1
End Sub


--
Jim
"XP" wrote in message
...
| Using Office 2003 and Windows XP; and using using VBA from MS-Excel;
|
| 1) There will be several (between 1 and 10) fixed width text files in a
| specific folder, each formatted the same way; the files range from a few
kb
| to 100kb in size.
|
| 2) I need a program that will sequentially open each text file and copy
the
| contents;
|
| 3) The contents of each separate text file, along with all formatting,
must
| be transferred undisturbed into a new fixed width text file, appending the
| contents of each text file into a new SINGLE merged text file;
|
| I can already code the part where the list of files is captured and each
may
| be opened and closed. I need help with the part where the data is copied
from
| the text file and then written to the new single merge file. Can someone
| please post some example code that would help me get started?
|
| Should I use FileSystemObject? Or something else? Suggestions? Help?
|
| Thanks much in advance for your assistance.

I adapted your example code and it works perfectly - and fast...

Thanks much Jim!

"Jim Rech" wrote:

Adapt this:

Sub a()
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array("c:\File1.txt", "c:\File2.txt")
Open "c:\file3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1
End Sub


--
Jim
"XP" wrote in message
...
| Using Office 2003 and Windows XP; and using using VBA from MS-Excel;
|
| 1) There will be several (between 1 and 10) fixed width text files in a
| specific folder, each formatted the same way; the files range from a few
kb
| to 100kb in size.
|
| 2) I need a program that will sequentially open each text file and copy
the
| contents;
|
| 3) The contents of each separate text file, along with all formatting,
must
| be transferred undisturbed into a new fixed width text file, appending the
| contents of each text file into a new SINGLE merged text file;
|
| I can already code the part where the list of files is captured and each
may
| be opened and closed. I need help with the part where the data is copied
from
| the text file and then written to the new single merge file. Can someone
| please post some example code that would help me get started?
|
| Should I use FileSystemObject? Or something else? Suggestions? Help?
|
| Thanks much in advance for your assistance.

El martes, 5 de febrero de 2008, 15:48:20 (UTC-5), Jim Rech escribió:
Adapt this:

Sub a()
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array("c:\File1.txt", "c:\File2.txt")
Open "c:\file3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1
End Sub


--
Jim
"XP" wrote in message
...
| Using Office 2003 and Windows XP; and using using VBA from MS-Excel;
|
| 1) There will be several (between 1 and 10) fixed width text files in a
| specific folder, each formatted the same way; the files range from a few
kb
| to 100kb in size.
|
| 2) I need a program that will sequentially open each text file and copy
the
| contents;
|
| 3) The contents of each separate text file, along with all formatting,
must
| be transferred undisturbed into a new fixed width text file, appending the
| contents of each text file into a new SINGLE merged text file;
|
| I can already code the part where the list of files is captured and each
may
| be opened and closed. I need help with the part where the data is copied
from
| the text file and then written to the new single merge file. Can someone
| please post some example code that would help me get started?
|
| Should I use FileSystemObject? Or something else? Suggestions? Help?
|
| Thanks much in advance for your assistance.

Hi Jim

I've used your example and it works creating a new file, but is there a way to add the text from file1.txt and file2.txt into the file3.txt.

I mean, updating the file 3 every time I run the code and don´t create a new file.

El martes, 5 de febrero de 2008, 15:48:20 (UTC-5), Jim Rech escribió:
Adapt this:

Sub a()
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array("c:\File1.txt", "c:\File2.txt")
Open "c:\file3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1
End Sub


--
Jim
"XP" wrote in message
...
Using Office 2003 and Windows XP; and using using VBA from MS-Excel;

1) There will be several (between 1 and 10) fixed width text files in a
specific folder, each formatted the same way; the files range from a few
kb to 100kb in size.

2) I need a program that will sequentially open each text file and copy
the contents;

3) The contents of each separate text file, along with all formatting,
must be transferred undisturbed into a new fixed width text file, appending
the contents of each text file into a new SINGLE merged text file;

I can already code the part where the list of files is captured and each
may be opened and closed. I need help with the part where the data is
copied from the text file and then written to the new single merge file.
Can someone please post some example code that would help me get started?

Should I use FileSystemObject? Or something else? Suggestions? Help?

Thanks much in advance for your assistance.

Hi Jim

I've used your example and it works creating a new file, but is there a way
to add the text from file1.txt and file2.txt into the file3.txt.

I mean, updating the file 3 every time I run the code and don´t create a new
file.

If this is being done correctly, each new block of data should be 'appended' to
the target file; -all of which gets done in memory using standard VBA file I/O
functions.

So once the 1st target file is created it would thereafter be re-opened to
append each new block of data. The process will create the file if it doesn't
already exist when it 'dumps' the 1st block of data into it.

Tht said, the proper way to do this is to recurse a folder for .txt files (or
whatever file extension you specify; -csv,dat,tsv...) and append them to a
single file. There already exists a free stand-alone utility (non-Excel) called
*TXTcollector* that you can download here...

https://txtcollector.en.softonic.com/

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks a Lot Jim for the quick Reply.

In some site, they adapted your code to relatives paths this way:

---
Dim r As String
r = Application.ThisWorkbook.Path
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array(r & "\File1.txt", r & "\File2.txt")
Open r & "\File3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1

---


But As i told you, the file 3 doesn't append the text, just create a new one every time the code is ejecuted.

Som help would be great.

Also, I've note that actually, the "CurrSrc" and "DestFile" variables are declared but not used at all, they can be "erased" from the code?

Thanks a Lot Jim for the quick Reply.

In some site, they adapted your code to relatives paths this way:

---
Dim r As String
r = Application.ThisWorkbook.Path
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array(r & "\File1.txt", r & "\File2.txt")
Open r & "\File3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1

---


But As i told you, the file 3 doesn't append the text, just create a new one
every time the code is ejecuted.

Som help would be great.

Also, I've note that actually, the "CurrSrc" and "DestFile" variables are
declared but not used at all, they can be "erased" from the code?

I'm not Jim! I don't see how this code will do what you want, NOR do I see any
point *spending* the energy required to rewrite a solution that will work (I
have Lou Gehrig's) when the already existing free utility I posted a link to
does an excellent job of merging text files.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Am 13.06.2017 um 18:38 schrieb :
Thanks a Lot Jim for the quick Reply.

In some site, they adapted your code to relatives paths this way:

---
Dim r As String
r = Application.ThisWorkbook.Path
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array(r & "\File1.txt", r & "\File2.txt")
Open r & "\File3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1

---


But As i told you, the file 3 doesn't append the text, just create a new one every time the code is ejecuted.

Som help would be great.

Also, I've note that actually, the "CurrSrc" and "DestFile" variables are declared but not used at all, they can be "erased" from the code?
Just another idea using the oldstyle CMD Shell:

Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
Const COPYCMD As String = "for %f in ({0}) do type ""%f"" {1}"

Dim strCopyCommand As String
Dim dblRetVal As Double

strCopyCommand = Replace(COPYCMD, "{0}", InFile)
strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)

SyncShell "cmd.exe /c " & strCopyCommand, vbNormalFocus
End Sub

Public Function SyncShell(ByVal Cmd As String, ByVal WindowStyle As
VbAppWinStyle) As Long
SyncShell = VBA.CreateObject("WScript.Shell").Run(Cmd, WindowStyle, True)
End Function

Usage example:
MergeFiles "file1.txt file2.txt", "file3.txt"

You will be able to modify the copy cmd to your needs and you can even
use standard cmd syntax with wildcards like
MergeFiles "File*.txt" , "OutFile.txt"
and so on.

Ulrich

Am 14.06.2017 um 00:38 schrieb Ulrich Möller:
Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
Const COPYCMD As String = "for %f in ({0}) do type ""%f"" {1}"

Dim strCopyCommand As String
Dim dblRetVal As Double

strCopyCommand = Replace(COPYCMD, "{0}", InFile)
strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)

SyncShell "cmd.exe /c " & strCopyCommand, vbNormalFocus
End Sub

Sorry for the mixed data types, here the corrected version:

Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
Const COPYCMD As String = "for %f in ({0}) do type ""%f"" {1}"

Dim strCopyCommand As String
Dim lngRetVal As Long

strCopyCommand = Replace(COPYCMD, "{0}", InFile)
strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)

lngRetVal = SyncShell("cmd.exe /c " & strCopyCommand, vbNormalFocus)
End Sub

Public Function SyncShell(ByVal Cmd As String, ByVal WindowStyle As
VbAppWinStyle) As Long
SyncShell = VBA.CreateObject("WScript.Shell").Run(Cmd, WindowStyle,
True)
End Function

Ulrich

Am 14.06.2017 um 00:38 schrieb Ulrich Möller:
Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
Const COPYCMD As String = "for %f in ({0}) do type ""%f"" {1}"

Dim strCopyCommand As String
Dim dblRetVal As Double

strCopyCommand = Replace(COPYCMD, "{0}", InFile)
strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)

SyncShell "cmd.exe /c " & strCopyCommand, vbNormalFocus
End Sub

Sorry for the mixed data types, here the corrected version:

Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
Const COPYCMD As String = "for %f in ({0}) do type ""%f"" {1}"

Dim strCopyCommand As String
Dim lngRetVal As Long

strCopyCommand = Replace(COPYCMD, "{0}", InFile)
strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)

lngRetVal = SyncShell("cmd.exe /c " & strCopyCommand, vbNormalFocus)
End Sub

Public Function SyncShell(ByVal Cmd As String, ByVal WindowStyle As
VbAppWinStyle) As Long
SyncShell = VBA.CreateObject("WScript.Shell").Run(Cmd, WindowStyle, True)
End Function

Ulrich

Hey Ulrich,
Nice to see you 'spreading your wings'!<g

FWIW:
My experience with users for this type of utility is (typically) where a share
folder is used as a repository for collecting data from various sources over a
given period. Once per period the data gets consolidated into a single file for
processing further by some means, and the source files get deleted so the
folder only contains un-processed data files.

That said, your example could be modified to drill drown a selected folder (and
optionally, subfolders) so the user doesn't have to specify filenames. Your
sample addresses EXACTLY what the OP states is wanted; -I'm guessing that's not
the 'actual' scenario...<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am 14.06.2017 um 00:58 schrieb GS:
Nice to see you 'spreading your wings'!<g

Why not? ;-)
FWIW:
My experience with users for this type of utility is (typically) where
a share folder is used as a repository for collecting data from
various sources over a given period. Once per period the data gets
consolidated into a single file for processing further by some means,
and the source files get deleted so the folder only contains
un-processed data files.

That said, your example could be modified to drill drown a selected
folder (and optionally, subfolders) so the user doesn't have to
specify filenames. Your sample addresses EXACTLY what the OP states is
wanted; -I'm guessing that's not the 'actual' scenario...<g

In the old days this was a classical job for some commands from the
command line. My intention was to bring this technique back to memory
and how we can use it now a days. Of course this can also be done with
some vba but what about the fun?

Ulrich

Hi Garry,

Am 14.06.2017 um 00:58 schrieb GS:
Nice to see you 'spreading your wings'!<g

Why not? ;-)
FWIW:
My experience with users for this type of utility is (typically) where a
share folder is used as a repository for collecting data from various
sources over a given period. Once per period the data gets consolidated
into a single file for processing further by some means, and the source
files get deleted so the folder only contains un-processed data files.

That said, your example could be modified to drill drown a selected folder
(and optionally, subfolders) so the user doesn't have to specify filenames.
Your sample addresses EXACTLY what the OP states is wanted; -I'm guessing
that's not the 'actual' scenario...<g

In the old days this was a classical job for some commands from the command
line. My intention was to bring this technique back to memory and how we can
use it now a days. Of course this can also be done with some vba but what
about the fun?

Ulrich

Ha, ha! The 'fun' only exists for the old timers, right! These days we only see
newbie OPs (which I am one of in the Classic VB forums), mostly looking for
'turn-key' solutions and so won't appreciate your offering same as I do!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion