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

[µo°Ý] Àx¦s®æ¯Á¤Þ­È

[µo°Ý] Àx¦s®æ¯Á¤Þ­È

¦U¦ì¥ý¶i¦n,

·Q½Ð±Ð¤@­ÓVBAªº¼gªk
§Ú±Nµ{¦¡©Ò¦bªºÀx¦s®æ
B1³]¬°­n°õ¦æªºÀɮצWºÙ
B2³]¬°¤u§@ªí¦WºÙ,¥B¥H¼Æ¦r©R¦W
B3³]¬°±q²ÄN­Ósheet¶}©l°_ºâ
B4³]¬°¤@­Ó¤é´Áªº­È
°ÝÃD¡G
¥H¤Uµ{¦¡¼gªk,¥u¯à¯Á¤Þ¨ì­n°õ¦æªºÀÉ®×¥h,¨ä¥L³£¤£°_§@¥Î,
¨Ò¦p¡G
B2¥»¨Ó¬O«ü¦V¯S©w¤u§@ªí,¦ý¦]¬°¥H¼Æ¦r©R¦W,´N³Q·í¦¨¬O²Än­Ó¤u§@ªí,¦Ñ¬O¶]¿ùsheet
B3«hÁÙ¼g¤£¥X¨Ó
B4©Ò³]©wªº¤é´Á,«hµLªk¶K¤W«ü©wªºsheet

½Ð°Ý¥H¤Uµ{¦¡¸Ó¦p¦ó­×§ï¡H
  1. Sub ´ú¸Õ()
  2. Dim Wb As Workbook, Sh As Worksheet, x As Range
  3.     Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value) 'Àx¦s®æªº­È¬°ÀɮצWºÙ
  4.     Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Value) 'Àx¦s®æªº­È¬°¤u§@ªí¦WºÙ
  5.     Sh.Activate
  6.     With Sh
  7.     Set x = .Range("B4")
  8.     x = Range("B4").Value '«ü©w1.sheet¤é´Á
  9.     End With
  10. End Sub
½Æ»s¥N½X

¤u§@ªí

20190421.161842.jpg
2019-4-21 16:20

¦^´_ 1# PJChen


    ½Ð°Ý©pªº³o¤äµ{§Ç¥D­n¥Øªº¬°¦ó¡H
    ¬O­n±N«ü©w¤u§@ªí¨Ì¾Ú¤é´Á¨Ó°µ©R¦W¶Ü¡H

   ¬Ý¤£À´©pªºB2¡BB3¤ÎB4³]©w¡A¬G¦¹¸ß°Ý¡C

TOP

¦^´_ 1# PJChen

§Ú¤]¤£¬O«Ü¤F¸ÑÀɮתºÃöÁp©Ê
©Ò¥H±Nsh§ï¬°¦r¦ê,´N¥i¥H«ü¦V©Ò³]©w¦WºÙªºsheet

Sub ´ú¸Õ()
Dim Wb As Workbook, sh As String, x As Range
'   Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value) 'Àx¦s®æªº­È¬°ÀɮצWºÙ
    sh = Worksheets("VBA").Range("B2") 'Àx¦s®æªº­È¬°¤u§@ªí¦WºÙ
    Worksheets(sh).Activate
    With Worksheets(sh)
    Set x = .Range("B4")
    x = Worksheets("VBA").Range("B4").Value '«ü©w1.sheet¤é´Á
    End With
End Sub

TOP

¦^´_ 1# PJChen

Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Value) 'Àx¦s®æªº­È¬°¤u§@ªí¦WºÙ

§ï¬°
Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Text) 'Àx¦s®æªº­È¬°¤u§@ªí¦WºÙ

TOP

¦^´_ 4# jcchiang

ÁÂÁ§A,§ï¬°text¯uªº´N¥i¥H¤F¡I
³Ñ¤U²Ä4­Ó°ÝÃDÁÙ¨S¸Ñ¨M
¥i¥Hªº¸Ü,¤]½Ð«ü¾É¤@¤U,ÁÂÁ                ´ú¸Õ.rar (40.88 KB)

B6¬°«ü©w¸ê®Æ§¨¦WºÙA
¸Ì­±¦³N­ÓÀÉ®×
¨C­ÓÀɮתº¤u§@ªí³£¥H¼Æ¦r©R¦W,¨Ò:1¡K2¡K3¡K
µ{¦¡¼g¦bMacro.xlsm¤¤,
§Ú·Q«ü©wªº¤u§@ªí¦WºÙ©ñ¦bB2Äæ¦ì
¦Ó­n¼g¤Hªº­È©ñ¦bB4Äæ¦ì
«ü©w¼g¤JªºÀx¦s®æ¬°©ñ¦bB2Äæ¦ì
§Ú§â©Ò­n°µªº¨Æ³£©ñ¦bMacro.xlsmªº¤u§@ªíVBA¤¤,
¥u­n±q³o¸Ì­×§ï­È,´N¥i¥HÀ°§Ú¼g¤J¸ê®Æ
½Ð°Ý­n¦p¦ó¼g³o¬qVBA?¥i¥HÅý¥¦¨Ì§Ç¼g¤J«ü©wªº¸ê®Æ§¨A?

TOP

¦^´_ 5# PJChen

¥\¤O¨S«Ü¦n,½Ð¸Õ¸Õ
¸ê®Æ§¨³]©w¬°A,¦p­nÅܰʽЦۦæ­×§ï(§tÀx¦s¸ô®|)

