Excel 2007 VBAŪTXTÀɨÃÂà¸m
- ©«¤l
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
Excel 2007 VBAŪTXTÀɨÃÂà¸m
¦bc:\file\¤U¦³1.txt,2.txt....¤£¤@©w¦³´XÓÀÉ®×,ÀɦW¬°¼Æ¦r»¼¼W. ¨CÓÀɮ׮榡³£¬Û¦P,¦p¤U:
¨Ò¦p: 1.txt
¦W¦r ¤ýxx
¼Æ¾Ç 58
^¤å 63
¦a²z 90
¤Æ¾Ç 80
2.txt
¦W¦r ªLx
¼Æ¾Ç 100
^¤å 6
¦a²z 60
¤Æ¾Ç 58
txt¤¤©T©w³£¬O5 rows, 2 columns, column¶¡¥ÎªÅ®æ®æ¶}, n¥Î°j°é±Nc:\file\¤Uªº50ÓÀÉ®×Ū¤JExcel,¨ÃÂà¸m¦¨¤U,row 1©T©w¬OÄæ¦ì¦WºÙ, row 2¶}©l¤À§OŪ¤J©Ò¦³ÀÉÂà¸m, ¦ý¥uŪ¨ì¦a²z, ¤£Åª¤J¤Æ¾Ç¨º¤@row¸ê®Æ.
¦W¦r ¼Æ¾Ç ^¤å ¦a²z
¤ýxx 58 63 90
ªLx 100 6 60
.
.
.
¨ì³Ì«átxt¨ºµ§
³Ì«á¦s¦¨c:\file\final.xls
·PÁÂ! |
|
|
|
|
|
|
- ©«¤l
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
stillfish00±z¦n, Y¸ê®Æ§ïµ{¦p¤U:
1.txt
Àç·~¤H²Î¤@½s¸¹ 11111111
t³d¤H©m¦W ³\xx
Àç·~¤H¦WºÙ xx¥ø·~ªÀ
Àç·~¡]µ|Äy¡^µn°O¦a§} xx¥«xx°Ïxx¨½xx¸ôxx«Ñxx¸¹x¼Ó
¸ê¥»ÃB(¤¸) 100000
²Õ´ºØÃþ ¿W¸ê( 6 )
³]¥ß¤é´Á 1020723
µn°OÀç·~¶µ¥Ø ´¶³qÜÀx¸gÀç( 530100 )
²î¤W³fª«¸Ë¨ø( 525912 )
¥]¸Ë©ÓÅóªA°È( 820914 )
2.txt
Àç·~¤H²Î¤@½s¸¹ 01111111
t³d¤H©m¦W ¶Àxx
Àç·~¤H¦WºÙ xx¥ø·~ªÀ
Àç·~¡]µ|Äy¡^µn°O¦a§} xx¥«xx°Ïxx¨½xx¸ôxx«Ñxx¡Ðx¸¹x¼Ó
¸ê¥»ÃB(¤¸) 60000
²Õ´ºØÃþ ¿W¸ê( 6 )
³]¥ß¤é´Á 0780522
µn°OÀç·~¶µ¥Ø ¨ä¥Lª÷ÄݼҨã»s³y( 251299 )
.
.
.
®Ú¾Ú±zªºµ{¦¡×§ï¦p¤U:
Sub importtxt()
Dim path, folder, fname
With Workbooks.Add '¼ÐÃD¦C
.Sheets(1).Range("A1:H1") = Array("Àç·~¤H²Î¤@½s¸¹", "t³d¤H©m¦W", "Àç·~¤H¦WºÙ", "Àç·~¡]µ|Äy¡^µn°O¦a§}", "¸ê¥»ÃB(¤¸)", "²Õ´ºØÃþ", "³]¥ß¤é´Á", "µn°OÀç·~¶µ¥Ø")
'·s¼W¼È¦s¸ê®Æªí
With .Sheets.Add(after:=.Sheets(.Sheets.Count))
'ÂsÄý¿ï¾Ü¸ê®Æ§¨
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then path = .SelectedItems(1) & "\"
End With
'¹ï©Ò¦³¸Ó¸ê®Æ§¨¤Uªºtxt³B²z
folder = Dir(path & "*.txt")
Do While folder <> ""
.Cells.ClearContents
'¶×¤J¥~³¡¸ê®Æ
With .QueryTables.Add(Connection:="TEXT;" & path & folder, Destination:=.Range("A1"))
.Name = "¸ê®Æ"
.RefreshPeriod = 0
.TextFileSpaceDelimiter = True 'ªÅ¥ÕÁ䬰¤À³Î¦r¤¸
.Refresh BackgroundQuery:=False
End With
'§R°£¸ê®Æ³s½u
.Cells.QueryTable.Delete
'·s¼W¸ê®Æ¨ì²Ä¤@Ó¤u§@ªí
.Parent.Sheets(1).Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 8).Value = Application.Transpose(.Range("B1:B8").Value)
folder = Dir
Loop
'§R°£¼È¦s¸ê®Æªí,¤£Åã¥Üĵ§iµøµ¡
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
.Sheets(1).Activate '¨Ï¶}±Ò¸ÓÀɮɪ½±µ¨ì²Ä¤@Ó¤u§@ªí
'¦sÀÉ
fname = Application.GetSaveAsFilename(InitialFileName:=path & "final.xls", FileFilter:="Excel Files (*.xls),*.xls", Title:="Àx¦sÀÉ®×")
'°£«D«ö¨ú®ø, §_«h¦sÀÉ
If TypeName(fname) = "String" Then .SaveAs Filename:=fname, FileFormat:=xlExcel8
End With
End Sub
¦ý¬O·|¦³3Ó°ÝÃD,
1. Y"Àç·~¤H²Î¤@½s¸¹"²Ä¤@½X¬O0, Ū¤J«á²Ä¤@½X0·|¤£¨£.
2. "²Õ´ºØÃþ"ªº¸ê®Æ,¨Ò¦p¬O: ¿W¸ê( 6 ), ¦]¬°(©M6¤¤¶¡¬OªÅ¥Õ, ¸ê®Æ·|¥uŪ¨ì"¿W¸ê("´N°±¤î.
3. "µn°OÀç·~¶µ¥Ø",¤]¦³¹³²Ä¤G¶µ¤@¼Ëªº°ÝÃD,(«á¬OªÅ¥Õ,«á±ªº¸ê®Æ´N·|¤£¨£.
·Q½Ð°Ý¦p¦ó¸Ñ¨M³o¤TÓ°ÝÃD? ¤Ó·PÁ±zªºÀ°¦£¤F~ |
|
|
|
|
|
|
- ©«¤l
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
¦^´_ 2# stillfish00
ÁÂÁÂ, ¦ý¬O¸ê®Æ§ïÅÜ«á, ·|¦]¬°0©MªÅ¥Õ³y¦¨°ÝÃD, ·Q¦A¦V±z½Ð±Ð, ·PÁ±z! |
|
|
|
|
|
|
- ©«¤l
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
¦^´_ 5# stillfish00
·PÁ±z, §Ú«á¨Ó¥Îlen°µªø«×§PÂ_,¦A¥[¤W¥Î&°µ¦X¨Öªº¤è¦¡¸Ñ¨M,¦ýÁÙ¬O±zªº°µªk¬O¤ñ¸ûeffective. Thanks. |
|
|
|
|
|
|