happycoccolin

¦^´_ 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

GBKEE

¦^´_ 15# happycoccolin
¤£¦n·N«ä §ï¤@¤U³Ì«á Save -> SaveAs

1. Wb.SaveAs "D:\TEST\" & Wb_Name & "XLSX"  '¦sÀɪº§¹¾ã¦WºÙ

½Æ»s¥N½X

happycoccolin

¦^´_ 14# GBKEE


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

ABC (3).zip (40.64 KB)

¤U¸ü¦¸¼Æ: 5

2013-7-26 16:57

GBKEE

¦^´_ 13# happycoccolin
¦P¤@­Ó¼Ò²Õ¤¤ ¤£¯à¦³¬Û¦Pªºµ{§Ç¦WºÙ  Sub Ex()
§ä§ä¬ÝÁÙ¦³²Ä2­Ó Sub Ex() ¶Ü?

happycoccolin

¦^´_ 12# GBKEE


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

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

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

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

GBKEE

¦^´_ 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

happycoccolin

¦^´_ 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)

¤U¸ü¦¸¼Æ: 6

2013-7-26 14:16

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

GBKEE

¦^´_ 9# happycoccolin

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim d As Object, i As Variant, S  As Variant, Wb As Workbook
   
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.    
    
34.     For Each i In d.keys
    
35.         If InStr(i, "-") Then If Mid(i, InStr(i, "-"), 2) <> "-1" Then d.Remove i        '¥i©¿²¤"-"ªº¨BÆJ
    
36.     Next
    
37.     With Workbooks("C.xlsx").Sheets(1)
    
38.         .Cells.Clear
    
39.         S = Application.Transpose(Application.Transpose(d.ITEMS))
    
40.         .[A1].Resize(UBound(S, 1), UBound(S, 2)) = S
    
41.     End With
    
42. End Sub

½Æ»s¥N½X

happycoccolin

¦^´_ 8# GBKEE


ÁÂÁªO¤j~

¥i¥H¦A½Ð°Ý¤@¤U¶Ü~¦p¦ó¤£«ü©wÀɦW~­n°µ¨ì¤H¤H³£¥i¥H§Q¥Î¦¹ÀɮרӰµ¤ñ¹ï

¨º¥i¥H«ç»ò³B²z©O?

§Ú²{¦b¬O¥HA.xlsx¬°¥D,sheet1§Ú¥[¤@­Ó«ö¶sª½±µRUN¦¹¤ñ¹ï

¦p¦ó¸ü¤J±ý¤ñ¹ïªºÀɮשO?

GBKEE

¦^´_ 7# happycoccolin
xls ¬O2003ª©¬¡­¶Ã¯ªº°ÆÀɦW
2007ª©¥H«áªº°ÆÀɦWxlsx¬O¨S¦³¥¨¶°ªº¬¡­¶Ã¯ ,°ÆÀɦWxlsm¬O¦³¥¨¶°ªº¬¡­¶Ã¯.