ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó«ü©w±N¤£¦PÀɮק¨ªºcsvÀɤ¤¯S©w½d³òªº¸ê®Æcopy¨ì¯S©wÀɮפ¤

[µo°Ý] ¦p¦ó«ü©w±N¤£¦PÀɮק¨ªºcsvÀɤ¤¯S©w½d³òªº¸ê®Æcopy¨ì¯S©wÀɮפ¤

¤p§Ì¦³¤@­ÓÀɮק¨¦WºÙ000(¦pªþ¥ó)
¸ÓÀɮק¨000¤¤¦³¦WºÙ¤À§O¬°01.02.03µ¥3­ÓÀɮק¨
¦Ó©óÀɮק¨01¤Î02¤¤¤À§O¦³¼Æ­ÓcsvÀÉ
¥t©óÀɮק¨03¤¤¤]¦³¼Æ­ÓxlsÀÉ(¦pªþ¥ó03-1)
·Q¦bÀɮק¨03¤¤ªºxls¤¤«ü©w¸ô®|©MÀɮצWºÙ±N¸ê®Æcopy¨ì«ü©wÀx¦s®æ(¦pªþ¥ó03Àɮק¨¤¤¤§03-1ÀÉ®×»¡©ú)
¹Ï¤ù-°ÝÃD»¡©ú.jpg

000.rar (315 KB)

¦p¦óŪ¨ú¦h­ÓÀɮפ¤¦P¤@­ÓÀx¦s®æ¸ê®Æ

¦U¦ì¥ý¶i¦n
½Ð°Ý¦p¦óŪ¨ú¦h­ÓEXCELÀɮפ¤¦P¤@­ÓÀx¦s®æ¸ê®Æ(¦pªþ¥ó)

TOP

¦^´_ 1# oak0723-1

¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Dim Wb As Workbook
  3. Sub Ex()
  4.     Dim xFile As String,  Sh As Worksheet, i As Integer, Rng As Range
  5.     Set Wb = Workbooks("03.XLS")   '«ü©wªºXLS¾×
  6.     xFile = ThisFile
  7.     If InStr(xFile, "CSV") = False Then MsgBox xFile: Exit Sub
  8.     Set Sh = Wb.Sheets("SHEET1") '«ü©wªºXLS¾×ªº¤u§@­¶
  9.     With Workbooks.Open(xFile)
  10.         For i = 1 To 2
  11.             Set Rng = .Sheets(1).Range(Sh.Cells(1 + 1, "E") & ":" & Sh.Cells(1 + i, "F"))  '«ü©wªº¦ì¸m
  12.             Set Rng = .Sheets(1).Range(Rng, Rng.End(xlDown))   ''«ü©wªº¦ì¸m©¹¤U¦Ü¸ê®Æªº²×ÂI
  13.             With Sh.Cells(Rows.Count, "a").End(xlUp).Offset(1)   'AÄæ³Ì©³¦C©¹¤W¨ì¦³¸ê®ÆªºÀx¦s»Õªº¤U¤@¦C
  14.                     .Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Value
  15.             End With
  16.         Next
  17.         .Close
  18.     End With
  19. End Sub
  20. Function ThisFile() As String
  21.       With Wb      '**«ü©wªºXLS¾×
  22.         ThisFile = Mid(.Path, 1, InStrRev(.Path, "\"))  '**«ü©wªºXLS¾×ªº¤W¼h¸ê®Æ§¨
  23.         With .Sheets("Sheet1")
  24.             ThisFile = ThisFile & .Range("b2") & "\" & .Range("c2") & ".CSV"  '¤W¼h¸ê®Æ§¨+¤l¸ê®Æ\ªºCSVÀɮק¹¾ã¸ô®|
  25.             '**** ÀˬdCSVªºÀɮ׬O§_¦s¦b
  26.             If Dir(ThisFile) = "" Or Application.CountA(.Range("E2:F3")) <> 4 Then ThisFile = "½ÐÀˬd" & vbLf & Join(Application.Transpose(Application.Transpose([B1:F1])), ",")
  27.         End With
  28.       End With
  29. End Function
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁÂGBKEE¦Ñ®vªº½ç±Ð
¥u¤£¹L¤p§Ìµo²{2­Ó°ÝÃD
1.­Y¿é¤J¸ê®Æ§¨©MÀɦW­Y«D­ì¹w³]01,01-1´NµLªk°õ¦æ
2.­Y¿é¤J¸ê®Æ§¨©MÀɦWºû«ù­ì¹w³]01,01-1§ó§ïÄæ¦ì°_ÂI©M²×ÂIÀx¦s®æ(¨Ò¦p¿é¤Jd5©Mf5)©Òcopy¹L¨Óªº¸ê®Æ«o¤´¬O­ì¹w³]d1,f1
3.­YÀÉ®×03§ó§ï¦WºÙ´NµLªk°õ¦æ¥X²{¿ù»~

TOP

¦^´_ 3# oak0723-1
2#ªºµ{¦¡½X¬O¨Ì§A´£¥Xªº±ø¥ó©Ò¼g,§A­n§ïÅܱø¥ó·íµM·|¤£¦æªº
  1. Set Wb = Workbooks("03.XLS")   '«ü©wªºXLS¾×
  2.     Set Wb = ActiveWindow       '§@¥Î¤¤ªºXLS¾×
  3.     Set Wb = ThisWorkbook       'µ{¦¡½X©Ò¦bªºXLS¾×
  4.     Set Wb = Windows(2)           '²Ä¤G­ÓXLS¾×
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁÂGBKEE¦Ñ®vÄ@·N¼ö¤ßÄ~Äò¦^´_¤p§Ìªº°ÝÃD
¥i¯à§Ú°ÝÃD°Ý±o¤£¦n
¦]¬°¤p§Ì¥u¬OÁ|¨Ò
©Ò¥HÀɦW³£Â²¤Æ´À¥N
¦ý¹ê°È¤W¤p§Ìªº¸ê®Æ§¨«Ü¦h
¨C­ÓÀɮפ¤ªºcsvÀɮפ]«Ü¦h
·íµM¨C­ÓÀɦW¤]³£¤£¦P
¦]¬°¨C­ÓcsvÀɤº³£¦³2²Õ¸ê®Æ¶·cpy¶°¤¤¦b¦P¤@­ÓxlsÀɤº
©Ò¥H§Æ±æ¦³¼ö¤ßºô¤Í¯àÀ°À°¤p§Ìªº¦£
ÁÂÁÂ

