|
 |  |  |  |  |  |  |
| Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks. |
| |||||||
 |
| |
10-22-2008
| #1 (permalink) |
| | Re: merge CSV to excel On Oct 22, 12:35*pm, "Ammy" <vbsstr...@xxxxxx> wrote: Quote: > 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 : > 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 System Specs |
|
10-22-2008
| #2 (permalink) |
| | Re: merge CSV to excel Tom Lavedas schreef: Quote: > On Oct 22, 12:35 pm, "Ammy" <vbsstr...@xxxxxx> wrote: Quote: >> 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 Quote: > 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 Quote: > .Refresh BackgroundQuery:=False > End With > End Sub > > Tom Lavedas > =========== > http://members.cox.net/tglbatch/wsh/ -- Luuk |
| My System Specs |
|
10-22-2008
| #3 (permalink) |
| | Re: merge CSV to excel Luuk schreef: Quote: > Tom Lavedas schreef: Quote: >> On Oct 22, 12:35 pm, "Ammy" <vbsstr...@xxxxxx> wrote: Quote: >>> 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? > Quote: >> 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 ... ;-) > > Quote: >> .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 System Specs |
|
| Thread Tools | |
| |
Similar Threads | ||||
| Thread | Forum | |||
| mail merge from excel doc | Vista General | |||
| running Excel Macro in in specified Excel files in spe | PowerShell | |||
| create Excel spreadsheet without loading Excel | VB Script | |||
| How to view Excel document without Microsoft Office Excel installe | Vista General | |||
| Vista ASP.NET 2.0 Cannot open Excel File using Microsoft.Excel 12.0 COM object | Microsoft Office | |||
