¦p¦ó¥ÎVBA¶×¤JCSVÀÉ?

am0251

¦³°ª¤â¥i¥H±Ð§Ú¤@¤U¦p¦ó¥ÎVBA¶×¤JCSVÀÉ©O?ÁÙ¥i¥H¦Û¿ï¦C©M¤À¹jªº²Å¸¹,ÁÂÁÂ~~!

jimmyhk

¦^´_ 1# am0251

¬O³o­Ó¶Ü?

1. sub openCSV()
   
2. 
   
3. dim mydir as string
   
4. mydir = thisworkbook.path & "\"
   
5. workbooks.opentext filename:=mydir & 123.txt, datatype:=xldelimited, comma:=true
   
6. 
   
7. end sub

½Æ»s¥N½X

am0251

ÁÂÁ¦^´_,¶}±Ò¬O¨S°ÝÃD,¥i¬O§Ú¥u·Q¶×¤J§Ú·Q­nªº¦æ¼Æ,¤S¸Ó«ç»ò¼g©O?

GBKEE

¦^´_ 3# am0251
¥i¬O§Ú¥u·Q¶×¤J§Ú·Q­nªº¦æ¼Æ  ³o¼Ëªº°Ýªk,«ÜªÅ¬}ªº Ãø©Ç¨S¤H¦^µª
­nªþ¤W¤å¦rÀÉ: »¡©ú­n·j´Mªº¯S©wªº¦r¦ê? ¦p¦ó ¶×¤J? ¦ó®É°±¤î¶×¤J?

luhpro

¦³°ª¤â¥i¥H±Ð§Ú¤@¤U¦p¦ó¥ÎVBA¶×¤JCSVÀÉ©O?ÁÙ¥i¥H¦Û¿ï¦C©M¤À¹jªº²Å¸¹,ÁÂÁÂ~~!
am0251 µoªí©ó 2011-9-3 17:09

¬O©³¤U³o¼Ë¶Ü?

1. Sub nn()
   
2.   Call openCSV("123.txt", 3, 6, " ")
   
3. End Sub

½Æ»s¥N½X

1. Sub openCSV(ByVal sFileName$, lStartLine As Long, lLoadLine As Long, sChar$)
   
2.   ' sFileName : CSVÀɮצWºÙ, lStartLine : ¶}©l¦C¸¹, lLoadLine : Ū¨ú¦C¼Æ, sChar : ¤À¹j¦r¤¸
   
3.   Dim iI%
   
4.   Dim sFullName$, sStr$
   
5.   Dim lJ As Long, lPos As Long
   
6.   Dim vData
   
7.   
   
8.   sFullName = ThisWorkbook.Path & "\" & sFileName
   
9. 
   
10.   If Dir(sFullName) <> "" Then
    
11.     Open sFullName For Input Access Read Shared As #1
    
12.       lJ = 1
    
13.       Do While lJ < lStartLine
    
14.         Line Input #1, sStr
    
15.         lJ = lJ + 1
    
16.       Loop
    
17.       
    
18.       For lJ = 1 To lLoadLine
    
19.         Line Input #1, sStr
    
20.         ReDim vData(0)
    
21.         vData = Split(sStr, sChar)
    
22. ' ÃD¥~¸Ü : Cells(lJ, 1).Resize(, UBound(vData) + 1) = Application.Transpose(vData) ¦¹¦æµ²ªG¤£¥¿½T¤£ª¾¿ù¦b­þ¸Ì, ¥u¦n§ï¦¨¥H¤U³o¼Ëªº°j°é...
    
23.          For iI = 0 To UBound(vData)
    
24.            Cells(lJ, iI + 1) = vData(iI)
    
25.          Next iI
    
26.       Next lJ
    
27.     Close (1)
    
28.   Else
    
29.     MsgBox ("§ä¤£¨ì " & sFileName & " ÀÉ®×")
    
30.   End If
    
31. End Sub

½Æ»s¥N½X

am0251

270710.rar (18.22 KB)

¤U¸ü¦¸¼Æ: 14

2011-9-28 15:02

ÁÂÁ¨â¦ìªº¦^ÂÐ,¥u¬Oluhpro¤j¤jªº¼gªk¦³ÂIÃø,¤pªº¨S¬ÝÀ´¦h¤Ö,¨ä¹ê§Ú¥u¬O·Q"¶×¤J"CSV"ÀÉ,¦Ó¤£¬O"¶}±Ò"¦]¬°§Ú­n¿ï¾Ü"¤À¹j²Å¸¹",¦p"TABÁä" "¤À¸¹" "³rÂI" "ªÅ®æ" µ¥¤èªk,ÁÙ¦³´N¬O¶×¤Jªº¦æ¼Æ,¦p³o­ÓÀÉ®×,§Úªº¤À¹j²Å¸¹¬O "³rÂI" ©M "ªÅ®æ",¶×¤Jªº¦æ¼Æ¥u¬O²Ä2¦æ©M²Ä5¦æ,¸Ó«ç»ò¼g©O?

