MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Loading CSV files into Worksheets


Posted by Ron Dionne on October 15, 2001 1:38 PM

Hi,
I'm opening a series of .CSV files into Excel in order
to do some statistical analysis. I'm using the
Workbooks.Open function to do this. Unfortunately,
This creates a seperate Workbook for each file, so I
end up accessing the data like this:

Workbooks(i).Worksheets(j).Cells(l,m)

Since I'm using automation from VB, this process
is very slow. If could instead load the data into
Worksheets under one Workbook, I would think that
it would speed things up a bit since I would be
getting rid of one level of object hierarchy.
I could access the data like this:

Worksheets(j).Cells(l,m) and not need the reference
to the seperate workbooks.

Oh Excel guru, please show me the way.
Thanks


Posted by Jonathan on October 15, 2001 2:57 PM

Workbooks.Open("Filename.xls")

Posted by Ron Dionne on October 22, 2001 3:29 PM

Geeeez, I already knew that ...

As my message stated, I'm already using this to
open the files, I just wanted to know how to put
all the files into ONE workbook. I figured out
how to do this already with this code:

Set newWB = Workbooks.Add

For i = newWB.Worksheets.Count To 2 Step -1
newWB.Worksheets(i).Delete
Next

For i = 1 To UBound(strFilenames)
Set tmpWB = Workbooks.Open(strFilenames(i))
tmpWB.Worksheets(1).Copy after:=newWB.Worksheets(i)
tmpWB.Close
Next

newWB.Worksheets("Sheet1").Delete

(I also remove the extra sheets).

RD