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

[µo°Ý] ½Ð°ÝVBA¥i¥H°µ¨ì¨âÀɮפñ¹ï«á¦A²£¥Í¥t¤@Àɮתº¤ñ¹ïµ²ªG¶Ü?

¦^´_ 10# GBKEE


    ÁÂÁªO¤j!!!!!!

¦ý¬O°»¿ù¥Ø«e°±¦b
    With Workbooks("C.xlsx").Sheets(1)

¬O¤£¬O­Y§Ú¤£«ü©w·sÀɮתºÀɦW´N¤£·|¦³¿ù»~©O?

À³¸Ó¬O»¡¤ñ¹ï§¹¦¨«á¶]¥XªºÀɮץѨϥΪ̦۩w¸q¦WºÙ

©êºp°ÝÃD«Ü¦h

§Úªþ¤WÀÉ®×µ¹±z°Ñ¦Ò
ABC.xlsm¬°¸ê®Æ®w
B.xlsx¬°±ý¤ñ¹ïÀÉ®×
§Æ±æ¤ñ¹ï«á²£¥Í¤@·sÀÉ®×(¥Ñ¨Ï¥ÎªÌ¦Û©w¸q¦WºÙ¥t¦s·sÀÉ)
ABC (2).zip (22.42 KB)

«Ü°ª¿³ªO¤jÄ@·N¸Ñ´b~^^

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-7-26 17:05 ½s¿è

¦^´_ 11# happycoccolin
  1. Option Explicit
  2. Sub Ex()
  3.     Dim d As Object, i As Variant, S  As Variant, Wb As Workbook, Wb_Name As String
  4.     Set d = CreateObject("scripting.dictionary")
  5.     With Application.FileDialog(msoFileDialogFilePicker) 'FileDialog :ªíÀÉ®×¹ï¸Ü¤è, msoFileDialogFilePicker(°Ñ¼Æ):¿ï¨úÀÉ®×
  6.         .AllowMultiSelect = False                        '¤¹³\¨Ï¥ÎªÌ±qÀÉ®×¹ï¸Ü¤è¶ô¿ï¨ú¦h­ÓÀÉ®×=False
  7.          If .Show = False Then MsgBox "¨S¦³¿ï¾ÜÀÉ®× !!!":   Exit Sub
  8.          Set Wb = Workbooks.Open(.SelectedItems(1))   '¶}±Ò«ü©wÀÉ®×
  9.     End With
  10.     i = 1
  11.     With Workbooks("A.xlsx").Sheets(1)   '
  12.         Do While .Cells(i, "e") <> ""
  13.             d(.Cells(i, "e").Value) = .Cells(i, "A").Value
  14.             i = i + 1
  15.         Loop
  16.     End With
  17.      i = 1
  18.     With Wb.Sheets(1)
  19.         Do While .Cells(i, "J") <> ""
  20.            S = Join(Application.Transpose(Application.Transpose(.Range("A" & i & ":J" & i))), ",")
  21.            If d.Exists(.Cells(i, "J").Value) Then
  22.                 S = S & "," & d(.Cells(i, "J").Value)
  23.                 d(.Cells(i, "J").Value) = Split(S, ",")
  24.            Else
  25.                 d(.Cells(i, "J").Value) = Split(S & ",No Data", ",")
  26.                 S = d(.Cells(i, "J").Value)
  27.            
  28.            End If
  29.             i = i + 1
  30.         Loop
  31.         .Parent.Close False               'Ãö³¬«ü©wÀɮפ£¦sÀÉ
  32.     End With
  33.     For Each i In d.keys
  34.         If InStr(i, "-") Then If Mid(i, InStr(i, "-"), 2) <> "-1" Then d.Remove i        '¥i©¿²¤"-"ªº¨BÆJ
  35.     Next
  36.     Do
  37.         Wb_Name = InputBox("¿é¤J·sÀɦW", "¦sÀɦWºÙ")
  38.     Loop Until Wb_Name <> ""                             'ª½¨ì¦³¿é¤J¦r¦êÂ÷¶}°j°é
  39.     Set Wb = Workbooks.Add(1)
  40.     With Wb.Sheets(1)
  41.         .Cells.Clear
  42.         S = Application.Transpose(Application.Transpose(d.ITEMS))
  43.         .[A1].Resize(UBound(S, 1), UBound(S, 2)) = S
  44.     End With
  45.     Wb.SaveAs "D:\TEST\" & Wb_Name & "XLSX"  '¦sÀɪº§¹¾ã¦WºÙ
  46. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 12# GBKEE


    ·PÁªO¤jªº¨³³t¦^ÂÐ~~~

