[Excel] 匯出檔案依照日期存檔名

資料來源
http://www.programmer-club.com/showSameTitleN/access/3368.html


用 VBA

Function Export()
On Error GoTo Export_Err


    Let strSQL = "select * into [EXCEL 8.0;DATABASE=C:\MyFile" & Format(Now, "yyyymmdd") & ".XLS].[MySheet] from MyTable"

    DoCmd.RunSQL strSQL, -1


Export_Exit:
    Exit Function

Export_Err:
    MsgBox Error$
    Resume Export_Exit

End Function

------------------------


1. >>Excel格式不會變, 比較漂亮 = ="
參考 http://www.programmer-club.com/pc2020v5/forum/ShowSameTitleN.asp?URL=N&board_pc2020=access&id=2547

多作一些Excel 的格式修正即可(有關Excel 巨集部分...可用錄製協助)
多多參考 http://www.excelhelp.net 網站

    Dim xls As Excel.Application
   Dim ps3 As Object

    Set x1app = Nothing
    Set x1app = CreateObject("Excel.Application")

    x1app.Workbooks.Open "C:\C:\MyFile" & Format(Now, "yyyymmdd") & ".XLS"
    x1app.Visible = True
    Set ps3 = GetObject(, "Excel.Application")
    Set ps3 = GetObject("C:\C:\MyFile" & Format(Now, "yyyymmdd") & ".XLS")

    ps3.ActiveSheet.Rows(1).Font.Name = "Verdana"
    ps3.ActiveSheet.Rows(1).Font.Bold = True
    ps3.ActiveSheet.Rows(1).Interior.ColorIndex = 40

   (記的要引用項目 : Microsoft Excel xx Object...)

2. >>若是檔名已經存在的話, 不能更新檔案, 程式會中斷.
    加一段 程式碼 ....刪除舊 Excel file 

    On Error Resume Next
    Kill "C:\C:\MyFile" & Format(Now, "yyyymmdd") & ".XLS"
    On Error GoTo 0