[RESOLVED] Add records to existing Csv File..

[frankwhite]

Hi, a problem im facing is that in word using vba I add records in a csv file saved in another location. The records go down, for example,

A1 would be record 1
A2 would be record 2
A3 would be record 3 etc.

The problem is that whenever i run the program again, and insert new records they get saved on top of the old records. what i want is the new records to get saved next to the old one. For example:

B1 would have a new record
B2 would have a new record
B3 would have a new record.

any suggestions please?

[RobDog888]

Using Basic File I/O always writes at the end of the file for new records being added. To get it to add to the top for new records beiing added you need to read the file in completely to an array and then delete or clear the file and write your new records and then write the old array records.

[frankwhite]

lol oh so were would i need to look to get help? are there any tutorials or information to help me with arrays, as i have never done anything like this before.

thanks

[RobDog888]

Well everything is on the forums already but if you need detailed help you just need to ask.

First link has a detailed tutorial on reading the file into an array.
The other tutorial links are great too.
http://vbforums.com/showthread.php?t...ght=File+I%2FO
http://vbforums.com/showthread.php?t...ght=File+I%2FO
http://vbforums.com/showthread.php?t...ght=File+I%2FO

[salvelinus]

Do you mean you want new records added to the end of the existing file, or at the start? When you say "on top of", do you mean they're overwriting the existing records? In that case, Open the file for Append, rather than Output, to add records at the end of the file.
If you want the new ones at the start of the file, you can use Rob's method, or first write the new records to a new file, then read the old file and write the records to the new file.

[frankwhite]

Oh sorry its a misunderstanding, i changed the output to apend, i thought i needed to have output as this creates a file, and thought apend does NOT create a file if there is no file in the directory.

The help i needed was my current records get saved between a1 and a16, and if i run the userform again and save the records again more records get saved between a17 and a32. I do not want this to happen i want the new records to get saved in to the next cell which is b1 and b17, and if i run this again the next save to happen between c1 and c17. I hope you understand now.

[salvelinus]

Oh, ok, you mean when you open the .csv in Excel. In that case, you would have to do something like Rob suggests. A .csv is just a text file. You can open it in Notepad. Excel just happens to open it in cells.

[frankwhite]

Im sorry but if you could give me a quick example id appreciate it, its just I havnt worked with vba much and once I see an example i can take it from there.

So far i know what i want to do is read the file, and add records next to it.

So far i have:

Open "C:\Records.csv" For Append As #1
    Print #1, "1,"; genders
Close #1
Open "C:\Records.csv" For Append As #2
    Print #2, "2,"; ages
Close #2

(i have declared the genders and ages variables at the top)

This adds to the records. From what i can see i think i need to use something like:

Open strSomeFile For Binary As #1
    Dim strBuff As String
    strBuff = Space(Lof(1))
    Get #1, , strBuff
Close #1

Correct?
Any Suggestions?

[RobDog888]

Not sure what else you need but you can do away with the two statements and merge them together.

Open "C:\Records.csv" For Append As #1
    Print #1, "1,"; genders
    Print #1, "2,"; ages
Close #1

[salvelinus]

Not sure what else you need but you can do away with the two statements and merge them together.

Open "C:\Records.csv" For Append As #1
    Print #1, "1,"; genders
    Print #1, "2,"; ages
Close #1

I don't think that's what he's looking for. I think he wants subsequent Appends to append each new record to the end of the row of existing records, so that he never has more than, say, 16 rows. Each row's just getting longer. That way, when he opens the .csv file in Excel, he never goes past row 16, but the number of columns increases. In other words, he doesn't want to Append to the end of the file, but to the end of each row. So, first .csv file would look like:

1,
2,
3,
.
.
16,

Second would look like:

1, 1,
2, 2,
3, 3,
.
.
16, 16,

not:

1,
2,
3,
.
.
16,
1,
2,
3,
.
.
16,

At least that's what I'm understanding. It might be easier to automate this in Excel.

[RobDog888]

Well going off of his code in post #8 I just simplified it. I too am unsure about what he needs.

[frankwhite]

Rob thanks for making my code simpler. salvelinus that is exactly what i am after. What you have said in your post is what i need. any suggestion please?

[salvelinus]

Using a .csv file, you'll have to overwrite the file everytime and re-insert all values. That means you'll have to read the values in existing line one, add your new values to that line, read the next line, add new values to that, etc. It can be done, but is awkward. You may want to look at inserting your values directly into an Excel file - .xls - instead, so you can just add new data more diectly.. Don't know enough Excel coding to give you an example offhand, but shouldn't be too hard.

[frankwhite]

thing is it was part of an assignment and our teacher requested for the answers to be put in to an csv file which i already have done, and be ready to be opened up in excel which already works with the csv file. I shall show it to my teacher but i think the current form of the csv file is fine, and what i wanted to do was just over the top.

Anyway thanx for your help every1.