Importing csv file data in Excel with VBA

amisuraca - Aug 30, 2009 at 01:50 PM
sadgurl Posts 697 Registration date Thursday May 14, 2009 Status Member Last seen November 24, 2010 - Aug 31, 2009 at 08:25 AM

I am builiding a spreadsheet that keeps track of my monthly business expenses. I use a small applet on my blackberry that allows me to enter each expense I incure on a daily basis. At the end of the month I can export this expense data from my blackberry applet to my computer as a csv file. So each one of my daily expense entries are seperated by a comma. Simple enought right?

Here is where it get's a bit interesting. Because I never know exactly how many expenses I am going to have in a given month, I don't know how many rows in my spreadsheet to create to handle each expense. Some months I may have 30 and some months I may have 100 individual expense transactions.

My spreadsheet has one row with the calculation formula needed for each expense. How do I copy that row with the formula and insert a new row right under the first row with the same formula for the exact number of records that I have in my csv file data that are individually seperated by commas.

I know this is a simple task for most of you guys but I am new to VBA.

I know that I am going to somehow have to define a dynamic range. I have been looking at the Querytable function and the add method. It seems to me that I need to import this data into my spreadsheet using some type of loop, looping for each record of data in the csv file. That way when I come to the end of my data in the csv file, I will then know when to stop inserting new rows for each new expense record.

I hope I have given enough information for your suggestions. Thank you all so much in advance for your help with this project of mine.


2 replies

sadgurl Posts 697 Registration date Thursday May 14, 2009 Status Member Last seen November 24, 2010 219
Aug 30, 2009 at 03:55 PM
Not sure I properly understand what you are trying to do, but the simplest way according to me is to built a vb code that read the last line, copy it and then paste it in last line +1

to find the last line, just read a colum that contain the data, loop until line content is null, this will allow you to find the last used row

last row will be empty, copy previous row content, and paste it in last row

that is for the excel sheet, but for the csv, its only database organisation
amisuraca Posts 1 Registration date Saturday August 29, 2009 Status Member Last seen August 30, 2009
Aug 30, 2009 at 05:41 PM

Thanks for your response! I guess I was a bit vaige about what my real challenge is here. When I import data from a csv file it is my understanding that all of the data comes in with one Querytable call. Is this right? If indeed this is right then how do I use any sort of looping structure to both insert rows and discriminate when I have read the last record of my data? It seems to me that all of the data just come flooding in and I cannot read the data from a csv file from one comma to the next comma inside of a loop. I hope this clears up my question. Thanks again in advance for everyones help.

sadgurl Posts 697 Registration date Thursday May 14, 2009 Status Member Last seen November 24, 2010 219
Aug 31, 2009 at 08:25 AM
Can you post a sample of you Csv file? I guess it would make it easier to explain with it

if I know how are your table organised I maybe can suggest a little something, excel data can be extracted from mainly 3 ways, using cell, rows, and column location, your excel sheet is similar to a large X,Y coordinate graph, cells, rows and column are define with numbers, when you loop just increment the number used as reference