Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Merge text files within ms access using vba

Posted on 2016-07-21
5
Medium Priority
?
319 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 40

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

886 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