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

«æ!«æ!·Q½Ð°Ý¼g¥X¿ï¾Ü©Ê¶×¤J¸ê®Æªº¤èªk(¤w¦³¤@¬qµ{¦¡½X,¦ý¤£ª¾¹D¦p¦ó§ï¼g))

¦p¦ó¸Ñ?????°õ¦æ¶¥¬q¿ù»~"1004"

¦¹µ{¦¡·|¥X²{ERRORª«¥ó©w¸q¿ù»~

issue_2.png
2020-1-30 11:17


¦U¦ì¤j¤j¦n

¤§«e¦b¦¹½×¾Â¤W¨Ï¥Î¤@­Óvbaµ{¦¡,¥i¿ï¨ú¸ô¸g¸ê®Æ§¨ªºÀɮרèϨä¦ÛªF½Æ»s«á¶K¤W«ü©wªºSHEET
¦ý«o¥X²{             °õ¦æ¶¥¬q¿ù»~"1004"   
                              §ä¤£¨ì". ½ÐÀˬdÀɦW¬O§_¦³«÷¿ù,©ÎÀɮצì¸m¬O§_¥¿½T.

µ{¦¡¦p¤U
Sheets("¤u§@ªí1").Activate

fds = Application.GetOpenFilename("Excel Files (*.xlsm;*.xlsx), *.xlsm;*.xlsx", , , , True)
If IsArray(fds) Then
For i = 1 To UBound(fds)
   [A2].Offset(i - 1) = fds(i)
Next
End If

Sheets("¤u§@ªí1").Activate

For Each a In Range([A2], Cells(Rows.Count, 1).End(xlUp))
    With Workbooks.Open(a)...................................................³o¥y¥X²{ERROR
    .Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    .Close 0
  End With
  Next

Sheets("¤u§@ªí1 (2)").Activate

Range("A2").Select
Columns("A:A").EntireColumn.AutoFit
Sheets("¤u§@ªí1").Activate
   
End Sub

·Q³Â·Ð¦U¦ì¤j¤j¬Ý¤U,À°­Ó¦£¶Ü?
Ian

TOP

¦^´_ 1# iverson105
À°§A­×§ï¤@¤U·|¿ù»~¥i¯à¬O¥ý«e¿ò¯d¤£¬OÀɮתº¸ê®Æ
¥[¤Wµ{¦¡½X²M°£¥ý«e¿ò¯dªºÀɮפå¦r
  1. Option Explicit
  2. Sub Ex()
  3.     Dim fds, i As Integer
  4.     With Sheets("¤u§@ªí1")
  5.         '**²M°£¥ý«e¿ò¯dªºÀɮפå¦r****
  6.         .Range(.Range("a2"), .Range("a2").End(xlDown)) = ""
  7.         fds = Application.GetOpenFilename("Excel Files (*.xlsm;*.xlsx), *.xlsm;*.xlsx", , , , True)
  8.         If IsArray(fds) Then
  9.             For i = 1 To UBound(fds)
  10.                 .Range("A2").Cells(i) = fds(i)
  11.                 With Workbooks.Open(.Range("A2").Cells(i))
  12.                     .Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  13.                     .Close 0
  14.                 End With
  15.             Next
  16.         End If
  17.     End With
  18. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

«æ!«æ!·Q½Ð°Ý¼g¥X¿ï¾Ü©Ê¶×¤J¸ê®Æªº¤èªk(¤w¦³¤@¬qµ{¦¡½X,¦ý¤£ª¾¹D¦p¦ó§ï¼g))

½Ð°Ý¦U¦ì¤j¤j
¥H¤U¨â¬qµ{¦¡½X¬O
1.¶}±Ò¸ê®Æ§¨Åª¨ú©Ò­nªºÀɮ׸ô®|³sµ²
2.§â¶}±Òªº³sµ²ÀÉ®×¥´¶} ¨Ã±NÀɮפºªºsheet¸ê®Æ¶×¤J
°ÝÃD¬O·í·Q­n¶×¤Jªº¸ê®Æ¦³«Ü¦hsheet,·Q«ü©w¶×¤J¬Y­Ósheetªº¬Y¤@Äæ¦ì©Î¬Y¤@¦C¸ê®Æ®É,­n«ç»ò§ï¼g

ÁÂÁ¦U¦ì«ü±Ðªü
  1. Sub DATA_INPUT()

  2. fds = Application.GetOpenFilename("Excel Files (*.xlsm;*.xlsx), *.xlsm;*.xlsx", , , , True)
  3.                                 
  4. If IsArray(fds) Then
  5. For i = 1 To UBound(fds)
  6.    [A2].Offset(i - 1) = fds(i)
  7. Next
  8. End If

  9. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  10. On Error Resume Next  '''''' ¿ù»~¸õ¹L
  11. For Each a In Range([A2], Cells(Rows.Count, 1).End(xlUp))
  12.   With Workbooks.Open(a)
  13.       .Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  14.      .Close 0
  15.   End With
  16. Next
  17. On Error GoTo 0 '''''''''''''¿ù»~¸õ¹L'''''''''''
  18. End Sub
½Æ»s¥N½X
Ian

¦^´_ 3# iverson105
.Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
¤W¦¡**¬°©Ò¶}±Ò¬¡­¶Ã¯¤W©Ò¦³¤u§@ªí½Æ»s¨ìThisWorkbook(°õ¦æ¥¨¶°ªº¬¡­¶Ã¯)¤U

§A·Q§ï¬°·Q«ü©w¶×¤J¬Y­Ósheetªº¬Y¤@Äæ¦ì©Î¬Y¤@¦C¸ê®Æ®É
¸Õ¸Õ¬Ý
.Sheets(1).Rows(1).Copy ThisWorkbook.Sheets("¤u§@ªí2").Rows(3)
**©Ò¶}±Ò¬¡­¶Ã¯²Ä¤@­ÓSheet²Ä¤@¦C(¾ã¦C)½Æ»s¨ìThisWorkbook¦WºÙ"¤u§@ªí2"Sheet¤Wªº²Ä¤G¦C(¾ã¦C)
.Sheets("¤u§@ªí1").Range("b5:d6").Copy ThisWorkbook.Sheets("¤u§@ªí2").Range("f5")
**©Ò¶}±Ò¬¡­¶Ã¯¦WºÙ"¤u§@ªí1"Sheet¤W"b5:d6"½Æ»s¨ìThisWorkbook¦WºÙ"¤u§@ªí2"Sheet¤Wªº"f5"
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE
½Ð°Ý¤@¤U
§Ú·Q§ï¼gªº¬O

    .Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
¤W¦¡**¬°©Ò¶}±Ò¬¡­¶Ã¯¤W©Ò¦³¤u§@ªí½Æ»s¨ìThisWorkbook(°õ¦æ¥¨¶°ªº¬¡­¶Ã¯)¤U

(§â©Ò¦³¶}±Òªº¬¡­¶Ã¯¤Wªº¬Y¤@¤u§@ªí ¤¤ªº(¦C¦p:Sheet("C").Range("A39:D99")¦Û°Ê½Æ»s¨ì§Ú«ü©wªºsheet(ie:"¤u§@ªí2")ªºA1¶}©l)
¦]¬°¶}±Òªº¤u§@ªí¥i¯à¦³«Ü¦h­Ó(ie:sheet("C")/Sheet("C1") /Sheet("C2")....¦ý¨C­Ósheet¸Ì §Ú¥u­nRange("A39:D99"),
¥i¥H§ï¼g¦¨   .Sheets.Range("A39:D99").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

TKS
Ian

TOP

¦^´_ 2# GBKEE

·P¿E¤£ºÉ
Ian

TOP

¦^´_ 6# iverson105

¹ï§Aªº»¡©ú¨S¦³«Ü©ú¥Õ
¸Õ¸Õ¬Ý¤U­±µ{¦¡½X¹ï¶Ü!
  1. Option Explicit
  2. Sub Ex()
  3.     Dim fds, i As Integer, Rng As Range, XSh As Worksheet, Sh As Worksheet
  4.         fds = Application.GetOpenFilename("Excel Files (*.xlsm;*.xlsx), *.xlsm;*.xlsx", , , , True)
  5.         If IsArray(fds) Then
  6.             Set Rng = Sheets("¤u§@ªí2").Cells(Rows.Count, "a").End(xlUp)  '   ªºsheet(ie:"¤u§@ªí2")ªºA1¶}©l
  7.             If Rng <> "" Then Set Rng = Rng.Offset(1)
  8.             For i = 1 To UBound(fds)
  9.                 With Workbooks.Open(fds(i))
  10.                     For Each Sh In .Sheets
  11.                     '  If InStr(Sh.Name, "XXX") Then  '¥i¥[¤W±ø¥ó ¦³«ü©w¤u§@¦WºÙ
  12.                         Sh.[A39:D99].Copy Rng    '¦ý¨C­Ósheet¸Ì §Ú¥u­nRange("A39:D99"),
  13.                         Set Rng = Sheets("¤u§@ªí2").Cells(Rows.Count, "a").End(xlUp).Offset(1)
  14.                         Debug.Print Rng.Address
  15.                       'End If
  16.                     Next
  17.                     .Close 0
  18.                 End With
  19.            Next
  20.         End If
  21. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

Error1.png
2020-4-29 13:47
Error2.png
2020-4-29 13:49
¦^´_ 7# GBKEE


    ½Ð°Ý¤j¤j
§Ú¦³ªþ¤W¨â±i¹Ï¤ù
1.¬O ¿ù»~°T®§
2.¬O§Úªº¾Þ§@·§©À
¤£ª¾¹D§A³o¼Ë¦³¨S¦³¤ñ¸û©ú¥Õ§Úªº·N«ä


·PÁ«ü±Ð
Error1.png
Ian

TOP

¦^´_ 8# iverson105
  1. Option Explicit
  2. Sub Ex()
  3.     Dim fds, i As Integer, Rng As Range, x_Sh As Worksheet, Sh As Worksheet
  4.         fds = Application.GetOpenFilename("Excel Files (*.xlsm;*.xlsx), *.xlsm;*.xlsx", , , , True)
  5.         If IsArray(fds) Then
  6.             Set x_Sh = ThisWorkbook.Sheets("¤u§@ªí2")   '§A«ü©w½Æ»s¸ê®Æ¨ìªº¤u§@ªí
  7.             Set Rng = x_Sh.Cells(Rows.Count, "a").End(xlUp)  '
  8.             If Rng <> "" Then Set Rng = Rng.Offset(1)
  9.             For i = 1 To UBound(fds)
  10.                 With Workbooks.Open(fds(i))  '¶}±Ò«ü©wªºÀÉ®×
  11.                     For Each Sh In .Sheets
  12.                       If InStr(UCase(Sh.Name), "SHEETC") Then    '§A©Ò«ü©wªº¤u§@ªí¦WºÙ"SHEETC"
  13.                          Sh.[A39:D99].Copy Rng                                      '**A39:D99 §A­n½Æ»sªº½d³ò
  14.                         Set Rng = x_Sh.Cells(Rows.Count, "a").End(xlUp).Offset(1)
  15.                       End If
  16.                     Next
  17.                     .Close 0
  18.                 End With
  19.            Next
  20.         End If
  21. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 9# GBKEE

ÁÂÁÂ!¤j¤jªºÀ°§U!
    ·P¿E¤£ºÉ!
Ian

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD