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

[µo°Ý] ±N¸ê®Æ¼g¤J¨ì¨ä¥L¦h­ÓEXCELÀÉ®×

¨â­Óxlsªº¤u§@ªí¸ê®Æ¦P¨B

°ÝÃD¤@
°²¦p§Ú¦³A.xlsx  »PB.xlsm (¥¨¶°ÀÉ)
A.xlsx ¦³¤u§@ªí1

B.xlsm ¦³¤u§@ªí1¡A­n¥h§ì¨ú©Ò¦³Aªº©Ò¦³¤u§@ªí1¤º®e¨ìB¤u§@ªí1 (¥u­nA.xlsx¸ê®Æ¦³§ó·sB´N·|§ó·s¸ê®Æ)

°ÝÃD¤G

°²¦p§Ú¦³A.xlsx  »PB.xlsm (¥¨¶°ÀÉ)
A.xlsx ¦³¤u§@ªí1

B.xlsm ¦³¤u§@ªí1¡A­n¥h§ì¨ú©Ò¦³A¤u§@ªí1¸Ì­±¾ã¦X¤º®e¦p B¾ã¦æ¡BD¾ã¦æ¡BE¾ã¦æ ¨ìB¤u§@ªí1ªº A¾ã¦æ¡BB¾ã¦æ¡BC¾ã¦æ (¥u­nA.xlsx¸ê®Æ¦³§ó·sB´N·|§ó·s¸ê®Æ)

³Â·Ð¦U¦ì¤j¤j¤F

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-11-4 15:30 ½s¿è

¦^´_ 1# mark761222

