GBKEE

¦^´_ 11# 198188
§Úªºfile ¦W¬O Outstanding Payments  12-4-2012
run ¬O¥X²{run-time error '53': file not found
½ÐÀˬdÀɦW

1. Option Explicit
   
2. Sub txet1()
   
3.   Dim P As String, xfile As String
   
4.   '§Úªºfile ¦W¬O "Outstanding Payments  12-4-2012"
   
5.   xfile = "Outstanding Payments  12-4-2012"  '
   
6.   P = Format(Date, "M-D-YYYY")
   
7.   P = "Outstanding Payments " & P & ".xls"
   
8.   MsgBox xfile = P   '¸g¤ñ¹ï ¬O False ¨â¦r¦ê¤£¬Û¦P
   
9. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 1# 198188
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub EX()
   
3.    Dim Rng(1 To 2) As Range
   
4.    'With Workbooks.Open("C:\USER\DESTOP\E.XLSX").Sheets("2012") 'ÀÉ®×¥¼¶}±Ò®É¥Î¦¹µ{¦¡½X
   
5.    With Workbooks("E.XLSX").Sheets("2012")                      'Àɮפw¶}±Ò®É¥Î¦¹µ{¦¡½X
   
6.         
   
7.        .Range("A1").CurrentRegion.Offset(1) = ""                '²M°£Â¸ê®Æ
   
8.         
   
9.         'A2:AM2 to A100:AM100 ¬OY:\2012\A.XLSX (2012) ªº¸ê®Æ
   
10.         Set Rng(1) = .[A2]                                      '²Ä¤@­ÓRng(1)
    
11.         With Workbooks.Open("Y:\2012\A.XLSX").Sheets("2012")    'Àɮ׶}±Ò
    
12.             Set Rng(2) = .[A2:AM2]
    
13.             Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))     '¸ê®Æ¤£°±¼W¥[: ¨ì³Ì«áªº¸ê®Æ
    
14.             Rng(2).Copy Rng(1)
    
15.            .Parent.Close False                                  'ÀÉ®×Ãö³¬
    
16.         End With
    
17.         'A101:AM101 to A150:AM150¬OC:\2012\B.XLSX (Nov)ªº¸ê®Æ
    
18.         Set Rng(1) = Rng(1).End(xlDown).Offset(1)               '²Ä¤G­ÓRng(1)
    
19.         With Workbooks.Open("Y:\2012\A.XLSX").Sheets("Nov")     'Àɮ׶}±Ò
    
20.             Set Rng(2) = .[A101:AM101]
    
21.             Set Rng(2) = .Range(Rng(2), .[AM101].End(xlDown))   '¸ê®Æ¤£°±¼W¥[: ¨ì³Ì«áªº¸ê®Æ
    
22.             Rng(2).Copy Rng(1)
    
23.            .Parent.Close False                                  'ÀÉ®×Ãö³¬
    
24.         End With
    
25.         'A151:AM151 to A270:AM270¬OZ:\2012\C.XLSX (2012) ªº¸ê®Æ
    
26.         Set Rng(1) = Rng(1).End(xlDown).Offset(1)              '²Ä¤T­ÓRng(1)
    
27.         With Workbooks.Open("Y:\2012\A.XLSX").Sheets("2012")    'ÀÉ®×¥¼¶}±Ò
    
28.             Set Rng(2) = .[A151:AM151]                          '²Ä¤G¦C ¶}©l
    
29.             Set Rng(2) = .Range(Rng(2), .[AM151].End(xlDown))   '¸ê®Æ¤£°±¼W¥[: ¨ì³Ì«áªº¸ê®Æ
    
30.            .Parent.Close False                                  'ÀÉ®×Ãö³¬
    
31.         End With
    
32.     End With
    
33. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 15# 198188
§AªºÀɮצWºÙ³Ì¦n¤£­n¦³ªÅ®æ,®e©ö¦b¿é¤JÀɦW³y¦¨¤£¥¿½T,©Ò¥H¨t²Î·|§ä¤£¨ìÀÉ®×
±NÀɮצWºÙ­×¥¿¦n,¦A´ú¸Õ¬Ý¬Ý

GBKEE

¦^´_ 22# 198188
¿ù¦b³o¸Ì

1. Option Explicit
   
2. Sub EX()
   
3.     Dim P
   
4.     P = Format(Date, "m") & "-" & Format(Date, "d") & "-" & Format(Date, "Y")
   
5.     MsgBox P
   
