Hi Steve
Here is a way to do it using pure VBA (This is a highly simplified version) :-
Sub ReadCSVfile()
Dim nRow As Long, nColumn As Long
Dim vLineInput As Variant
Dim vSplitLineInput As Variant
Const COMMA = ","
Const CSVFILE = "D:\Wouxun\2010 05 11.csv"
Open CSVFILE For Input As 1
While Not EOF(1)
Line Input #1, vLineInput
vSplitLineInput = Split(vLineInput, COMMA)
nRow = nRow + 1
For nColumn = 0 To UBound(vSplitLineInput)
Cells(nRow, nColumn + 1).Value = vSplitLineInput(nColumn)
Next nColumn
Wend
Close 1
End Sub
This methodology has the advantage that you have control over each field and can validate and modify the data as you read it.
I could write a long essay on what can go wrong with CSV file uploads. This applies to all methods, not just the above. Here is a a short version.
1. Delimiter is not comma but is semicolon because user PC regional setting northern European.
2. End of line delimiter is not vbCrLf but is LfCr (Unix) or just vbLF (Mac-User saved from Excel as Mac file ) or just vbCr
3. Comma embedded in text field in file, hence wrong number of fields.
4. Comma is used as decimal separator in numeric fields. (regional settings issue)
5. Accounting format negative numbers.
6. Engineering format numbers.
7. Text contains single quotes e.g. O'Sullivan
8. Double quotes may interfere
9. HTML markup tags in text
10. ASCII character 160 confuses (space character from web)
11. Wrong underline or minus sign characters - ASCII 150,151,
12 'Clever' single quotes from Word. - ASCII 96
13. Any non-printing ASCII character (0-31)
14. Embedded Cr or Lf in text fields splits lines
15 Unicode characters
16. Excel saves CSV as a rectangle of data, even when rows do not always have the same number of fields.
17. Any of the above as a result of user editing. Do not underestimate the users' ingenouity.
And many more . . . I have seen them all in my career. I did not mention date/time issues, that is a minefield.
If you are writing for an international audience you must pay particular attention to Regional Settings issues. The 'Quiet Life' principle applies.
Regards
Derek +++
Apologies if my post contains A with circumflex. It's a Yahoo bug. . . Yahoo Groups does not like multiple spaces or indents.
>________________________________
> From: David Grugeon <yahoo@grugeon.com.au>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 6 June 2012, 12:16
>Subject: RE: [ExcelVBA] Opening a CSV file and populating cells just like Excel does
>
>
>
>Hi Steve
>
>Try Data/Get external data/From Text. (that is based on Excel 2010 - can't
>recall how to do it in obsolete versions.)
>
>If this does what you want, record a macro of doing it.
>
>Mine recorded this
>
>Sub Macro1()
>'
>' Macro1 Macro
>'
>
>'
>With ActiveSheet.QueryTables.Add(Connection:= _
>"TEXT;C:\Users\David\Documents\Business\Clients\Etheridge\Staff
>List.csv", _
>Destination:=Range("$A$1"))
>.Name = "Staff List"
>.FieldNames = True
>.RowNumbers = False
>.FillAdjacentFormulas = False
>.PreserveFormatting = True
>.RefreshOnFileOpen = False
>.RefreshStyle = xlInsertDeleteCells
>.SavePassword = False
>.SaveData = True
>.AdjustColumnWidth = True
>.RefreshPeriod = 0
>.TextFilePromptOnRefresh = False
>.TextFilePlatform = 850
>.TextFileStartRow = 1
>.TextFileParseType = xlDelimited
>.TextFileTextQualifier = xlTextQualifierDoubleQuote
>.TextFileConsecutiveDelimiter = False
>.TextFileTabDelimiter = False
>.TextFileSemicolonDelimiter = False
>.TextFileCommaDelimiter = True
>.TextFileSpaceDelimiter = False
>.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
>.TextFileTrailingMinusNumbers = True
>.Refresh BackgroundQuery:=False
>End With
>End Sub
>
>Best Regards
>David Grugeon
>Excel VBA Group Moderator
>
>-----Original Message-----
>From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
>Of noskosteve
>Sent: Wednesday, 6 June 2012 1:48 PM
>To: ExcelVBA@yahoogroups.com
>Subject: [ExcelVBA] Opening a CSV file and populating cells just like Excel
>does
>
>I wanna' be just like my daddy...
>
>I want to open a CSV file into an already open Sheet the same way Excel
>opens it as a new, complete Workbook.
>
>It is a simple CSV file with records (lines) that have either text or
>numbers in the fields (with commas between, of course) and CR at the end.
>There are no quotes around the strings (they aren't Write strings).
>Typical records:
>2,Anthem,442.525000,,5.000000,Tone,114.8,,,,,,,
>3,Bank One,146.820000,-,0.600000,Tone,159.8,,,,,,,
>
>As you see, there can even be blank fields, thus blank cells.
>
>I already can and do open tab delimited files, but I use Input # and
>normally get the individual fields into individual variables by manually
>(code) parsing the resulting whole record (line) strings.
>
>However, I want to open the file at the users command (a button push) and
>have the values put into adjacent cells just the way it happens when you
>open a CSV file using Excel's Open. [Recording this operation shows that it
>simply opens another complete Workbook.]
>
>Help says that Line Input # is for reading Print # lines.
>and that
>Input # is for reading Write # lines...
>but that the fields are brought into individual variables.
>
>I don't know if this can be done automatically, or if I must bite the bullet
>and use code to parse the values and put them in cells.
>
>I didn't know where to start looking.
>I spoze' I could do a regular Open a CSV Workbook, copy to the desired
>Sheet, then close the CSV Workbook, but it would seem since Excel does it,
>VBA should be able to make that happen as well.
>Ya' know . something like "Application.OpenCSVFile = xlJustLikeExcel
>Filename = "whatever" Destination=Sheet4"
>(;-)
>--
>73, Steve
>
>------------------------------------
>
>----------------------------------
>Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
>here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
>ARCHIVES for EXCEL VBA.
>
>----------------------------------
>Visit our ExcelVBA group home page for more info and support files:
>http://groups.yahoo.com/group/ExcelVBA
>
>----------------------------------
>More free tutorials and resources available at:
>http://www.mousetrax.com
>
>----------------------------------Yahoo! Groups Links
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar