If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[RESOLVED] Import CSV into existing Excel Worksheet with VBA

I should know how to do this but haven't played in Excel for some time. I'm trying to open a CSV file and copy the contents into a worksheet in my current workbook. I'm having two problems.

The first is the copy and paste. I have two methods that work (kind of). I can write each cell one at a time but this is very slow. I can also do a copy and paste but I'm having to hard code the range. I've found the last column but not sure how to use that number in the range. Also when the code tries to close the CSV file after the copy, I get a prompt because of the data stored on the clipboard.

The second problem is reading in multple CSV files. The code below "works" for one file but throws an error if I try to open a second file. It throws the error on the line that tries to open the file. (run-time error '1004' Application-defined or object-defined error)

I'm sure that I'm over complicating this but can anyone point me in the right direction?

Re: Import CSV into existing Excel Worksheet with VBA

Never mind that on the file open problem. It helps if I use the filename that actually exists. Looks like I need a little error checking before release. Don't want an error if the user types the wrong file name.