Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
size of excel file
here is code
<%@ Language=VBScript % <% Response.Expires = 0 Response.Buffer = True Response.ContentType = "application/vnd.ms-excel" Server.ScriptTimeout = 5000 dim Period, PeriodName Period = Request.Form("cmbPeriod") PeriodName = Request.Form("hdnPeriodName") DataName = "Raw Price Data" dim Conn, connStr connStr = Application("Connstr") set conn = Server.CreateObject("ADODB.Connection") Conn.ConnectionTimeout = 1000 Conn.Open connStr Dim objSS, objConst,SheetList,SheetDelta,SheetAdhoc,SheetGene ric,SheetBefore,SheetA fter, iSheetCnt Dim oRange, oRange2 Set objSS = CreateObject("OWC10.Spreadsheet") Set objConst = objSS.Constants iSheetCnt = 1 set SheetList = objSS.Worksheets(iSheetCnt) SheetList.Name = "RawPriceData" dim rowCounter rowCounter = 1 SheetList.Range("A" & rowCounter).Value = "Fiscal Year" SheetList.Range("B" & rowCounter).Value = "Fiscal Week" SheetList.Range("C" & rowCounter).Value = "Tool_Num" SheetList.Range("D" & rowCounter).Value = "DW_SKU_Num" SheetList.Range("E" & rowCounter).Value = "Site_Name" SheetList.Range("F" & rowCounter).Value = "List_Price" SheetList.Range("G" & rowCounter).Value = "Discount" SheetList.Range("H" & rowCounter).Value = "Rebate" SheetList.Range("I" & rowCounter).Value = "Promo_Price" SheetList.Range("J" & rowCounter).Value = "Shipping" SheetList.Range("K" & rowCounter).Value = "Tax" SheetList.Range("L" & rowCounter).Value = "Net_price" SheetList.Range("M" & rowCounter).Value = "Hitouch_Flag" SheetList.Range("A" & rowCounter & ":" & "M" & rowCounter).Font.Bold = True dim strSql, Rs set Rs = Server.CreateObject("ADODB.Recordset") strSql = "SELECT DP.FISCAL_WEEK, P.PERIOD_NUM, P.SKU_NUM, SKU_MASTER.DW_SKU_NUM, Sm.SITE_NAME, P.LIST_PRICE, P.DISCOUNT, P.REBATE," & _ " P.PROMO_PRICE, P.SHIPPING, P.TAX, P.NET_PRICE, P.HITOUCH_FLAG " & _ " FROM PRICE_DATA P INNER JOIN SITE_MASTER Sm ON P.SITE_ID = SM.SITE_ID " & _ " INNER JOIN SKU_MASTER ON P.SKU_NUM = SKU_MASTER.SKU_NUM " & _ " INNER JOIN CATEGORY_MASTER CM ON SKU_MASTER.CATEGORY_ID = CM.CATEGORY_ID " & _ " INNER JOIN SUBCATEGORY_MASTER SCM ON SKU_MASTER.SUBCATEGORY_ID = SCM.SUBCATEGORY_ID" & _ " INNER JOIN DATA_PERIOD_MASTER DP ON DP.PERIOD_NUM = P.PERIOD_NUM " & _ " Where P.Period_num = " & Period & _ " ORDER BY P.HITOUCH_FLAG, CATEGORY_NAME, SUBCATEGORY_NAME, P.SKU_NUM, Sm.SITE_NAME " 'Modified on August 04, 2003 Santosh Patil ' 'strSql = "SELECT P.PERIOD_NUM, P.SKU_NUM, SKU_MASTER.DW_SKU_NUM, Sm.SITE_NAME, P.LIST_PRICE, P.DISCOUNT, P.REBATE," & _ ' " P.PROMO_PRICE, P.SHIPPING, P.TAX, P.NET_PRICE, P.HITOUCH_FLAG " & _ ' " FROM PRICE_DATA P INNER JOIN SITE_MASTER SM ON P.SITE_ID = SM.SITE_ID " & _ ' " INNER JOIN SKU_MASTER ON P.SKU_NUM = SKU_MASTER.SKU_NUM " & _ ' " INNER JOIN CATEGORY_MASTER CM ON SKU_MASTER.CATEGORY_ID = CM.CATEGORY_ID " & _ ' " INNER JOIN SUBCATEGORY_MASTER SCM ON SKU_MASTER.SUBCATEGORY_ID = SCM.SUBCATEGORY_ID" & _ ' " Where Period_num = " & Period & _ ' " AND SM.SITE_ACTIVE_FLAG = 1 " & _ ' " AND SKU_MASTER.SKU_ACTIVE_FLAG = 1 " & _ ' " AND CM.CATEGORY_ACTIVE_FLAG = 1 " & _ ' " AND CM.CATEGORY_ACTIVE_FLAG = 1 " & _ ' " ORDER BY P.HITOUCH_FLAG, CATEGORY_NAME, SUBCATEGORY_NAME, P.SKU_NUM, Sm.SITE_NAME " 'Response.Write strSql 'Response.End Rs.Open strSql, Conn ' " INNER JOIN CATEGORY_MASTER ON " if not rs.EOF then do while not Rs.EOF rowCounter = rowCounter + 1 if Rs("HITOUCH_FLAG") = true then HITOUCH_FLAG = "1" else HITOUCH_FLAG = "0" end if strFiscalWeek = trim(rs("FISCAL_WEEK")) SheetList.Range("A" & rowCounter).Value = mid(strFiscalWeek,1,4) SheetList.Range("B" & rowCounter).Value = mid(strFiscalWeek,5) SheetList.Range("C" & rowCounter).Value = trim(Rs("SKU_NUM")) ' coding for formatting number if DW_SKU_NUM is numeric strDW_SKU_NUM = trim(Rs("DW_SKU_NUM")) strDW_SKU_NUM = "'" & strDW_SKU_NUM SheetList.Range("D" & rowCounter).Value = strDW_SKU_NUM SheetList.Range("E" & rowCounter).Value = trim(Rs("SITE_NAME")) SheetList.Range("F" & rowCounter).Value = trim(Rs("LIST_PRICE")) SheetList.Range("G" & rowCounter).Value = trim(Rs("DISCOUNT")) SheetList.Range("H" & rowCounter).Value = trim(Rs("REBATE")) SheetList.Range("I" & rowCounter).Value = trim(Rs("PROMO_PRICE")) SheetList.Range("J" & rowCounter).Value = trim(Rs("SHIPPING")) SheetList.Range("K" & rowCounter).Value = trim(Rs("TAX")) SheetList.Range("L" & rowCounter).Value = trim(Rs("NET_PRICE")) SheetList.Range("M" & rowCounter).Value = HITOUCH_FLAG Rs.MoveNext loop end if objSS.Windows(1).DisplayRowHeadings = True objSS.Windows(1).DisplayColumnHeadings = True objSS.Windows(1).FreezePanes = False objSS.Windows(1).DisplayGridlines = True objSS.DisplayToolbar = False objSS.AutoFit = True 'SheetList.save "RawPriceData" Response.Write objSS.HTMLData set objSS = nothing conn.Close set conn= nothing % *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel File Size | Excel Worksheet Functions | |||
File Size With Macros Has Increased From Its Origina Size | Excel Discussion (Misc queries) | |||
Unusual File Size in Excel file | Excel Discussion (Misc queries) | |||
how to set sheet size to reduce file size | Excel Discussion (Misc queries) | |||
Unable to open excel file and when view the file size show as 1 KB | Excel Discussion (Misc queries) |