Solved

Merge text files within ms access using vba

Posted on 2016-07-21
5
63 Views
Last Modified: 2016-07-22
I have three fixed width text files I export from within access.  Once the three text files are exported I would like to combine them into one text file and add the date to the name of the combined file.  I would like to do this using VBA so that I can execute the function with the click of a button.
0
Comment
Question by:PriceD
  • 2
  • 2
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41723993
why are you exporting three text file?
is it not possible to combine all  the three files prior to exporting as text file?
0
 
LVL 15

Author Comment

by:PriceD
ID: 41724536
The three files have different fixed width fields that need to be in a specific order.  

There is a header (file 1)  has 10 fields that have to be fixed width
Records (file 2)  has 18 fields that have to be fixed width, different fields, different widths then the header.
Footer (file 3) has 7 fields different widths etc

I need to combine these three text files into one so that I can submit the one text file to an insurance agency that has set these parameters for automatic claims submissions.

If there is a better way to combine this data from within access and keep the width criteria I welcome any suggestions.
0
 
LVL 39

Expert Comment

by:als315
ID: 41724639
You can do it with shell command or with script:
Sub tst1()
Dim osh As Object
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim strCopy As String
Set osh = VBA.CreateObject("WScript.Shell")
'Create your copy command
strCopy = "copy c:\somefolder\a.txt + c:\somefolder\b.txt + c:\somefolder\c.txt c:\somefolder\c" & Format(Date, "-YYYY-MM-DD") & ".txt"

osh.Run "cmd.exe /S /C " & strCopy, windowStyle, waitOnReturn
End Sub

Open in new window

with script you can wait until script is finished. It is helpful if files are large and you like to check result
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 41724650
test this codes, add windows scripting runtime library to your references


Sub combineTextFiles()
Dim fPath As String, fName As Object, txtContent As String
Dim fArr() As Variant, j As Integer, newFile As Object
Dim fs As Object
fPath = CurrentProject.Path & "\"

 'place your file in an array in the manner that you like them to appear in the new file

fArr = Array("file1.txt", "file2.txt", "file3.txt")

Set fs = CreateObject("Scripting.filesystemobject")
Set newFile = fs.createtextfile(fPath & "myTextFile_" & Format(Now(), "yyyymmdd") & ".txt", True)

For j = LBound(fArr) To UBound(fArr)
    Set fName = fs.OpenTextFile(fPath & fArr(j), ForReading)
        If fName.AtEndOfStream Then
            txtContent = ""
            Else
            txtContent = fName.ReadAll
        End If
        newFile.Write txtContent & Chr(10) & Chr(13)
Next
set fs=nothing
End Sub

Open in new window

0
 
LVL 15

Author Closing Comment

by:PriceD
ID: 41724975
Thanks Rey!

Had a bit of trouble until I realised I hadn't added the "Microsoft Scripting Runtime"  as you had instructed.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now