<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%
'FUNCTION:(TN) QUERY DATA FROM ACCESS DB AND INSERT THEM INTO EXCEL WHICH CREATED NEWLY BY FSO OBJECT
dim msg,exlFileName
msg=""
'exlFileName=trim(request.Form("weFile01"))  'get file name from client
exlFileName="ExcelFileName001"
err.clear 

if exlFileName<>"" then
    '--------------------------------------------------------
    'Connect ACCESS DB and then query data from table
    dim acDBName,objDB,strConn
    acDBName="DBS/DB01.mdb"
    strConn="DRIVER={microsoft access driver (*.mdb)};UID=;PWD=;DBQ="&server.MapPath(acDBName)
    set objDB=server.CreateObject("adodb.connection")
    objDB.open strConn
    
    dim objRS,qSQL
    qSQL="select * from pnAddress"
    set objRS=server.CreateObject("adodb.recordset")
    objRS.open qSQL,objDB,1,1
    '--------------------------------------------------------
    
    '--------------------------------------------------------
    'Create EXCEL file and write (query)data to it
    if objRS.bof and objRS.eof then
        msg="<font color=""red"">No data in ACCESS DB, and not create EXCEL file.</font>"
    else
        dim objFSO,objExcel,saveExlFileName,strTemp,tempA,i
        
        'create EXCEL file name include path
        saveExlFileName=""
        saveExlFileName="Excels/"&exlFileName&year(now())    '可以使用程序生成保存的路径或文件夹
        if len(month(now()))<2 then
            saveExlFileName=saveExlFileName&"0"&month(now())
        else
            saveExlFileName=saveExlFileName&month(now())
        end if
        if len(day(now()))<2 then
            saveExlFileName=saveExlFileName&"0"&day(now())
        else
            saveExlFileName=saveExlFileName&day(now())
        end if
        if len(hour(now()))<2 then
            saveExlFileName=saveExlFileName&"0"&hour(now())
        else
            saveExlFileName=saveExlFileName&hour(now())
        end if
        if len(minute(now()))<2 then
            saveExlFileName=saveExlFileName&"0"&minute(now())
        else
            saveExlFileName=saveExlFileName&minute(now())
        end if
        saveExlFileName=saveExlFileName&".xls"
        
        on error resume next
        'create FSO and create EXCEL file, exist then delete and create it
        set objFSO=server.CreateObject("scripting.FileSystemObject")
        if objFSO.fileExists(server.MapPath(saveExlFileName)) then
            objFSO.deleteFile(server.MapPath(saveExlFileName))