6.     P = Format(Date, "m-D-YYYY")
   
7.     MsgBox P
   
8. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 24# 198188
¸Õ¸Õ¬Ý

1. Sub copyfile()
   
2.     '³æ¯Â½Æ»s§ó¦W
   
3.     Dim xlfile As String
   
4.     xlfile = "Y:\2012\payment 2012\Outstanding payment ss 2012\Dec 2012\Outstanding Payments" & Format(Date, "m-D-YYYY") & ".xlsm"
   
5.     If Dir(xlfile) = "" Then
   
6.         MsgBox "§ä¤£¨ì ÀÉ®×"
   
7.     Else
   
8.         FileCopy xlfile, "C:\users\patrick.hkg\desktop\outstanding payments.xlsm"
   
9.     End If
   
10. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 29# 198188
¥Ñ©ó§Úªºdata base¨S¦³¬OEÄæ¤~¥i¥Hcheck¨ì³Ì«á¤@µ§¡A½Ð°Ý§ÚÀ³¸Ó¦p¦ó§ï¡C
¨S¦³¬OEÄæ ¬O¦ó·N

GBKEE

¦^´_ 31# 198188

1. Set Rng(2) = .[A2:AL2]
   
2.     'Set Rng(2) = .Range(Rng(2), .[AL2].End(xlDown))
   
3.     Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
   
4.     'Resize ÄÝ©Ê ½Õ¾ã«ü©wªº½d³ò¡C¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí½Õ¾ã«áªº½d³ò¡C

½Æ»s¥N½X

GBKEE

¦^´_ 34# 198188
­×§ï¿ù»~ÂI

1.      If  Rng(1).End(xlDown).Row <> Rng(1).Parent.Rows.Count Then
   
2.         Set Rng(1) = Rng(1).End(xlDown).Offset(1)
   
3.     Else
   
4.         MsgBox "¤w¨ìÀɮש³³¡ µLªk·s¼W¸ê®Æ"
   
5.        Exit Sub
   
6.     End If

½Æ»s¥N½X

GBKEE

¦^´_ 38# 198188

1. Option Explicit
   
2. Sub Ex() 'µ{§Ç¦WºÙ¤£­n¥Î copy ³o¬Ovba¤èªkªºÃöÁä¦r
   
3.    Dim Rng(1 To 2) As Range
   
4.      With Workbooks("payment.XLSM").Sheets("2012")
   
5.          .Range("A1").CurrentRegion.Offset(1) = ""
   
6.         Set Rng(1) = .[E2]  'EÄæ¸ê®Æ¦³³sÄò
   
7.        'MsgBox Rng(1).Cells(1, -3).Address '¦^¨ìAÄæ
   
8.          With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jenny.XLSX").Sheets("SHEET1")
   
9.              Set Rng(2) = .[A2:L2]
   
10.              Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
    
11.              Rng(2).copy Rng(1).Cells(1, -3)   'AÄæ
    
12.             .Parent.Close False
    
13.          End With
    
14.         Set Rng(1) = Rng(1).End(xlDown).Offset(1)
    
15.          With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jane.XLSX").Sheets("SHEET1")
    
16.             Set Rng(2) = .[A2:L2]
    
17.            ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î
    
18.             Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
    
19.             Rng(2).copy Rng(1).Cells(1, -3)
    
20.             .Parent.Close False
    
21.         End With
    
22.         '
    
23.         '
    
24.         ' ¥H¤U¦P
    
25.         '
    
26.         '
    
27.         
    
28. 
    
29.       End With
    
30. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 40# 198188
With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")
±N¦¹ÀÉ®×»P¥¼¥X¿ùÀÉ®×¹ï½Õ¬Ý¬O§_¤@¼Ëªº¥X¿ù,¥i¨Ì¤U¤èµ{§Ç¬Ý¬Ý

¦^´_ 41# 198188

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng(1 To 2) As Range
   
4.     With Workbooks("payment.XLSM").Sheets("2012")
   
5.         Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) '³o Rng(1)ªº¦ì¸m
   
6.         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")
   
7.             Set Rng(2) = .[A2:L2]
   
8.             Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))
   
9.             MsgBox .Rows.Count - Rng(2).Rows.Count < .Rows.Count - Rng(1).Row 'True: Rng(2)½d³ò¤j©ó Rng(1) ´N¦³¿ù»~
   
10.             Rng(2).Copy Rng(1)
    
11.             .Parent.Close False
    
12.         End With
    
13.     End With
    
14. End Sub

½Æ»s¥N½X