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

Ãö©óVBA¥t¦sÀɮצWºÙ

Ãö©óVBA¥t¦sÀɮצWºÙ

³Ìªñ¦bEXCEL¸Ì­±¥t¥~°µ¤F¤@­Ó¥t¦s·sÀɪº«ö¶s
¦]¬°ÀɮצWºÙ³£¬O¦Û°Ê¥Ñ"¤ë¥÷" & "01"©R¦WªºÀɮצWºÙ
¥h¦~11¤ë¶}©l°µªº
EX:1101   1102  1103
¨º®É¨ÃµLµo²{²§±`
²{¦b1¤ë¤~¹J¨ìª¬ªp
¨º´N¬O¦sÀɵo²{«e­±¤Ö¤F0
EX:101   102   103
¸Õ¤F¦n¦h¤èªk³£µLªkÅý¥L«e­±¦³0
¤]¸Õ¹L¥ý±N¸ê®Æ¦s¦b¬YÀx¦s®æ
¦b¥ÎÀx¦s®æ©R¦W¤@¼Ë¨S¦³0
¤£ª¾¹D¦³¤°»ò¤èªk¥i¥H¿ì¨ì©O???
½Ð¦U¦ì¤j¤jÀ°¦£§Ú§a....

¦^´_ 11# peter9527

¨º´N­n§ä¨ì³Ì¤j½s¸¹ªºÀɦW
  1. Function topfile(fn$, fd$) As Integer
  2. fs = Dir(fd & fn)
  3. Do Until fs = ""
  4. If Val(Replace(fs, Val(fn), "")) > topfile Then topfile = Val(Replace(fs, Val(fn), ""))
  5. fs = Dir
  6. Loop
  7. End Function
  8. Sub Save_File()
  9. Dim f$
  10. d = Format(Date, "yymm")
  11. f = ThisWorkbook.Path & "\"
  12. k = Format(topfile(d & "*.xls", f) + 1, "00")
  13. fs = f & d & k & ".xls"
  14. yn = MsgBox("¬O§_¥t¦s¬°" & d & k & ".xls", vbYesNo)
  15. If yn = 6 Then ThisWorkbook.SaveAs fs
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 10# Hsieh


    Hsieh¤j¤j ¤£¦n·N«ä «e´X¤Ñ¤ñ¸û¦£ ²{¦b¤~¦³ªÅ
¨ä¹ê§Ú¨º­Ó°ÝÃD¥D­n¬O¤S¸I¨ì©R¦W¤è¦¡ "¤ë¥÷"&"¼Æ¦r"
¼Æ¦rªº®æ¦¡³¡¥÷...10¥H¤Uªº¼Æ¦r«e­±¨S¦³"0"...¤£¹L§Ú¤w¸g­×§ï¦n¤F

³Ì«á§Ú¦³´ú¸Õ
¤j­P¤W³£¥i¥¿±`¹B§@
¤£¹L§Ú«á¨Óµo²{¦³¤@­Ó¤p°ÝÃD
¦pªG§â¤§«eÀx¦sªº¨ä¤¤¤@­ÓÀɮקR°£±¼
¤§«áªºÀx¦sÀÉ®×´N·|¥X²{°ÝÃD¤F
·|¸ò³Ì«á¤@¦¸Àx¦sªºÀɮצWºÙ­«½ÆÀx¦s
§Ú¥H¬°¬O§Ú¦³¤£¤p¤ß§ï¿ùµ{¦¡½X
¥ÎHsieh¤j¤jªº­ì©l½X¥h´ú¸Õ
¤]¬O¦³¤@¼Ëªº±¡ªpµo¥Í...

TOP

¦^´_ 9# peter9527


    µ{¦¡³]­p³Ì­«­nªº´N¬O³W«h
§A­n±q©R¦W³W«h¤¤«ä¦Ò
¤F¸Ñ¦p¦ó®M¥Î?
¤£µM§O¤H¤]¤£À´§A¦b´y­z¤°»ò?
§A´NµLªk±o¨ì¸Ñµª
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh


    ·PÁ¤j¤jªº«ü¾É
¥i¬O§Ú·Q§â¥L¹B¥Î¦b§Ú­ì¥»ªºÀɮצWºÙÀx¦s®æ¦¡
"·í¤ë¤ë¥÷" & "00" + 1
0201    0202   0203....¥H¦¹Ãþ±À
«ç»ò¥Î³£¥Î¤£¦n
¤£ª¾¸Ó¦p¦ó­×§ï

TOP

¦^´_ 7# peter9527
2003ª©À³¸Ó¦³filesearch¥i¥Î¡A¦]À³2007ª©¥H¤W¨Ï¥ÎDIR¨ç¼Æ­pºâ
  1. Function Files_Count(Fd As String, Fs As String) As Integer 'FdÀɮץؿý¡AFs·j´MÀɦW
  2. findfs = Dir(Fd & Fs)
  3. Do Until findfs = ""
  4.    Files_Count = Files_Count + 1
  5.    findfs = Dir
  6. Loop
  7. End Function
  8. Sub add_filename()
  9. k = Files_Count(ThisWorkbook.Path & "\", Format(Date, "yymm") & "*.xls")
  10. fc = Format(Date, "yymm") & "_" & k + 1 & ".xls"
  11. yn = MsgBox("¬O§_­n¥t¦s¬°" & fc, vbYesNo)
  12. If yn = 6 Then ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & fc Else MsgBox "ÀÉ®×¥¼¥t¦s"
  13. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  peter9527

¤j·NÀ³¬O¦p¦¹
Hsieh µoªí©ó 2011-1-11 12:24



    Hsieh¤j¤j
§Ú¤S¦³¤@­Ó°ÝÃD¤F
¦pªG»¡§Ú·Q­n§âfc = fs.fileexists(fd & mymoon & Format(i, "00") & ".xls")  'ÀˬdÀɦW¬O§_¦s¦b
³o¤@¬q§ï¦¨§PÂ_ÀɮצWºÙªº«e¥|­Ó¼Æ¦r¬O§_¦³¦s¦b
­n«ç»ò§ï©O

¦]¬°¦³®É­ÔÀɮצWºÙ«á­±¥i¯à·|¥[¤@¨Çµù°O
³o¼Ë¤U¤@¦¸¥t¦sÀɮתº§ì¤§«eÀɦW´N¶]±¼¤F
­n¦Û¤v§ïÀɦW...

TOP

¦^´_ 5# Hsieh

¤Ó·PÁ¤F
³ºµM¥u¥Îµuµuªº´X¦æ´N·d©w¤F§ÚªºÃøÃD
¯u¤£ª¾¹D¸Ó«ç»ò·PÁ§A
³Ì«á¦A¤@¦¸·PÁ¤j¤jªºÀ°¦£...¯uªº¬O«D±`ÁÂÁ§A...À°¤F§Ú¤j¦£

TOP

¦^´_ 4# peter9527

¤j·NÀ³¬O¦p¦¹
  1. Sub nn()
  2. fd = ThisWorkbook.Path & "\"  '¦sÀɥؿý
  3. mymoon = Format(Date, "mm")  '¤ë¥÷
  4. Set fs = CreateObject("Scripting.FileSystemObject")
  5. Do
  6. i = i + 1
  7. fc = fs.fileexists(fd & mymoon & Format(i, "00") & ".xls")  'ÀˬdÀɦW¬O§_¦s¦b
  8. Loop Until fc = False
  9. mystr = mymoon & Format(i, "00")  '·sÀɦWºÙ
  10. yn = MsgBox("¬O§_­n¥t¦s¬°" & mystr & ".xls", vbYesNo)
  11. If yn = 6 Then ThisWorkbook.SaveAs fd & mystr & ".xls" Else MsgBox "ÀÉ®×¥¼¥t¦s"
  12. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# solely


    ¦]¬°¤½¥q­nªº®æ¦¡´N¬O­n³o¼Ë
°²³]¬O1¤ëªº²Ä¤@µ§¸ê®Æ´N¬O0101  ²Ä¤Gµ§0102...¥H¦¹Ãþ±À
©Ò¥H¤~·|³o»ò³Â·Ð.....

§Aªº¤èªk½T¹ê¥i¦æ
¥i¬O§Ú¤£ª¾¹D­n«ç»ò¹B¥Î¦b§Úªºµ{¦¡¸Ì­±

µ{¦¡½X¦p¤U
Private Sub CommandButton1_Click()


direct = ThisWorkbook.Path & "\"
session2 = Month(Date) & "01"

Do
Set fs = CreateObject("Scripting.FileSystemObject")
b = fs.fileExists(direct & session2 & ".xls")
session2 = session2 + 1
Loop Until b = False


Var = MsgBox("¬O§_­n¥t¦sÀɮצWºÙ " & session2 - 1, vbOKCancel, "¥t¦s´£¥Ü")
If Var = vbOK Then
Worksheets("Sheet1").Range("L1") = Date

direct = ThisWorkbook.Path & "\"
session1 = Month(Date) & "01"

Do
Set fs = CreateObject("Scripting.FileSystemObject")
a = fs.fileExists(direct & session1 & ".xls")
session1 = session1 + 1
Loop Until a = False
    Application.DisplayAlerts = False
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True

ActiveWorkbook.SaveAs Filename:=direct & session2 - 1 & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
MsgBox "ÀɮצWºÙ " & session2 - 1 & " ¥t¦s¦¨¥\!!"
End If

If Var = vbCancel Then
End If
End Sub

TOP

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