• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Re: merge CSV to excel

T

Tom Lavedas

#1
On Oct 22, 12:35 pm, "Ammy" <vbsstr...@xxxxxx> wrote:

> Can anybody help me withe this script.
> I want comma seperated files (; ) import it to excel.
>
> The script is ok but the data could not split in the rows, i must change
> that in excel.
> Like Extra >>Text to coloms.
>
> Thanks,
>
> Ammy
>
> filename001.csv
> -----------------
> data;data;data;data;data;data
> data;data;data;data;data;data
> data;data;data;data;data;data
>
> Here is the script :
>
Since your file is NOT comma delimited, their are only two things I
can think to do; change the delimiter in the file to commas or use an
Excel data import in place of the open. Here is a macro I recorded in
Excel, with the extra bits stripped out and some other changes to make
it VBS compatible. Its up to you to install in in your script ...

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/22/2008 by Tom Lavedas
'
sFileSpec = "C:\Someplaceuseful\test.csv"
With ActiveSheet.QueryTables.Add("TEXT;" & sFileSpec, Range("A1"))
.AdjustColumnWidth = True
.TextFileStartRow = 1
.TextFileParseType = 1 ' xlDelimited
.TextFileSemicolonDelimiter = True
.Refresh BackgroundQuery:=False
End With
End Sub

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/
 

My Computer

L
#2
Tom Lavedas schreef:

> On Oct 22, 12:35 pm, "Ammy" <vbsstr...@xxxxxx> wrote:

>> Can anybody help me withe this script.
>> I want comma seperated files (; ) import it to excel.
>>
>> The script is ok but the data could not split in the rows, i must change
>> that in excel.
>> Like Extra >>Text to coloms.
>>
>> Thanks,
>>
>> Ammy
>>
>> filename001.csv
>> -----------------
>> data;data;data;data;data;data
>> data;data;data;data;data;data
>> data;data;data;data;data;data
>>
>> Here is the script :
>>
>
> Since your file is NOT comma delimited, their are only two things I
CSV is the commonly known name for comma delimited, or not?

> can think to do; change the delimiter in the file to commas or use an
> Excel data import in place of the open. Here is a macro I recorded in
> Excel, with the extra bits stripped out and some other changes to make
> it VBS compatible. Its up to you to install in in your script ...
>
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 10/22/2008 by Tom Lavedas
> '
> sFileSpec = "C:\Someplaceuseful\test.csv"
> With ActiveSheet.QueryTables.Add("TEXT;" & sFileSpec, Range("A1"))
> .AdjustColumnWidth = True
> .TextFileStartRow = 1
> .TextFileParseType = 1 ' xlDelimited
> .TextFileSemicolonDelimiter = True
the line above makes it semicolondelimited ... ;-)


> .Refresh BackgroundQuery:=False
> End With
> End Sub
>
> Tom Lavedas
> ===========
> http://members.cox.net/tglbatch/wsh/


--
Luuk
 

My Computer

L
#3
Luuk schreef:

> Tom Lavedas schreef:

>> On Oct 22, 12:35 pm, "Ammy" <vbsstr...@xxxxxx> wrote:

>>> Can anybody help me withe this script.
>>> I want comma seperated files (; ) import it to excel.
>>>
>>> The script is ok but the data could not split in the rows, i must change
>>> that in excel.
>>> Like Extra >>Text to coloms.
>>>
>>> Thanks,
>>>
>>> Ammy
>>>
>>> filename001.csv
>>> -----------------
>>> data;data;data;data;data;data
>>> data;data;data;data;data;data
>>> data;data;data;data;data;data
>>>
>>> Here is the script :
>>>
>>
>> Since your file is NOT comma delimited, their are only two things I
>
> CSV is the commonly known name for comma delimited, or not?
>

>> can think to do; change the delimiter in the file to commas or use an
>> Excel data import in place of the open. Here is a macro I recorded in
>> Excel, with the extra bits stripped out and some other changes to make
>> it VBS compatible. Its up to you to install in in your script ...
>>
>> Sub Macro2()
>> '
>> ' Macro2 Macro
>> ' Macro recorded 10/22/2008 by Tom Lavedas
>> '
>> sFileSpec = "C:\Someplaceuseful\test.csv"
>> With ActiveSheet.QueryTables.Add("TEXT;" & sFileSpec, Range("A1"))
>> .AdjustColumnWidth = True
>> .TextFileStartRow = 1
>> .TextFileParseType = 1 ' xlDelimited
>> .TextFileSemicolonDelimiter = True
>
> the line above makes it semicolondelimited ... ;-)
>
>

>> .Refresh BackgroundQuery:=False
>> End With
>> End Sub
>>
>> Tom Lavedas
>> ===========
>> http://members.cox.net/tglbatch/wsh/
>
>
>

oh, i forgot to mention that ist quit common in the Netherlands to have
semicolon separated files because we do everything with a decimal comma

so 2/3 is not 0.6666667 but is 0,6666667

--
Luuk
 

My Computer

Users Who Are Viewing This Thread (Users: 1, Guests: 0)