File Dialog-Browse/Save/Append

[andycl]

The commas will align the data in the approprioate fields for the import.

[OBP]

Andy, how about
datastring = ",,,,,Data added " * Date()
that will tell anyone looking at the data when each append was made.

[OBP]

I am not sure what this means
"The commas will align the data in the approprioate fields for the import."



Do you mean importing in to Access or Excel?

[andycl]

Sorry for the confusion. Yeah after all the moving and copying files, it's time to import the master file to Access. This is where we both were saying Access should have been used to let Access handle the importing/appending for each file type.

By addidng the 3 commas, it separated the first field of the appended data to the correct field of the table and set all subsequent fields correctly.

[OBP]

So, can you explain the sequence of events?
Copy file or append to Master
Import master to Access
Copy fille or append to Master
Import master to Access again?

[andycl]

Evidently all of the copy file and append file will have to happen prior to importing to Access. After the master is imported to Access, no more appending will occur. So I guess if something is missed to bad so sad. You'll have to start another DB for any other data. Not my show.

So the header rows of the files are used as field names for the table when they are imported to access. When you append a file, it appends everything obviously including the header rows. Creating a file that header row of the intial master file a. The file is structured as header rows, all the data, then after an append, header rows, and all the data. When I import the appended master file, Access will import to the last data set before the header rows are repeated. I've got to omit the header rows when appending to the master file. Didn't think that would happen because when I was importing a file at a time that did not occur.

[OBP]

You could allow the Header rows to be inported and then use a Delete query to remove them.
I think you can specify whether or not a file has header rows or not.
But I also have a VBA import routine that could probably be modified to ignore them during import.
Are you using VBA, docmdimport or a Data Import to bring in the data to Access?

[andycl]

If that was confusing, i'm trying to say omit the header rows of an append file to the master. The master already has the header rows.

[OBP]

OK, of course that is possible, the code that I provided on your previous post skips the first line of code to avoid the headings.
Have you forgotten it already?

ps going out for a while so won't be able to answer until I get back.

[andycl]

We've had a few post going back and forth so cut me some slack. jk
I'll go back and look. Is it just to remove the first line? Because they are all different. Some 2 lines, some 3, and some not the complete line and the data set begins.

[andycl]

You're referring to the import and not omitting for an append, correct?

[OBP]

It was the more than 265 fields question, that was you wasn't it?
It was this line of code
If reccount > 1 Then ' process if not heading record
where we were using reccount, in this one we aren't currently using a counter be we could.
Or as I said you can do it in the import routine or even after the data has been imported by deleting any offending records.

[OBP]

Hey what do mean cut you some slack, I am the one who is 70 with fading brain cells.

[SamT]

My brain hurts

[andycl]

I was joking around anyways. 70, I would have never know.

Just so I'm putting the problem statement out there. Master file is in the directory and it has it's header rows for the file. A second file is appended to master file and it also brings the header rows with it. When trying to import using the routine you are referring to, it imports and stops where the appended file header row begins. So the newly appended
master file looks like this: Header rows - initial master file data - Header rows (from appending file) - data (from appending file).

I can see how using what was earlier used to overlook the headers but not sure how to go about overlooking both sets of headers rows as they will be downstream in the file and not the first rows of the file. And the second set of headers will be located differently for each of the five file types. Thats why I was hoping to omit the header rows from the append file in during that routine.

I know this is all crazy.

[OBP]

Sam, I am currently working on three separate databases, it keeps the old brain cells ticking over, I also post on Tech Guys,where the other 2 are, which used to be a sister forum to VBAX, I have 19000 posts over there.

[OBP]

The routine that I wrote for your previous post ommits the header rows during import and the same method can easily be used to ommit the header during the Append operation.
The only problem is if it is over more than one input "line", unless you know it in advance if it then you can ommit as many rows as necessary.

[OBP]

Andy, this should do it
Dim sDest As String, firstfile As String, filecopied As Integer, reccount As Integer
and

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
    End If
reccount = reccount + 1
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

This line could be
If reccount > 1 Then
If reccount >2 Then
or
If reccount > 3 Then

[SamT]

This is an interesting, if easy, problem and I would like to work on it, but I am not going to read the previous 97 posts to figure out the data structure of Andy's situation.

I don't even know if my speculation in that regard, in post #60, was accurate.

Don't let your chest swell too much, I am 67 and this is my hobby too, also to keep my b.b.b.brain active when it is too hot outside to build stuff. If you go back far enough, you can find my name on a few other forums, even had my own, but it was getting too much like work, so I killed mine and dropped out of the rest and only work on the extremely easy and the most interesting projects here.

[andycl]

So do the other lines need to be added? I added what OBP posted and the data append actually stopped working. It ran the procedure but didn't append any data.

SamT your insight and comments are appreciated as well.