ªð¦^¦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....

¦^´_ 1# peter9527
¦pªG¸ê®Æ¨Ó·½B1¬O¤é´Á«¬ºA2011/01/10
Format(Range("B1"),"mm")
¦pªG¸ê®Æ¨Ó·½B1¬O¼Æ­È«¬ºA20110110
Mid(Range("B1"),5,2)
¦pªGÁÙ¤£¦æ¡A´N¶K¤@¤U§Aªºµ{¦¡½X§a~~

§Ú¤ñ¸û¤£³ßÅw«ä¦Ò¡A©Ò¥Hª½±µ¥H"«È¤á¦WºÙ+«ØÀɮɶ¡"¬°ÀɦWXD
ActiveWorkbook.SaveAs Filename:="D:\¦¨¥»¸Õºâªí\" & Range("F2") & Format(Range("AH1"), "yyyymmddhhss") & ".xls"

TOP

¦^´_ 1# peter9527
ActiveWorkbook.SaveAs Filename:="D:\¦¨¥»¸Õºâªí\"  & Format(Date, "MMDD") & ".xls"

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

¦^´_ 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

¦^´_ 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

¦^´_  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

¦^´_ 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

¦^´_ 8# Hsieh


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

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

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD