File Dialog-Browse/Save/Append

[OBP]

OK, checked and working.
I will post the code in full.

Dim CheckFile, FileFound As String, i As Integer, datastring As String, xchar As String
Dim sDest As String, firstfile As String, filcopied As Integer, reccount As Integer
Dim ctlList As Control, varItem As Integer, posn As Integer, FileName As String, result As Integer, fileinarray As Integer
CheckFile = Array("TESTMAIN", "TESTCASE", "TESTPRIMARY", "TESTSECONDARY", "TESTALTERNATE")
 On Error GoTo errorcatch
 'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\TestFolder\" ' & CheckFile(0) ' will be changed to CurrentProject.path & MAXIMOExports
Set ctlList = Me.List0
For varItem = 0 To ctlList.ListCount - 1
    For x = 1 To Len(ctlList.ItemData(varItem))
         ' Parse filename only
        If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
    Next x
    firstfile = ctlList.ItemData(varItem)
    FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
    'MsgBox varItem & " - " & FileName
    result = 0
    fileinarray = 0
    filecopied = 0
    For i = LBound(CheckFile) To UBound(CheckFile)
        result = InStr(1, ctlList.ItemData(varItem), CheckFile(i))
        'MsgBox ctlList.ItemData(varItem) & " - " & CheckFile(i)
        If result <> 0 Then
            MsgBox result & " - File is in array append it"
            fileinarray = 1
            FileFound = Dir(sDest & "*" & CheckFile(i) & "*.*")
            If FileFound = "" Then
                fileinarray = 0
                Exit For
            End If
            GoSub Appendsub
            'Open sSourceFile For Input As SourceFileNum
            Do Until FileFound = ""   ' Start the loop.
                ctr = ctr + 1
                FileFound = Dir()   ' Getting next entry.
                If FileFound <> "" Then GoSub Appendsub
            Loop
            Exit For
        End If
    Next i
    If fileinarray = 0 Then
        If filecopied = 0 Then
            MsgBox result & " - file is not in array copy it"
            FileCopy ctlList.ItemData(varItem), sDest & FileName
            filecopied = 1
        End If
    End If
Next varItem

Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description
            
Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
datastring = ""
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
    xchar = Input(1, #1)
    If xchar = vbLf Then reccount = reccount + 1
        If reccount > 0 Then
            datastring = datastring & xchar
        End If
Loop
'MsgBox datastring
Print #2, datastring
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

[OBP]

If you have any files that will exceed the 65,000 string character limit we can instead print the datastring after every vbLf, ie one line at a time.

[OBP]

Sam, the problem is that the Data and the headings are in one RECORD, but the record delimiter is a vbLf instead vbCf, so VBA thinks the whole file is just one record and using LineInput it imports the whole file as 1 record.
Andy's prvious post deals with it in detail.
So I basically used the same method to read the data one character at a time ans use it to create a string to write to the Master file.

[OBP]

Andy, humble apologies, do you remember the very first database I sent on the question that had the "Browsing" routine in it using File Scripting.
Well, File Scripting reads your text files one line at a time (where open file #1 can't).

So to read it one line at a time all it takes is

Dim oFSO As New FileSystemObject
Dim oFS
Set oFS = oFSO.OpenTextFile("C:\Users\A C\Downloads\TestFolder\TESTMAIN000.TXT")

Do Until oFS.AtEndOfStream
    sText = oFS.ReadLine
    MsgBox sText
Loop

Practically all of the work that we have done over the last 2 questions could have been easily handled using FSO.

[SamT]

Notepad has a HardCoded limit of 255 characters per line, So that Notepad test does not return a true result of where the actual line of text ends.

Most editors have some arbitrfary line length, some are dependent on the machine it runs on, some,like Notepad, are hard coded.

Sam, the problem is that the Data and the headings are in one RECORD, but the record delimiter is a vbLf instead vbCf, so ... whole file is just one record and using LineInput it imports the whole file as 1 record.

Not so.
The File is a Record Set
One actual line in the file is a Record. See First sentence above.
The Record Separator is vbLf
One Header Name is one Field.
The Field separator is the character(s) between Header Names

What is/are the character(s) between two Header Names? That is the Separator I am talking about

Try this

tmp1 =Split(File, vbLf)
tmp2 = Split(tmp1(0), "Separator_I_am_talking_about")
MsgBox UBound(tmp2)
MsgBox "the number of Fields in a Record is: " & UBound(tmp2) + 1
For i = 0 to UBound(tmp2)
MsgBox tmp2(i)
next

7Zip one of the shortest files and upload it. I will open it with UltraEdit, which has no arbitrary line length.
Please use 7zip, since I know that it does not change the file when it compresses it like some do.

Gotta go build something, be back when it's too hot outside.

[OBP]

Sam, just go and read Andy's previous post here
http://www.vbaexpress.com/forum/show...ds-into-Access.

We went right through this and Jonh pointed out that it uses vbLf which is the record seperator and the comma is the field seperator, but it is now completely immaterial, as FSO can read the file correctly where as Opening the file and using LineInput does not.

[andycl]

OBP, that seems to have got it. I think I had a typo that I didnt catch until now. Thats my bad. But this is working great. The routine does not bring the header rows during data transfer of appending a file. I'll run through a few files for testing but from a quick look this seems to have done the trick. I also have the vbYesNo box working like I want. Yes to append and No to save file elsewhere. As of now, I don't believe there will be files with 65,000 character string. But it may be something I'm not aware of.

Thanks as always for the help.

[OBP]

Andy, after you have shown them it working, it would be worth spending the time going over to FSO, it would make the programming much shorter and simpler for both importing and appending.

[andycl]

Would the program crash if there were character strings exceeding 65,000? All files are appending correctly (without header rows) except one. It is the largest file size of the files. I don’t know if that has anything to do with it or not but the program locks up on that one file when appending.

[OBP]

It is possible, I will look at an alternative.

[andycl]

Ok good deal. The file structure isn't different but looking there may be some strings in that 65,000 range

[OBP]

Andy, replace the subroutine with this and test it.

Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
datastring = ""
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
    xchar = Input(1, #1)
    If xchar = vbLf Then
        reccount = reccount + 1
        datastring = datastring & xchar
        If reccount > 1 Then
            Print #2, datastring
            datastring = ""
        End If
    End If
    If reccount > 1 Then datastring = datastring & xchar
Loop
'MsgBox datastring
'Print #2, datastring
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

I still think FSO is the way to go, but try this one for now.

[SamT]

Hey, you got it working.

And I agree that the FSO is the way to go

[andycl]

So it is the file size that causes it lock up. When I reduced the file down, it worked as expected.

[andycl]

I'll have to look at the FSO option. Maybe it handles larger files.

[OBP]

When you say "So it is the file size that causes it lock up. When I reduced the file down, it worked as expected.", is that with the newest code that I posted or the original code?
As the newest code is using a string that is only the size of 1 record.

[OBP]

I have already posted the code for reading the data using FSO, the only thing you have to do is set the VBA library References to
Microsoft Scripting Runtime.

[andycl]

It was my own oversight. I didnt change this line If reccount > 0 Then to If reccount > 1 Then
Works the way it should. So is it still reading each character or the entire file as a record?

[andycl]

Is the FSO code you're talking about in another thread?

[OBP]

It was my own oversight. I didnt change this line If reccount > 0 Then to If reccount > 1 Then
Works the way it should. So is it still reading each character or the entire file as a record?

The latest version reads each character to build up a Record (datastring) ie uses vblf to end adding to it and append it to the Master file.
It then clears the datastring to build the next record.