­º¥ý¥²¶·»¡©ú¡A¨â­Óxlsªº¤u§@ªí¸ê®Æ¤£¥i¯à¦P¨B¡I¡I´Nºâ¦P®É§ó·s®É¡A¤]¤£¯àºÙ¬°¦P¨B¡I´Nºâµ{¦¡½X¼g¦baÀɮסA¤]¤@¼Ë¡A³£·|¦³¨Ò¥~ªº®É­Ô¡A©Ò¥H¥u¯à¥Hµ{¦¡¨ÓŪ¨úaÀɮתº³Ì·s¸ê®Æ¦Ó¤w¡A¤U­±µ{¦¡½X¤]¥u¬OŪ¨ú³Ì·s¸ê®Æ¡A¦Ó¤£¯àºÙ¤§¬°"¦P¨B"
¨ä2­Ó°ÝÃDµ{¦¡½X¦p¤U¡G
  1. Public Sub ex1()
  2.     Sheets("¤u§@ªí1").UsedRange.ClearContents
  3.     Dim xlPath As Variant, xlFile As Variant
  4.     Dim arr
  5.     xlPath = ThisWorkbook.Path & "\"
  6.     xlFile = "a.xlsx"
  7.     Workbooks.Open (xlPath & xlFile)
  8.     arr = Workbooks(xlFile).Worksheets("¤u§@ªí1").UsedRange
  9.     Workbooks(xlFile).Close True
  10.     Sheets("¤u§@ªí1").[A1].Resize(UBound(arr), UBound(arr, 2)) = arr
  11. End Sub
  12. Public Sub ex2()
  13.     Sheets("¤u§@ªí1").UsedRange.ClearContents
  14.     Dim xlPath As Variant, xlFile As Variant
  15.     Dim Barr, Darr, Earr
  16.     xlPath = ThisWorkbook.Path & "\"
  17.     xlFile = "a.xlsx"
  18.     Workbooks.Open (xlPath & xlFile)
  19.     With Workbooks(xlFile).Worksheets("¤u§@ªí1")
  20.         Barr = .Range("B1", .Cells(65535, "B").End(xlUp))
  21.         Darr = .Range("D1", .Cells(65535, "D").End(xlUp))
  22.         Earr = .Range("E1", .Cells(65535, "E").End(xlUp))
  23.     End With
  24.     Workbooks(xlFile).Close True
  25.     With Sheets("¤u§@ªí1")
  26.         .[A1].Resize(UBound(Barr)) = Barr
  27.         .[B1].Resize(UBound(Darr)) = Darr
  28.         .[C1].Resize(UBound(Earr)) = Earr
  29.     End With
  30. End Sub
½Æ»s¥N½X

TOP

·PÁÂlpk187!
¥¿¦b§V¤O²z¸Ñ§A¼gªºµ{¦¡¬[ºc

TOP

[µo°Ý] ±N¸ê®Æ¼g¤J¨ì¨ä¥L¦h­ÓEXCELÀÉ®×


§Ú¦³¤T­ÓEXCELÀɮפÀ§O¬°A B C ¦p¹Ï
A¬°­pºâÀɮ׳z¹L«ö¶s±N¸ê®Æ¶Ç°e¨ìB »P C
§PÂ_B »P C¸ê®Æ³Ì«á¤@µ§¸ê®Æ¨Ã¶ñ¤W

¦³¤j­ô¤j©j¼g¹LÃþ¦¡ªºµ{¦¡¶Ü

¦^´_ 1# mark761222

½Ð°Ñ¦Ò
http://minyeh187.pixnet.net/blog/post/166816531

TOP

TO lpk187
¤S¹J¨ì°ÝÃD¤F¡Aµy·L§ï¤F¤@¤Uµ{¦¡
°²³]A ¤u§@ªí¦³2µ§¸ê®Æ A1:E1 ­n°e¸ê®Æ¨ìB
A2:E2­n°e¸ê®ÆC
§ï¤F¥H¤Uµ{¦¡¡A¦ý¬O¥u¶Ç°e¤é´Á¡A¤º®e¤£·|¶Ç°e¡A¦Ó¥B¤é´ÁÁÙ­«½Æ2¦¸¡A¬O­þ¸Ì²z¸Ñ¿ù»~¤F¶Ü

¤£¦n·N«ä¤@­È³Â·Ð§A
  1. Private Sub Workbook_Open()
  2. Dim xlPath As Variant, Ro As Integer
  3. Dim xlFilea, xlFileb, arra, arrb
  4.     xlPath = ThisWorkbook.Path & "\"
  5. xlFilea = ("B.xlsx")
  6. xlFileb = ("C.xlsx")
  7. arra = Sheets("¤u§@ªí1").Range("A1:E1")
  8. arrb = Sheets("¤u§@ªí1").Range("A2:E2")

  9. For i = 0 To 1
  10.     Workbooks.Open (xlPath & xlFilea)
  11.     With Workbooks(xlFilea).Worksheets("¤u§@ªí1")
  12.          Ro = .Cells(65535, 1).End(xlUp).Row + 1
  13.         .Cells(Ro, 1) = arra
  14.     End With
  15.     Workbooks(xlFilea).Close True
  16. Next
  17. For i = 0 To 1
  18.     Workbooks.Open (xlPath & xlFileb)
  19.     With Workbooks(xlFileb).Worksheets("¤u§@ªí1")
  20.          Ro = .Cells(65535, 1).End(xlUp).Row + 1
  21.          .Cells(Ro, 1) = arrb
  22.     End With
  23.     Workbooks(xlFileb).Close True
  24. Next

  25. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# mark761222


   ¤é´ÁÁÙ­«½Æ2¦¸¬O§A¦h¼g¤F For i = 0 To 1 ¡A§â2­ÓFor i = 0 To 1...Next§R¤F§a¡I
­P©ó¬°¤°»ò¥u¶Ç°e¤é´Á¡A¤º®e¤£·|¶Ç°e
«h¬O.Cells(Ro, 1) = arra ¿ù»~¡A§AÀ³¸Ó­n¦h¥[Resize½d³òµ¹¥¦
.Cells(Ro, 1).Resize(Ubound(arra),Ubound(arra,2)) = arra
.Cells(Ro, 1) = arrb ¥ç¦P¤W

TOP

¦^´_ 7# lpk187

ÁÂÁÂ!¯uªº¥i¥H¤F¡A¦pªG­n¥[¤WÅçÃÒ¾÷¨îÀ³¸Ó«ÜÃø§a
¨Ò¦pA 2015/11/6¥h°»´úB 2015/11/6 »PC 2015/11/6  ¦pªG¦³³o­Ó¤é´Á¤£·s¼W¡A¦³ªº¸Ü·s¼W

ÁÂÁ§Aªº«ü¾É!

TOP

¦^´_ 8# mark761222

¸Õ¸Õ¬Ý
  1. Private Sub Workbook_Open()
  2. Dim xlPath As Variant, Ro As Integer
  3. Dim xlFilea, xlFileb, arra, arrb
  4.     xlPath = ThisWorkbook.Path & "\"
  5. xlFilea = ("B.xlsx")
  6. xlFileb = ("C.xlsx")
  7. arra = Sheets("¤u§@ªí1").Range("A1:E1")
  8. arrb = Sheets("¤u§@ªí1").Range("A2:E2")
  9.     Workbooks.Open (xlPath & xlFilea)
  10.     With Workbooks(xlFilea).Worksheets("¤u§@ªí1")
  11.         Set da = .Columns(1).Find(arra(1, 1), , , , , 2)
  12.         If Not da Is Nothing Then GoTo 10
  13.          Ro = .Cells(65535, 1).End(xlUp).Row + 1
  14.          
  15.         .Cells(Ro, 1) = arra
  16.     End With
  17. 10:
  18.     Workbooks(xlFilea).Close True

  19.     Workbooks.Open (xlPath & xlFileb)
  20.     With Workbooks(xlFileb).Worksheets("¤u§@ªí1")
  21.         Set da = .Columns(1).Find(arra(1, 1), , , , , 2)
  22.         If Not da Is Nothing Then GoTo 10
  23.         Ro = .Cells(65535, 1).End(xlUp).Row + 1
  24.         .Cells(Ro, 1) = arrb
  25.     End With
  26. 20:
  27.     Workbooks(xlFileb).Close True

  28. End Sub
½Æ»s¥N½X

TOP

¤Ô¹Ø!¶V¨Ó¶V½ÆÂø¡A³£§Ö¬Ý¤£À´¤F= =
¥i¥HÀ°¦£µù¸Ñ¤@¤U¶Ü@@

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD