Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Merge text files within ms access using vba

Posted on 2016-07-21
5
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

722 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