TOP

·PÁÂGBKEE¦Ñ®vÄ@·N¼ö¤ßÄ~ÄòÃö¤ß¤p§Ìªº°ÝÃD
¤p§Ì³o­Ó°ÝÃD¤w¥Î¨ä¥L¤è¦¡¸Ñ¨M
¤£ª¾¬O§_¯àÀ°¤p§Ì¥t¤@­Ó°ÝÃD
¦p¦óŪ¨ú¦h­ÓÀɮפ¤¦P¤@­ÓÀx¦s®æ¸ê®Æhttp://forum.twbts.com/thread-19358-1-1.html

TOP

¦^´_ 7# oak0723-1
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng(1 To 2) As Range, Sh As Worksheet, i As Integer, E As Range
  4.     '**³]©wŪ¨ú¦hÀɮתº¸ê®Æ¦ì¸m
  5.     Set Rng(1) = ActiveWorkbook.ActiveSheet.Range("C7")                 '**µøµ¡§@¥Î¤¤ªº¬¡­¶Ã¯ªº§@¥Î¤¤¤u§@­¶ªº"C7"
  6.     'Set Rng(1) = WorkbookS("???.xls").Sheets("???").Range("C7")        '**«ü©wªº¬¡­¶Ã¯ªº???¤u§@­¶ªº"C7"
  7.     'Set Rng(1) = ThisWorkbook.Sheets("???").Range("C7")                   '**µ{¦¡½X©Ò¦bªº¬¡­¶Ã¯ªº???¤u§@­¶ªº"C7"
  8.     '**³]©w F6µ¥¶À¦â°Ï°ì¿é¤JÀx¦s®æ½s¸¹ªº¦ì¸m
  9.     Set Rng(2) = Rng(1).Parent.Range("F6")
  10.     Set Rng(2) = Range(Rng(2), Rng(2).End(xlToRight))                       '©µ¦ù¨ì³Ì¥kÃ䪺¸ê®Æ
  11.     '**************************************
  12.     Do While Rng(1) <> ""    'Rng¦³¸ê®Æ
  13.         If Dir(Rng(1) & "\" & Rng(1).Cells(1, 2) & ".xls") = "" Then GoTo NoXls_Or_NoSheet:
  14.         With Workbooks.Open(Rng(1) & "\" & Rng(1).Cells(1, 2) & ".xls")  '**¶}±Ò¦hÀÉ®×  (XLS©Î CSV)
  15.             On Error GoTo NoXls_Or_NoSheet:
  16.             Set Sh = .Sheets(Rng(1).Cells(1, 3).Text) '**³]©w¦hÀɮתº¤u§@­¶
  17.             Err.Clear
  18.             i = 4
  19.             For Each E In Rng(2)
  20.                 Rng(1).Cells(1, i) = Sh.Range(E)  'Ū¨ú¶À¦â°Ï°ì«ü©wªº¸ê®Æ
  21.                 i = i + 1
  22.             Next
  23.             .Close        'Ãö³¬ÀÉ®×
  24.         End With
  25.         Set Rng(1) = Rng(1).Offset(1)  '¤U¤@­ÓÀɮ׸ê®Æ
  26.     Loop
  27.     '*********************************************
  28.     Exit Sub           'µ{¦¡°õ¦æ¥¿±`¤UÂ÷¶}³oµ{¦¡
  29. NoXls_Or_NoSheet:
  30.     MsgBox Rng(1) & " \ " & Rng(1).Cells(1, 2) & " \ " & Rng(1).Cells(1, 3) & vbLf & "§ä¤£¨ì" & IIf(Err, Rng(1).Cells(1, 3).Text, "")
  31. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁÂGBKEE¦Ñ®vÄ@·N¼ö¤ßÀ°¤p§Ì¸Ñµªªº°ÝÃD
¦]¤p§Ì³Ìªñ¤ñ¸û¦£¥[¤W¦³ÂI·P«_..©Ò¥Hºë¯«¸û®t
©Ò¥H¤ñ¸û±ß¦^´_..¯u¬O©êºp....
­è´ú¸Õ¹L..½T¹ê¬O¤p§Ì©Ò»Ý­nªº
«D±`·PÁÂ...
¦A¦¸·P®¦...

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD