Record a macro when you import the data into a separate workbook/worksheet.
Select the usedrange and edit|copy
then go to that new workbook/worksheet and edit|paste special|values
then back to close that other imported text workbook.

You should be able to do this in code:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim TextFileName As String
Dim TextWks As Worksheet
Dim TemplateName As String

Application.ScreenUpdating = False

TemplateName = "c:\my documents\excel\book1.xls"
TextFileName = "c:\my documents\excel\spacedelim.txt"

Set newWks = Workbooks.Add(template:=TemplateName).Worksheets(1 )

Workbooks.OpenText Filename:=TextFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1))

Set TextWks = ActiveSheet

TextWks.UsedRange.Copy
newWks.Range("a2").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
TextWks.Parent.Close savechanges:=False

Application.ScreenUpdating = True

End Sub

Alternatively, try Data|Import external data (this is the caption in xl2002.
Earlier versions used something else.)

Create a new workbook based on your template
then record a macro when you do the Data|import.



Bing wrote:

Hi Dave,

Thanks for responding.

I have to be able to programmatically do this... ie. no manual intervention,
so not cutting/pasting/etc.

I am using the Workbooks.Add (template) to open the required workbook. Now
i'm trying to AUTOMATICALLY LOAD the data into that workbook. I can't seem
to find a built-in excel function to do this - ie. load the data into the
specified workbook.

The opentext method is great, but you CAN'T specify a workbook template.
OpenText seems to open an blank workbook and loads the data there. Unless i
am mistaken?

"Dave Peterson" wrote:

Have you thought of using workbooks.add to add the workbook based on that
template.

Then open your text file (rename your .csv file to .txt so that you can specify
each column's format), then just copy|paste special|Values into your worksheet
in that new workbook.

You could even do Data|import external data into your workbook (that was based
on the template).

===
If you're data is pretty large, I find that excel's built in OpenText much
faster.


Bing wrote:

Hi,

I asked this question early but need some clarification. Specifically i'm
trying to programmaticially load CSV data into a specific template-based
workbook without any user interaction.

The opentext method does a good job of this but doesn't have any
functionality to specific a specific workbook to load the data in.

With the workbooks.add method i can specify a specific workbook template,
and then i can execute a macro in that template that will load the data by
opening the csv file and reading line by line, splitting the line on "," and
then output each field into individual cells on a row.

I have some concerns with the performance of this methodology compared with
excel's opentext method. However the OPENTEXT method is probably doing the
same thing that i'm doing so performance wise is it pretty much more the same?

Alternatively i can read in a line, output entire line into 1 cell, then
call range.TextToColumns method but that seems even worse performance wise
because i need to do an extra step to write the line to the cell.

Does anyone have a smarter way programmatically achieving the same function
as OPENTEXT, BUT having the data loaded into a specific workbook template?

Thanks again!
Bing

--

Dave Peterson


--

Dave Peterson