replace character in txt file before import to excel
 

Hi all,

Background
I have a vba rutine that imports a csv file to excel.
The source application where the data is taken from sometimes contain a
semicolon sign ";" with a text field. The semicolon will cause an offset
of the data in excel when the user regional settings is using semicolon
as a delimiter.

Question
is it possible to use some vba code that will replace ";" with for
example "|" before the import of the file to excel is done?

Any ideas are warmly welcome
- Chr

replace character in txt file before import to excel
 

You can only remove the unwanted semicolons if you know which ones are the
good ones and which are the bad ones. You have to first look at the text
file and identify why the bad ones are in the file. Only then can you filter
out the bad ones.

"Christian" wrote:

Hi all,

Background
I have a vba rutine that imports a csv file to excel.
The source application where the data is taken from sometimes contain a
semicolon sign ";" with a text field. The semicolon will cause an offset
of the data in excel when the user regional settings is using semicolon
as a delimiter.

Question
is it possible to use some vba code that will replace ";" with for
example "|" before the import of the file to excel is done?

Any ideas are warmly welcome
- Chr

replace character in txt file before import to excel
 

Hi Joel,

Sure. For this particular project all ";" are unwanted. Actual you just
made me think! Maybe the actual import goes ok as the "," is used as
delimiter, maybe the ";" is causing problems further down in one of the
many other sub rutines I execute when processing the file. Have to check
that.

Thanks for pointing out the obvious - it's needed sometimes :-)

Btw if I find that the ";" has to go is there a way i can get excel to
do search&replace in the file without importing it first? At the moment
the user is opening the file in notepad and does the search replace
first before using the file with excel.
- Chr

Joel wrote:
You can only remove the unwanted semicolons if you know which ones are the
good ones and which are the bad ones. You have to first look at the text
file and identify why the bad ones are in the file. Only then can you filter
out the bad ones.

"Christian" wrote:

Hi all,

Background
I have a vba rutine that imports a csv file to excel.
The source application where the data is taken from sometimes contain a
semicolon sign ";" with a text field. The semicolon will cause an offset
of the data in excel when the user regional settings is using semicolon
as a delimiter.

Question
is it possible to use some vba code that will replace ";" with for
example "|" before the import of the file to excel is done?

Any ideas are warmly welcome
- Chr