能否让EXCEL表上的时间与系统时间同步

excelvbafanes

::'( 我有一工作表,一直开它可以联机记录当天的数据,可是过了晚上12点后,却不能跟系统日期同步.这样就记录不了第二天的数据.要关了再重新打开才能记录,有什么办法解决让它跟系统日期同步?

[ 本帖最后由 excelvbafanes 于 2009-2-1 21:08 编辑 ]

qbliu

要获取系统时间是比较简单的，虽然不懂你到底什么意思，但传一个我自己写着玩的东西，看能不能对你有所帮助，建议你上传附件来说明你的需求。

excelvbafanes

谢谢有一定帮助不过还没解决问题

excelvbafanes

http://maweifeng.cnblogs.com/

excelvbafanes

我有一工作表,一直开它可以联机记录当天的数据,可是过了晚上12点后,却不能跟系统日期同步.这样就记录不了第二天的数据.要关了再重新打开才能记录,有什么办法解决让它跟系统日期同步?

[ 本帖最后由 excelvbafanes 于 2009-2-2 21:58 编辑 ]

mosforyou

楼上的逗人玩吗，弄个图片上来怎么查找你的问题所在啊！

itxianfeng

上传的图片并不能解决你的问题，应该上传你的公式或是表格

excelvbafanes

Sub auto_open()
Attribute auto_open.VB_ProcData.VB_Invoke_Func = " \n14"
    Caption
    average2
    Sheet1ontime
    daysave
End Sub

Sub Caption()
Attribute Caption.VB_ProcData.VB_Invoke_Func = " \n14"
    Dim fname As String
    Dim sday As String
    Dim smonth As String
    Dim syear As String
    'Sheets(1).Range("B5:T28").ClearContents
    'Sheets(2).Range("B5:AG28").ClearContents
    sday = Trim(Str(Day(Date)))
    If Len(sday) = 1 Then
        sday = " " + sday
    End If
    smonth = Trim(Str(Month(Date)))
    If Len(smonth) = 1 Then
        smonth = " " + smonth
    End If
    syear = Trim(Str(Year(Date)))
   
'    Application.Caption = "净水厂"
    Application.Caption = "日报表"
    Application.WindowState = xlMaximized
    Sheets(1).Select
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "       " + syear + "年" + smonth + "月" + sday + "日"
    Sheets(2).Select
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "       " + syear + "年" + smonth + "月" + sday + "日"
    Sheets(2).Select
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "       " + syear + "年" + smonth + "月" + sday + "日"
    Sheets(1).Select
    Cells(1, 1).Select
End Sub

Sub daysave()
Attribute daysave.VB_ProcData.VB_Invoke_Func = " \n14"
    Application.OnTime TimeValue("0:00:30"), "save_month_as"
    Application.OnTime TimeValue("0:01:00"), "save_copy_as"
End Sub

Sub save_copy_as()
Attribute save_copy_as.VB_ProcData.VB_Invoke_Func = " \n14"
    Dim fname As String
    Dim sday As String
    Dim smonth As String
    Dim syear As String
    sday = Trim(Str(Day(Date - 1)))
    If Len(sday) = 1 Then
        sday = "0" + sday
    End If
    smonth = Trim(Str(Month(Date - 1)))
    If Len(smonth) = 1 Then
        smonth = "0" + smonth
    End If
    syear = Trim(Str(Year(Date - 1) + 8))
    fname = "c:\project\report\" + syear + smonth + sday + ".xls"
    Sheets(1).Select
    Workbooks.Open(filename:="c:\project\report\DAY1_TMP.XLS").RunAutoMacros which:=xlAutoOpen

'Sheet 1 operation
    Windows("day1.XLS").Activate
    Sheets(1).Select
    Range("B5:M31").Select
    Selection.Copy
    Windows("day1_TMP.XLS").Activate
    Sheets(1).Select
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Sheets(1).Select
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "       " + syear + "年" + smonth + "月" + sday + "日"
'Sheet 2 operation
    Windows("day1.XLS").Activate
    Sheets(2).Select
    Range("B5:O31").Select
    Selection.Copy
    Windows("day1_TMP.XLS").Activate
    Sheets(2).Select
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Sheets(2).Select
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "       " + syear + "年" + smonth + "月" + sday + "日"
'Sheet 3 operation
    Windows("day1.XLS").Activate
    Sheets(2).Select
    Range("P5:AG31").Select
    Selection.Copy
    Windows("day1_TMP.XLS").Activate
    Sheets(3).Select
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Sheets(3).Select
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "       " + syear + "年" + smonth + "月" + sday + "日"
'Day save and print operation
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs filename:=fname, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    Sheets(1).Select
    ActiveSheet.PrintOut
    Sheets(2).Select
    ActiveSheet.PrintOut
    Sheets(3).Select
    ActiveSheet.PrintOut
    ActiveWorkbook.Close
    Range("A1").Select
    Workbooks.Open(filename:="c:\project\report\TEMP1.XLS").RunAutoMacros which:=xlAutoOpen
    'Windows("day1.XLS").Activate
End Sub

mosforyou

Sub Caption()
，，，，，，，，，，，，，，，，

    Application.Caption = "净水厂"
   Application.Caption = "日报表"
    Application.WindowState = xlMaximized
    sheets(1).range("M1").Formula = Format(Date, "yyyy年mm月dd日")
    Sheets(2).Range("O1").Formula = Format(Date, "yyyy年mm月dd日")
    Sheets(2).Range("AG1").Formula = Format(Date, "yyyy年mm月dd日")
    Sheets(1).range("a1").Select
，，，，，，，，，，，，，，，，，
End Sub
其它过程中的也按这种方式去改
fname取值：
Dim fname As String
    Dim sday As String
    Dim smonth As String
    Dim syear As String
fname = Format(Date - 1, "yyyy年mm月dd日")
syear = Left(fname, 4)
smonth = Mid(fname, 6, 2)
sday = Mid(fname, 9, 2)
fname = "c:\project\report\" + syear + smonth + sday + ".xls"

daysave 应为
，，，，，，，，，，，，
  Application.OnTime now()+TimeValue("00:00:30"), "save_month_as"，true
    Application.OnTime now()+TimeValue("00:01:00"), "save_copy_as",true
，，，，，，，，，，，，
这个代码应在workbook_open事件中调用（call daysave)

要在其中的某些过程中再次启用该过程，如
sub  save_month_as
'''''''''''''''
call daysave
'''''''''''''''
end sub
要有终止ontime的过程，比如可在workbook_beforeclose事件中添加
Application.OnTime now()+TimeValue("00:00:30"), "save_month_as"，false
    Application.OnTime now()+TimeValue("00:01:00"), "save_copy_as",false
否则系统会很累的哦！

[ 本帖最后由 mosforyou 于 2009-2-3 00:08 编辑 ]