½Ð°Ý¤@¤U¥Ø«e°»¿ù°±¦b
Sub Ex()

§Ú²{¦b­n«ç»ò³B²z©O?

ÁÂÁªO¤jªºÀ°¦£!!!!!!!

TOP

¦^´_ 13# happycoccolin
¦P¤@­Ó¼Ò²Õ¤¤ ¤£¯à¦³¬Û¦Pªºµ{§Ç¦WºÙ  Sub Ex()
§ä§ä¬ÝÁÙ¦³²Ä2­Ó Sub Ex() ¶Ü?
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 14# GBKEE


    ª©¤j~~¥u¦³¤@­ÓEx~~~§Ú¦³ªþÀÉ~~>< «ô°UÀ°¦£¼·ªÅ¬Ý¤@¤U¦n¶Ü~
·P¿E¤£ºÉ!!!

ABC (3).zip (40.64 KB)

TOP

¦^´_ 15# happycoccolin
¤£¦n·N«ä §ï¤@¤U³Ì«á Save -> SaveAs
  1. Wb.SaveAs "D:\TEST\" & Wb_Name & "XLSX"  '¦sÀɪº§¹¾ã¦WºÙ
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 16# GBKEE


    ÁÂÁª©¤jªºÀ°¦£~~~~

¦A½Ð±Ð¤@¤U~¥Ø«e°»¿ù°±¦b
With Workbooks("A.xlsx").Sheets(1)   '

¦]¬°§Ú¤w¸g±N¸ê®Æ®w©ñ¦bABC.xlsm¸Ì­±¤F

¨º¤U­±³o¬q»yªk¬O§_´N¤£¥Î°w¹ïA.xlsx°µ³B²z©O?

i = 1
    With Workbooks("A.xlsx").Sheets(1)   '
        Do While .Cells(i, "e") <> ""
            d(.Cells(i, "e").Value) = .Cells(i, "A").Value
            i = i + 1
        Loop

TOP

¦^´_ 17# happycoccolin
¦]¬°§Ú¤w¸g±N¸ê®Æ®w©ñ¦bABC.xlsm¸Ì­±¤F
    With Workbooks("A.xlsx").Sheets(1)   -§ï¤@¤UÀɦW  With Workbooks("ABC.xlsm").Sheets(1)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 18# GBKEE


    ³ø§iª©¤j~

¥Ø«e°»¿ù°±¦b
Wb.SaveAs "D:\TEST\" & Wb_Name & "XLSX"  '¦sÀɪº§¹¾ã¦WºÙ

¥i¬O¤ñ¹ïµ²ªG³q³q³£¬ONO DATA­C~

½Ð¨£ªþÀÉ
ABC3.zip (58.66 KB)

TOP

¦^´_ 19# happycoccolin
¥i¬O¤ñ¹ïµ²ªG³q³q³£¬ONO DATA
°ÝÃD¦b³o¸Ì
  1. With Workbooks("ABC.xlsm").Sheets(1)
  2.         '³oSheets(1) ¬OABC.xlsmªº²Ä¤@­Ó¤u§@ªí (MAIN)
  3.         '¬O§_­n§ï¦¨ Sheets(2) ->  ²Ä¤G­Ó¤u§@ªí (A)
  4.         Do While .Cells(i, "e") <> ""
  5.             d(.Cells(i, "e").Value) = .Cells(i, "A").Value
  6.             i = i + 1
  7.         Loop
  8.     End With
½Æ»s¥N½X
D:\TEST \  ³o¸ê®Æ§¨¬O§ÚÀH·N¼gªº,§A»Ý§ï¬°§APC¤¤¦s¦bªº¸ê®Æ§¨
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD