Import External Data w/ CSV & Line-Breaks ...
 

Hello -

I need to load data from a CSV file that contains line-breaks in some
entries. The file looks like

"DATA-A", "A"
"DATA-B", "B
C
D"
"DATA-E", "E"

when I open it in an ASCII editor. The line-breaks are Chr(10) and if
I open the file in Excel it puts the B C D in one cell with three
lines.

My problem is that I want to load this data with the Data Import
External Data Import Data... function so that I can easily update it.
I tried that but Excel will interpret the Chr(10) as a new line and
the imported data will look like it does in the ASCII editor.

I guess I could write some VB code and link that to a button to do the
loading and correct interpretation of that data, but I think it would
be easier to just use Excel's existing functionality if it lets me do
that.

I would appreciate any suggestions!

Thanks,
Joe

Import External Data w/ CSV & Line-Breaks ...
 

How does Data=Import External Data = Import Data
allow for easy updating over just opening the file in Excel (which
apparently does what you want).

I have never seen creating a query table as a two way link? Is that an
option?

--
Regards,
Tom Ogilvy

"Joe HM" wrote in message
oups.com...
Hello -

I need to load data from a CSV file that contains line-breaks in some
entries. The file looks like

"DATA-A", "A"
"DATA-B", "B
C
D"
"DATA-E", "E"

when I open it in an ASCII editor. The line-breaks are Chr(10) and if
I open the file in Excel it puts the B C D in one cell with three
lines.

My problem is that I want to load this data with the Data Import
External Data Import Data... function so that I can easily update it.
I tried that but Excel will interpret the Chr(10) as a new line and
the imported data will look like it does in the ASCII editor.

I guess I could write some VB code and link that to a button to do the
loading and correct interpretation of that data, but I think it would
be easier to just use Excel's existing functionality if it lets me do
that.

I would appreciate any suggestions!

Thanks,
Joe

Import External Data w/ CSV & Line-Breaks ...
 

Hello -

I really haven't played that much with the Import External Data
function. I just thought that once I had it set up, I could just
re-import the data with one of the Data Refresh Data function.

I want the data to be in a specific column of an existing worksheet.
Maybe I could just load it in as some temporary sheet and copy it over?

How can I use the Open() or OpenText() function to load a CSV file into
an existing worksheet without opening a new workbook?

Thanks,
Joe

Import External Data w/ CSV & Line-Breaks ...
 

You can't. but you can write code to do it.

set sh = Activesheet
application.ScreenUpdating = False
workbooks.Open filename ' or OpenText
activeworkbook.Range("A1").CurrentRegion.copy _
Destination:= sh.Range("F1"
Activeworkbook.close Savechanges:=False
application.ScreenUpdating = true

--
Regards,
Tom Ogilvy


"Joe HM" wrote in message
ups.com...
Hello -

I really haven't played that much with the Import External Data
function. I just thought that once I had it set up, I could just
re-import the data with one of the Data Refresh Data function.

I want the data to be in a specific column of an existing worksheet.
Maybe I could just load it in as some temporary sheet and copy it over?

How can I use the Open() or OpenText() function to load a CSV file into
an existing worksheet without opening a new workbook?

Thanks,
Joe

Import External Data w/ CSV & Line-Breaks ...
 

Great ... thanks!

The only thing I had to add was to insert a Sheets(1) after the
activeworkbook and before the .Range("A1")

Thanks again,
Joe