Function switchexcelinfo (xlsfilename) '#########' Power by tangn.com '##################################################################################################################### i, j, kDim ExcelConnDim ExcelFileDim objExcelAppDim objExcelBookDim bgColorDim ErEr = FalsexlsStr = "" If xlsFileName = "" Then Er = TrueEnd IfIf Er = False AND UCase (Mid (file.fileName, InStrRev (file.filename, "."), Len (File.FileName)))) <> "xls" Then Er = false
If Er = False ThenExeclFile = Server.MapPath (xlsFileName) Set objExcelApp = CreateObject ( "Excel.Application") objExcelApp.QuitSet objExcelApp = CreateObject ( "Excel.Application") objExcelApp.QuitobjExcelApp.DisplayAlerts = false 'no warning objExcelApp.Application .Visible = false 'not display interface objExcelApp.WorkBooks.Open (ExeclFile) set objExcelBook = objExcelApp.ActiveWorkBook reDim arrSheets (objExcelBook.Sheets.Count) For i = 1 to objExcelBook.Sheets.CountarrSheets (i) = objExcelBook.Sheets (i ) .NameNextobjExcelApp.QuitSet objExeclApp = nothingSet ExcelConn = Server.CreateObject ( "ADODB.Connection") ExcelDriver = "Driver = {Microsoft Excel Driver (* .xls)}; DBQ =" & ExeclFileExcelConn.Open ExcelDriverSet rs = Server.CreateObject ( "Adodb.recordset") for i = 1 to ubound (arrsheets) SQL = "SELECT *" & Arrsheets (i) & "$] '' displaying the name of each worksheet '' 'XLSSTR = Xlsstr &" & " Arrsheets (I) & "Xlsstr = XLSSTR &"
"
Rs.open SQL, ExcelConn, 1, 1
K = 1
While Not Rs.eof
IF K MOD 2 <> 0 Then Bgcolor = "BGColor = # e0e0e0" else bgcolor = ""
XLSSTR = XLSSTR & "
"
Xlsstr = Xlsstr & "& RS (0) &" for j = 0 to rs.fields.count-1
Xlsstr = Xlsstr & "& RS (j) &" "
NEXT
XLSSTR = Xlsstr & ""
rs.movenext
K = K 1
Wend
XLSSTR = XLSSTR & "
"
Rs.close
NEXT
Excelconn.Close
SET ExcelConn = Nothing
END IF
Switchexcelinfo = xlsstr
END FUNCTION
