Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing csv by code
Hi there,
i wrote some vba code to open a csv file within Excel 2000. Since the separator is ';' and not ',' i wrote: Set wb = Workbooks.Open(FILENAME:=filename, ReadOnly:=True, Format:=4) But the file is not imported correctly. BTW: if i double-click on the file, Excel are not able to import it but if I open the file within Excel, the file are ok. Any idea? -- SteM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing csv by code
First, I'd rename the .csv file to .txt. .CSV file means something special to
excel and you'll have trouble in your code if you leave the file named .csv. Then instead of writing the code yourself, try recording a macro when you open the .txt file manually. You'll be able to specify the separator you want. SteM wrote: Hi there, i wrote some vba code to open a csv file within Excel 2000. Since the separator is ';' and not ',' i wrote: Set wb = Workbooks.Open(FILENAME:=filename, ReadOnly:=True, Format:=4) But the file is not imported correctly. BTW: if i double-click on the file, Excel are not able to import it but if I open the file within Excel, the file are ok. Any idea? -- SteM -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing csv by code
Try this code. Change Default folder and delimiter.
Sub GetCSVData() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Const Delimiter = "," Set fsread = CreateObject("Scripting.FileSystemObject") 'default folder Folder = "C:\temp\test" ChDir (Folder) FName = Application.GetOpenFilename("CSV (*.csv),*.csv") RowCount = LastRow + 1 If FName < "" Then 'open files Set fread = fsread.GetFile(FName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine 'extract comma seperated data ColumnCount = 1 Do While InputLine < "" DelimiterPosition = InStr(InputLine, Delimiter) If DelimiterPosition 0 Then Data = Trim(Left(InputLine, DelimiterPosition - 1)) InputLine = Mid(InputLine, DelimiterPosition + 1) Else Data = Trim(InputLine) InputLine = "" End If Cells(RowCount, ColumnCount) = Data ColumnCount = ColumnCount + 1 Loop RowCount = RowCount + 1 Loop tsread.Close End If End Sub "SteM" wrote: Hi there, i wrote some vba code to open a csv file within Excel 2000. Since the separator is ';' and not ',' i wrote: Set wb = Workbooks.Open(FILENAME:=filename, ReadOnly:=True, Format:=4) But the file is not imported correctly. BTW: if i double-click on the file, Excel are not able to import it but if I open the file within Excel, the file are ok. Any idea? -- SteM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Alan Beban's code on Arrays; Importing a module or a project | Excel Worksheet Functions | |||
help: code for importing xml into excel sheet | Excel Programming | |||
Importing txt into XL as Delimited, what's wrong w my code? | Excel Programming | |||
importing worksheet via code | Excel Programming | |||
Importing Code into 'ThisWorkbook' | Excel Programming |