GBKEE

¦^´_ 6# am0251

1. Sub Ex()
   
2.     Dim ¶×¥XÀÉ As String, ¶×¤JÀÉ As Workbook
   
3.     Set ¶×¤JÀÉ = ActiveWorkbook     'Set ¶×¤JÀÉ = Workbooks("TEST.XLS")
   
4.     ¶×¥XÀÉ = "D:\TEST\270710.CSV"
   
5.     With Workbooks.Open(¶×¥XÀÉ).Sheets(1)
   
6.         .Range("A5").Resize(5).EntireRow.Copy ¶×¤JÀÉ.Sheets(1).[A1]
   
7.         '.Range("A5").Resize(5) ->5¦C  ,  EntireRow ¾ã¦C½d³ò
   
8.           .Parent.Close False    'Ãö³¬ ¶×¥XÀÉ
   
9.     End With
   
10. End Sub

½Æ»s¥N½X

am0251

ÁÂÁ¦^ÂÐ,¥i¯à¬O§Úªí¹Fªº¤£¬O«Ü¦n,³o­ÓÀɮ׬O§Ú¥Î"¸ê®Æ - ±q¤å¦rÀÉ"ªº¤èªk¶×¤Jªº,"SHEET1"¤À¹j²Å¸¹¬O¥Î¤F,"³rÂI" ©M "ªÅ®æ","SHEET2"´N¬O§Ú­nªº¥Øªº¤F,¥u¦³²Ä2©M²Ä5¦æ¶×¤J¤F,§Ú¥u¬O¨S¿ìªk¼g¥X¨Ó........ Inport CSV.rar (122.98 KB)

¤U¸ü¦¸¼Æ: 10

2011-9-29 13:19

GBKEE

¦^´_ 8# am0251

1. Sub Ex()
   
2.     Dim ¶×¥XÀÉ As String, ¶×¤JÀÉ As Workbook
   
3.     Set ¶×¤JÀÉ = ActiveWorkbook
   
4.     'Set ¶×¤JÀÉ = Workbooks("TEST.XLS")
   
5.     ¶×¥XÀÉ = "D:\TEST\270710.CSV"
   
6.     With Workbooks.Open(¶×¥XÀÉ).Sheets(1)
   
7.         ¶×¤JÀÉ.Sheets(1).Range("A:A") = .Range("B:B").Value
   
8.         ¶×¤JÀÉ.Sheets(1).Range("B:B") = .Range("E:E").Value
   
9.         .Parent.Close False    'Ãö³¬ ¶×¥XÀÉ
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

am0251

ÁÂÁ¦^ÂÐ,¥i¬O§Ú¥Î"¿ý»s"ªº¤èªk±o¨ì¤F¤U­±ªº¥¨¶°,¦³¨S¦³¿ìªk²³æªº¤èªk¼g¥X¨Ó©O?

1. Sub csv()
   
2. '
   
3. ' csv Macro
   
4. '
   
5. 
   
6. '
   
7.     With ActiveSheet.QueryTables.ADD(Connection:= _
   
8.         "TEXT;E:\Excel\270710.csv", Destination:=Range("$A$1"))
   
9.         .Name = "270710"
   
10.         .FieldNames = True
    
11.         .RowNumbers = False
    
12.         .FillAdjacentFormulas = False
    
13.         .PreserveFormatting = True
    
14.         .RefreshOnFileOpen = False
    
15.         .RefreshStyle = xlInsertDeleteCells
    
16.         .SavePassword = False
    
17.         .SaveData = True
    
18.         .AdjustColumnWidth = True
    
19.         .RefreshPeriod = 0
    
20.         .TextFilePromptOnRefresh = False
    
21.         .TextFilePlatform = 950
    
22.         .TextFileStartRow = 1
    
23.         .TextFileParseType = xlDelimited
    
24.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
    
25.         .TextFileConsecutiveDelimiter = True
    
26.         .TextFileTabDelimiter = True
    
27.         .TextFileSemicolonDelimiter = False
    
28.         .TextFileCommaDelimiter = True
    
29.         .TextFileSpaceDelimiter = True
    
30.         .TextFileColumnDataTypes = Array(1, 1, 9, 9, 1, 1, 1)
    
31.         .TextFileTrailingMinusNumbers = True
    
32.         .Refresh BackgroundQuery:=False
    
33.     End With
    
34. End Sub

½Æ»s¥N½X