?
Solved

Merge text files within ms access using vba

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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