File Dialog-Browse/Save/Append

[OBP]

Andy that is very odd, because I have tested it and it works for me, it just doesn't append the first line.

[OBP]

Sam, are you also self taught from the 80s?

[SamT]

yes and no, but from the 70's. Military.

I didn't pick up VBA until ca2004 when I rewrote the Excel Object Model and help files as a learning experience. I am still debating on upgrading to Win 7 and Excel 2007. It's getting boring. I am playing with CLISP at the moment, might set up an LFS Machine for that.

Or... I might grow Pot and go back to Natural Philosophy and finish my treatise on Quantum Gravity. But that would mean refreshing my math skills. Bleh.

[andycl]

This is odd then. I have checked everything I know to check. The routine runs start to finish and the Msgbox gives the indication data was transferred. But there is no data being transferred. If I remove the reccount variable (back to what it was previously), it will append the data. I'm not sure what gives if OBP has it running. And I did declare reccount as an integer.

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

[andycl]

Any ideas? Also, before appending if I want to add a choice for the user to append or save in different location. Can I use the vb yes no msgbox and the msofiledialogsaveas? I've read where save as doesn't actually save the file.

[OBP]

Been busy today cleaning my son's house as he is on holiday and I am off out to Poker in a minute.
How many lines of code were being tranferred before you added reccount in to the subroutine?
Whe I get home I will post my version of the database.

[andycl]

Individual lines in the file? Vary from 50 to a couple hundred lines. If that's not what you're asking sorry, just let me know. I do need to add the option save the file to a different location if the user does not want to append the new file to the master. I've got to use file dialog and probably a yes no msgbox. Trying to add that in while we figure out the reason the append stopped after adding reccount.

Good luck playing cards.

[SamT]

IIRC, you said that some of the files have more than 1 header line. How can you tell by looking at the file which are header lines?

[andycl]

I guess my interpration of a header line is what is being used as a field name of database table when the file is imported to Access. Correct me if that's off base. I say the files consist of more than 1 header line because if you open the file there are 2 and 3 header lines and the actual data starts after the last header name. That can be mid line of the third line of the file. My definitions could be off and we are talking about different things. But essentially what I am trying to do is when a file is appending to the master file omit the header lines because those header lines are already in the master file. They shouldn't be duplicated.

[andycl]

I said all that and didn't answer your question. I know the file structure for the files that being worked with. The header lines will remain the same each time a file is exported.

[OBP]

Here it is.
Use the top button for the code.

[SamT]

if you open the file there are 2 and 3 header lines and the actual data starts after the last header name

Hmmmmm. What if you open the file in Notepad and turn off Word Wrap?
I'll buy you a cup of coffee the next time we meet if you see more than one header line then.

BTW, your definition is correct.

[andycl]

With word wrap off, the middle of the third line is where the last header name is and the data starts. This particular file has over 200 field names. But it is not one continuous line even with word wrap off. With word wrap on its many more lines. I'm going to put the files up here. Because even with the db OBP posted the append routine does nothing when I added reccount. It doesn't move any of the data to the master file. That makes no sense.

[OBP]

Andy we know from your other code where we split the file into 2 tables that the files use the vblf as it's record seperator for the header, the same as rest of the data, so as the VBA code also uses the vblf to separate the lines there should be no need to skip more than 1 line input.
I have retested my code this morning and it is still working great, doing exactly as expected, I just don't see how skipping the line input for 1 line affects all the rest.
Have you tried adding a msgbox to display what it says it is appending?

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

[OBP]

Update, using your original data file which was for importing 265+ fields I have reproduced your problem, ie it does NOT update when both files have Headers.
I will now try and establish how to overcome the problem.

[OBP]

Sometimes we can be as dumb as hell.
We established in your first post that because of the vblf that your files use the inputline imports the WHOLE file in one record, how could we have forgotten?
So it works for my files because they DO input 1 line at a time and not for your files because they don't, that was why we went to importing the Characters one a time and decide where the Record delimiters were.
So it look like we will have to do the same here.

Although I don't really understand what would be detrimental about having more header records as they can easily be deleted in other programs.

[andycl]

So we need to do Input (1, #1) or something like that.

[OBP]

Yes I will get on it now that I have had Lunch.
I won't bother with other files until I have this working.
The save to another folder could be achieved using the same copy code as now and then "kill" the original file.

This is the sort of code that you need for the decision process, obviously with different wording and actions

Dim response
    response = MsgBox("Are You Sure You Want to Quit Access", vbYesNo + vbExclamation + vbDefaultButton2)
    If response = vbNo Then Exit Sub   ' User chose No.
    
    DoCmd.Quit

[andycl]

Ok good deal. Thanks for help

[SamT]

With word wrap off, the middle of the third line is where the last header name is and the data starts. This particular file has over 200 field names. But it is not one continuous line even with word wrap off. With word wrap on its many more lines. I'm going to put the files up here. Because even with the db OBP posted the append routine does nothing when I added reccount. It doesn't move any of the data to the master file. That makes no sense.

That means that even the Records are probably spread across multiple lines.

I think that the files must be read by field count, rather than by lines.

Something like

'first, Replace linefeeds with separator
TmpArray = Split(FileText, Separator)

For j = 0 to UBound(TmpArray) Step FieldCount
If TmpArray(j) = FirstHeaderName then GoTo J_Next 'Skips Headers
Write(TmpArray(j to j+FieldCount) to TmpFile
Write Linefeed Character toTmpFile
J_Next:
Next

Append TmpFile to Master