Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 28th, 2006, 05:21 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default multiple textfiles to one textfile for import

Hello,

I am searching for days now and still have a problem with some coding.

I build an Access application which makes .txt files of .port files. These .txt files need to be writen in 1 other .txt file, which will be imported in the Access application. The name of the .txt files are always different.

So I made this:

Function ImportPorB()

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objImportB
Set objImportB = objFSO.CreateTextFile("V:\aa\bb\cc\ImportB.txt")

Dim objFile
Dim Src
Dim NewSrc
Dim Order
Dim ImportB

For Each objFile In objFSO.GetFolder("V:\aa\bb\cc\").Files
Order = Left(objFile.Name, 8 )
Src = "V:\aa\bb\cc\" & Order & ".por "
NewSrc = "V:\aa\bb\cc\" & Order & ".txt "

If Right(objFile.Name, 5) = "B.por" Then

Debug.Print Src
Debug.Print NewSrc

FileCopy Src, NewSrc

End If

Next
Set ImportB = objFSO.OpenTextfile("V:\aa\bb\cc\ImportB.txt")
NewSrc.write ImportB.ReadAll
ImportB.Close
NewSrc.Close

Set objFSO = Nothing

End Function

The first part is working fine! The code is making a ImportB.txt and make of all .por files a .txt file.

But he doesn't fill the ImportB.txt and gives a warning.

So I made this:

Open "V:\aa\bb\cc\ImportB.txt" For Input As #1
Open "V:\aa\bb\cc\" & Order & ".txt " For Output As #2
Write #1,
Close #1
Close #2

But still a warning.

So I tried this:
Dim nExportbestand As Integer
nExportbestand = FreeFile

Open "V:\aa\bb\cc\ImportB.txt" For Output As #nExportbestand

Print #nExportbestand, "bla bla"

Close #nExportbestand

Still a warning.

My last idea was this:
Set FS = CreateObject("Scripting.FileSystemObject")
Set a = FS.CreateTextFile("V:\aa\bb\cc\ImportB.txt")
a.Write (NewSrc)
a.Close

No warnings this time. But now he writes the pathname of the file in ImportB.txt instead of the text in the textfile.

What am I doing wrong? :(:(

 
Old September 28th, 2006, 07:25 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Using text1.txt as the source file, and text2.txt as the target file:

Const ForReading = 1
Const ForAppending = 8

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\text1.txt", ForReading)
strContents = objFile.ReadAll
objFile.Close

Set objFile = objFSO.OpenTextFile("C:\text2.txt", ForAppending)
objFile.Write strContents
objFile.Close

This opens the first file, reads all of its contents into the variable strContents and then closes the first file. It opens the second file, and appends strContents in line after the original contents of the second file.

Does this help?



mmcdonal
 
Old September 28th, 2006, 07:49 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well in the mean while I wrote this:

Set a = objFSO.CreateTextFile("V:\aa\bb\cc\ImportB.txt")
Set b = objFSO.OpenTextFile(NewSrc)
a.write b.ReadAll
a.Close

It is working, but he only writes 1 file into the ImportB.txt and not all files.

 
Old September 28th, 2006, 08:02 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Take one file at a time, read it, and then append it, then go to the next file, read it, and then append it, etc.

There are more operations this way, but it will work.

If you must put all the data into one variable before appending, remember to do this:

strContent = strContents & objFile.ReadAll

Otherwise each new read wipes out the old one in the variable, and you only get one file appended. You may want to do this instead:

strContents = strContents & vbCrLf & objFile.ReadAll

This will make sure there is a line break between rows at the beginning and ends of each file.

Does this help?

mmcdonal
 
Old September 28th, 2006, 08:09 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, a change:

This - strContent = strContents & objFile.ReadAll
Should be - strContents = strContents & objFile.ReadAll


mmcdonal
 
Old September 28th, 2006, 09:03 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I got it up and running!!!
The createtextfile was in the loop. How could I've not seen it!!!!!



Function ImportPor()

    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Dim a As Object
    Set a = objFSO.CreateTextFile("V:\aa\bbImportB.txt")
    Dim b

    Dim objFile
    Dim Src
    Dim NewSrc
    Dim Order

    For Each objFile In objFSO.GetFolder("V:\aa\bb\").Files
        Order = Left(objFile.Name, 8)
        Src = "V:\aa\bb\" & Order & ".por "
        NewSrc = "V:\aa\bb\" & Order & ".txt "

        If Right(objFile.Name, 5) = "B.por" Then

            Debug.Print Src
            Debug.Print NewSrc

            FileCopy Src, NewSrc

            Set b = objFSO.OpenTextFile(NewSrc)
            a.write b.ReadAll
            b.Close

        End If

    Next

    a.Close

End Function






Similar Threads
Thread Thread Starter Forum Replies Last Post
[VB2005]Import textfiles to Excel using QueryTable bellaelysium Visual Studio 2005 0 July 28th, 2008 01:32 AM
How to load multiple textfile royaloba Pro VB 6 2 October 27th, 2006 12:18 AM
Multiple delimiter Text file import into Access Suomi Access VBA 2 June 12th, 2005 10:26 AM
Import Multiple Unknown Excel Spreadsheets in .Net bcarmen Excel VBA 1 January 31st, 2005 04:46 PM
Multiple import kud Excel VBA 0 June 2nd, 2004 01:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.