Sub ´ú¸Õ()
Dim Wb As Workbook, Sh As Worksheet, x As Range, Source1 As String
    Source1 = Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value '­n¶}±ÒªºÀɮצWºÙ
    Workbooks.Open "c:\A\" & Source1 & ".xlsx" '¶}±Ò­n¼g¤JªºÀÉ®×   
    Workbooks("Macro.xlsm").Activate   
    Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value) 'Àx¦s®æªº­È¬°ÀɮצWºÙ
    Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Text)  'Àx¦s®æªº­È¬°¤u§@ªí¦WºÙ,¦]¬°¤u§@ªí¦WºÙ¬O¼Æ¦r,·|³Q·í¦¨"¼Æ²ÄN­Ó¤u§@ªí",ÁÙµLªk¸Ñ¨M,¥H¥[1¥ý¼È¥Î      
    Sh.Activate
    With Sh
    Set x = Sh.Range(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B5").Text) '«ü©w¼g¤JªºÀx¦s®æ¬°©ñ¦b¨º­ÓÄæ¦ì
     x = Workbooks("Macro.xlsm").Worksheets("VBA").Range("B4").Text '¼g¤Hªº­È©ñ¦b¨º­ÓÄæ¦ì
    End With
    Workbooks(Dir("C:\A\" & Source1 & ".xlsx")).Close True 'Ãö³¬¨ÃÀx¦s¼g¤JªºÀÉ®×
End Sub

TOP

¦^´_ 6# jcchiang

±z¦n,

Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1")Àx¦s®æÁö¦³«ü¦WÀɦW,¦ý¬Oµ¹¨ä¥Lµ{¦¡¥Îªº,
¦b³o­Óµ{¦¡¤¤,§Ú¬O·Q¨Ì§Ç¼g¤J«ü©wªº­È¨ìA¸ê®Æ§¨ªº©Ò¦³Àɮפ¤(¤£¥]§t¤l¸ê®Æ§¨),©Ò¥H¤£¯à¥Î«ü¦WÀɦW¤è¦¡.

TOP

Sub ´ú¸Õ()
Dim P$, S$, T$, R$, F$, N&, xA As Range, xB As Workbook, xS As Worksheet
P = [B6]: If P = "" Then MsgBox "**¤l¸ê®Æ§¨¥¼¿é¤J!¡@": Exit Sub
P = ThisWorkbook.Path & "\" & P & "\"
If Dir(P, vbDirectory) = "" Then MsgBox "**¤l¸ê®Æ§¨¤£¦s¦b!¡@": Exit Sub
S = [B2]: If S = "" Then MsgBox "**¤u§@ªí¦WºÙ¥¼¿é¤J!¡@": Exit Sub
T = [B4].Text: If S = "" Then MsgBox "**¼g¤Jªº¤º®e¥¼¿é¤J!¡@": Exit Sub
R = [B5]: If R = "" Then MsgBox "**¼g¤J¦ì§}¥¼¿é¤J!¡@": Exit Sub
On Error Resume Next: Set xA = Range(R): On Error GoTo 0
If xA Is Nothing Then MsgBox "**¼g¤J¦ì§}¤£¥¿½T!¡@": Exit Sub
If xA.Count > 1 Then MsgBox "**¼g¤J¦ì§}¤£¬O³æ¤@Àx¦s®æ!¡@": Exit Sub
'--------------------------------------------------------------------
[F1:H999].ClearContents:  Set xA = [F1]
Application.ScreenUpdating = False
Do
   If F = "" Then F = Dir(P & "*.xls") Else F = Dir()  'xls ¦Û¦æ§ï¦¨ xlsx  
   If F = "" Then Exit Do
   N = N + 1: xA = N: xA(1, 2) = F: xA(1, 3) = "(X)¤u§@ªí¤£¦s¦b"
   Set xB = Workbooks.Open(P & F):  Set xS = Nothing
   On Error Resume Next: Set xS = xB.Sheets(S): On Error GoTo 0
   If Not xS Is Nothing Then xS.Range(R) = T: xB.Save: xA(1, 3) = "(V)¼g¤J§¹¦¨"
   Set xA = xA(2):  xB.Close 0
Loop
End Sub

´ú¸Õ(1).rar (23.65 KB)


>>>>

TOP

¦^´_ 8# ­ã´£³¡ªL

¤j¤j¦n,
§Ú§ï¤FÀɦW,µ{¦¡¦bMacro.AµLªk°õ¦æ,¸Õ¤F«Ü¦h¦¸³£¤£¦æ¡I¥i¬O¶K¦b¥¿¦¡ª©¤S¥i¥H,¥Î§AªºÀɮפ]¥i¥H,§Ú¬d¤£¥X­ì¦],¥i§_À°¦£¬Ý¤@¤U?
¤£¹L´N¨º¦¸°õ¦æ¦³¨Ç°ÝÃD:
1. Macro.A¬O§Ú¥Î¨Ó´ú¸Õªº,¥¿¦¡ª©ªº¸Ì­±¦³«Ü¦hªºVBA,³o­Óµ{¦¡°õ¦æ«á,·|À°§Ú¶K¤W
¨Î¨Å-¥þ¥x.xlsx
¤ñµá¦h-OK.xlsx
¤ñµá¦h-¥þ¥x.xlsx
³o¤T­ÓÀÉ"(V)¼g¤J§¹¦¨",¦bF:H,¦Ó¥B©ú©ú¥u¦³¤T­ÓÀÉ,¥¦«o¶K¤F«Ü¦h¦¸,¤@ª½´`Àô«Üªø¤@¦ê¦Ü168¦C,
³o·|§â§Ú¥¿¦¡ª©ªºMacro.xlsmªºª©­±¯}Ãa±¼,©Ò¥H§Ú§â¼g¤Jªº¥\¯à¼È®É¨ú®ø,
2. ·í¸ê®Æ§¨¤£¬O¦bMacro.xlsmªº¦P¤@­Ó¦a¤è,¦Ó¬O¨ä¥¦¸ô®|®É,§ï¦¨¥H¤U¬O§_¥¿½T?
¨Ò¦p W:\­ÜÀx¦@¥Î\C,§ï¼g¸ô®| P = .Path & "W:\­ÜÀx¦@¥Î\C &"\"
´ú¸Õ3.rar (41.64 KB)

TOP

¦^´_ 9# PJChen


«¢ÅoPJ¡A

§Ú¤£²M·¡©p¹ê»Ú¸m¤Jµ{§ÇªºEXCEL¿ï¾Ü¬°¦ó¥H¤Î¸ê®Æ§¨¦ì¸m©Ò¦b³B¡A¦ý§Ú¬O±NMACROA¥H¤Î¸ê®Æ§¨©ñ¦b¦P¤@¸ô®|¤U¡A¨Ã¦b¦¹«e´£¤U´ú¸Õ­ã¤jªºµ{§Ç¬O¥i¦æªº¡C
¥¼µo¥Í©p©Ò´£ªº´`Àô«Üªø¦Ü168¦C¡C
«Øij©p¥i¶i¤Jµ{§Ç·í¤¤¡A±N¨C¤@­ÓÅܼƳ£¥[¤J·s¼WºÊ¬Ý¦¡¡A³v¦æÆ[¬Ý¡A¬Ý¬O­þ¤@­Ó¦a¤è¶}©l¡A¦³«D¹w´Áªº±¡ªpµo¥Í¡C(¦p¹Ï¤ù©Òµø)
µ{§Ç¸ÌÀY·s¼WºÊ¬Ý¦¡.png
2019-5-1 15:01


P.S.§Ú¦³¦b­ã¤jµ{§Ç¸ÌÀY¡A³Æµù¤@¤U¦U¦æ·N«ä¨Ñ©p¥H¤Î¨ä¥L¤H°Ñ¦Ò(¥¼§ó°Ê­ã¤jªºµ{§Ç)¡C

    Macro.A_¥[¤J³Æµù¨Ñ°Ñ¦Ò.rar (21.94 KB)

TOP

        ÀR«ä¦Û¦b : Ãø¦æ¯à¦æ¡AÃø±Ë¯à±Ë¡AÃø¬°¯à¬°¡A¤~¯àª@µØ¦Û§Úªº¤H®æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD