Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting into Excel File

Status
Not open for further replies.
Enkrypted

Enkrypted

Technical User
Sep 18, 2002
663
US
I'm trying to add an export function on to our intranet. I currently have it setup to display the export button only after the report search has been run. If you click on the export button after the search has been run, it calls another ASP page with the following:

Code: 
<%

Response.ContentType ="Application/vnd.excel"
Response.AddHeader "Content-Disposition", "attachment; filename=qireport.xls"

%>

I am running 2003 SBE and SQL Server. If there is any other information needed, let me know.
It will prompt you to save the excel file on your computer. The problem I am running into is getting the generated report information into the excel file. Was hoping someone might be able to help me out. I will post the code in hopes it might help:

Code: 
<%
If QIRGetDatesSubmitted = 1 THEN
	Select case Request("QIRType")
		CASE "newqi"
			SQL = "SELECT * FROM ViewQISheetsDetail WHERE (AppTime IS NOT NULL) "
			SQL = SQL & "AND (AssignedTo = '" & QIRAssignedTo & "') "
			SQL = SQL & "AND (AppTime >= '" & QIRStartDate & " 12:01 AM') "
			SQL = SQL & "AND (AppTime <= '" & QIREndDate & " 11:59 PM') "
			SQL = SQL & "AND (QISheetCancelled IS NULL and QISheetCancelled <> '' or QISheetCancelled <> '1') ORDER BY AppTime"
		CASE "cancelled"
			SQL = "SELECT * FROM ViewQISheetsDetail WHERE (AppTime IS NOT NULL) "
			SQL = SQL & "AND (AppTime >= '" & QIRStartDate & " 12:01 AM') "
			SQL = SQL & "AND (AppTime <= '" & QIREndDate & " 11:59 PM') "
			SQL = SQL & "AND (QISheetCancelled = 1) ORDER BY AppTime"
		CASE "dead"
			SQL = "SELECT * FROM ViewFollowUpCalls WHERE "
			SQL = SQL & "((FirstCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
			SQL = SQL & "(FirstCallMadeOn <= '" & QIREndDate & " 11:59 PM') OR "
			SQL = SQL & "(SecondCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
			SQL = SQL & "(SecondCallMadeOn <= '" & QIREndDate & " 11:59 PM') OR "
			SQL = SQL & "(ThirdCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
			SQL = SQL & "(ThirdCallMadeOn <= '" & QIREndDate & " 11:59 PM') OR "
			SQL = SQL & "(FourthCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
			SQL = SQL & "(FourthCallMadeOn <= '" & QIREndDate & " 11:59 PM')) "
			SQL = SQL & "AND LeadIsDead = 1"
		End Select
	

	' get results for report
	alldata = getRecordSetToArray(SQL)
	
	' check that there is data present
	IF alldata(0,0) = "NO RECORDS FOUND" THEN
		' no records
		%>
		<table cellpadding="0" cellspacing="0" border="0" width="100%">
			<tr>
				<TD><font class="small"><b>No QI Reports Found</b></font></TD>
			</tr>
		</table>
		<%
	ELSEIF alldata(0,0) = "INVALID SQL QUERY" THEN
		' invalid SQL
		%>
		<table cellpadding="0" cellspacing="0" border="0" width="100%">
			<tr>
				<TD><font class="small"><b>There is an error in the search submitted: <br><%=SQL %><br></b></font></TD>
			</tr>
		</table>		
		<%
	ELSE	
		' build report data
		strReportData = mergeFormattedStringWithArray(strFormatReportData,alldata)
		
		%>
		<%
		Const cID = 0
		Const cCustomerID = 1
		Const cCreationDate = 2
		Const cScopeOfWork = 3
		Const cCallBackConfirmed = 4
		Const cAppTime = 5
		Const cAssignedTo = 6
		Const cExpr1 = 7
		Const cTitle = 8
		cFirstName = 9
		cLastName = 10
		if Request("QIRType") = "dead" Then
			cFirstName = 8
			cLastName = 9
		End if
		Const cAddress1 = 11
		Const cAddress2 = 12
		Const cCity = 13
		Const cState = 14
		Const cZip = 15
		Const cSubdivision = 16
		Const cEmail = 17
		Const cHomePhone = 18
		Const cWorkPhone = 19
		Const cCellPhone = 20
		Const cDirections = 21
		Const cCustomerCreationDate = 22
		Const cHeardAboutUsID = 23
		Const cHeardAboutUsCustomerID = 24
		Const cDirectMail = 25
		Const cSNP = 26
		Const cYellowPages = 27
		Const cReferral = 28
		Const cReferralText = 29
		Const cNeighbor = 30
		Const cNeighborText = 31
		Const cAngies = 32
		Const cMike = 33
		Const cInternet = 34
		Const cPreviousCust = 35
		Const cServMag = 36
		Const cBBB = 37
		Const cSawSign = 38
		Const cSawTruck = 39
		Const cOther = 40
		Const cOtherText = 41
		Const cQISheetCancelled = 42
		Const cQISheetCancelledBy = 43
		%>
		
		<TABLE cellpadding="0" cellspacing="0" border="0" width="100%">
		<TR>
		<TD><FONT class="small"><B>Lastname, Firstname</TD>
		<TD>&nbsp;&nbsp;&nbsp;&nbsp;</TD>
		<% If Request("QIRType") <> "dead" then %>
		<TD><FONT class="small"><B>Appointment Time</TD>
		<TD>&nbsp;&nbsp;&nbsp;&nbsp;</TD>
		
		<TD><FONT class="small"><B>How They Heard About Us</TD>
		<% end if %>
		</TR>
		
		<% 
		for x = 0 to Ubound(alldata,2)
		IF BGCOLOR="#FFFFFF" THEN
		  BGCOLOR="#E1ECF7"
		Else
		  BGCOLOR="#FFFFFF"
		END IF
		
		Heard = ""
		on error resume next
		If alldata(cDirectMail, x) THEN
		  Heard = Heard & "Direct Mail<BR>"
		END IF
		
		If alldata(cSNP, x) THEN
		  Heard = Heard & "SNP<BR>"
		END IF
		
		If alldata(cYellowPages, x) THEN
		  Heard = Heard & "Yellow Pages<BR>"
		END IF
		
		If alldata(cReferral, x) THEN
		  Heard = Heard & "Referral<BR>"
		END IF
		
		If Len(trim(alldata(cReferralText, x))) > 0  THEN
		  Heard = Heard & alldata(cReferralText, x) & "<BR>"
		END IF
		
		If alldata(cNeighbor, x) THEN
		  Heard = Heard & "Neighbor<BR>"
		END IF
		
		If Len(trim(alldata(cNeighborText, x))) > 0  THEN
		  Heard = Heard & alldata(cNeighborText, x) & "<BR>"
		END IF
		
		If alldata(cAngies, x) THEN
		  Heard = Heard & "Angies<BR>"
		END IF
		
		If alldata(cMike, x) THEN
		  Heard = Heard & "Mike<BR>"
		END IF
		
		If alldata(cInternet, x) THEN
		  Heard = Heard & "Internet<BR>"
		END IF
		
		If alldata(cPreviousCust, x) THEN
		  Heard = Heard & "Previous Cust<BR>"
		END IF
		
		If alldata(cServMag, x) THEN
		  Heard = Heard & "Serv Mag<BR>"
		END IF
		
		If alldata(cBBB, x) THEN
		  Heard = Heard & "BBB<BR>"
		END IF
		
		If alldata(cSawSign, x) THEN
		  Heard = Heard & "Saw Sign<BR>"
		END IF
		
		If alldata(cSawTruck, x) THEN
		  Heard = Heard & "SawTruck<BR>"
		END IF
		
		If alldata(cOther, x) THEN
		  Heard = Heard & "Other<BR>"
		END IF
		
		If Len(trim(alldata(cOtherText, x))) > 0  THEN
		  Heard = Heard & alldata(cOtherText, x) & "<BR>"
		END IF
		on error goto 0
		ThisAppTime = FormatDate(alldata(cAppTime, x), "n/j/y") & "&nbsp;" & FormatDate(alldata(cAppTime, x), "g:i A")
		
		%>
		
		<TR bgcolor="<%=BGCOLOR%>">
		<TD colspan="5"><IMG SRC="images/spacer.gif" width="1" height="4"></TD>
		</TR>
		
		<TR bgcolor="<%=BGCOLOR%>">
		<TD><FONT class="normal"><%=alldata(cLastName, x)%>, <%=alldata(cFirstname, x)%></TD>
		<TD>&nbsp;&nbsp;&nbsp;&nbsp;</TD>
		<% If Request("QIRType") <> "dead" then %>
		<TD><FONT class="normal"><%=ThisAppTime%></TD>
		<TD>&nbsp;&nbsp;&nbsp;&nbsp;</TD>
		
		<TD><FONT class="normal"><%=Heard%></TD>
		<% End If %>
		</TR>
		
		<TR bgcolor="<%=BGCOLOR%>">
		<TD colspan="5"><IMG SRC="images/spacer.gif" width="1" height="4"></TD>
		</TR>
		
		<% Next %>
		
		</TABLE>
		<BR>
<form name="form1" method="post" action="exportinfo.asp">
  <input type="submit" name="Submit" value="Export to Excel">
</form>
<BR><BR><BR><BR><BR><BR>
		<% 
	' end of check for data
	END IF
' end of check for submitted form
END IF 
%>

<Script language="javascript">
function CheckWholeForm()
{

}

function SubmitFormYear()
{
  var today = new Date()
  var month = today.getMonth() + 1
  var day = today.getDate()
  var year = today.getFullYear()
  var s = "/"

  var jsEndDate = month + s + day + s + year

  var jsFullStartDate = DateAdd('yyyy', -1, jsEndDate)

  var Smonth = jsFullStartDate.getMonth() + 1
  var Sday = jsFullStartDate.getDate()
  var Syear = jsFullStartDate.getFullYear()

  var jsStartDate = Smonth + s + Sday + s + Syear

  document.GetDates.QIRStartDate.value = jsStartDate;
  document.GetDates.QIREndDate.value = jsEndDate;
  document.GetDates.submit();
}


function SubmitFormMonth()
{
  var today = new Date()
  var month = today.getMonth() + 1
  var day = today.getDate()
  var year = today.getFullYear()
  var s = "/"

  var jsEndDate = month + s + day + s + year

  var Smonth = today.getMonth()
  var Sday = today.getDate()
  var Syear = today.getFullYear()

  //alert('Smonth=' + Smonth);
  //alert('Sday=' + Sday);

  if ((Smonth == 2) && (Sday > 28))
  {
  Sday = 28;
  }


//30 days hath September (9), April (4), June(6) and November(11)


  if (((Smonth == 9)||(Smonth == 4)||(Smonth == 6)||(Smonth == 11)) && (Sday > 30))
  {
  Sday = 30;
  }


  var jsStartDate = Smonth + s + Sday + s + Syear

  document.GetDates.QIRStartDate.value = jsStartDate;
  document.GetDates.QIREndDate.value = jsEndDate;
  document.GetDates.submit();
}


function SubmitFormWeek()
{
  var today = new Date()
  var month = today.getMonth() + 1
  var day = today.getDate()
  var year = today.getFullYear()
  var s = "/"

  var jsEndDate = month + s + day + s + year

  var jsFullStartDate = DateAdd('ww', -1, jsEndDate)

  var Smonth = jsFullStartDate.getMonth() + 1
  var Sday = jsFullStartDate.getDate()
  var Syear = jsFullStartDate.getFullYear()

  var jsStartDate = Smonth + s + Sday + s + Syear

  document.GetDates.QIRStartDate.value = jsStartDate;
  document.GetDates.QIREndDate.value = jsEndDate;
  document.GetDates.submit();
}

function SubmitFormDay()
{
  var today = new Date()
  var month = today.getMonth() + 1
  var day = today.getDate()
  var year = today.getFullYear()
  var s = "/"

  var jsEndDate = month + s + day + s + year

  var jsFullStartDate = DateAdd('d', -1, jsEndDate)

  var Smonth = jsFullStartDate.getMonth() + 1
  var Sday = jsFullStartDate.getDate()
  var Syear = jsFullStartDate.getFullYear()

  var jsStartDate = Smonth + s + Sday + s + Syear

  document.GetDates.QIRStartDate.value = jsStartDate;
  document.GetDates.QIREndDate.value = jsEndDate;
  document.GetDates.submit();
}

</SCRIPT>

<script type="text/javascript" src="b.js"></script>

When the report is run for each indiviual rep, it will provide us with the customers first and last name, the appointment time, and how they heard about us. I only need to gather information on name and how they heard about us and put that into the excel file. If any of you have any ideas about how I can pull that information into the excel file it would be greatly appreciated.

Enkrypted
A+
 
Sort by date Sort by votes
In VB the

[excel object].Range[somerange].CopyFromRecordset rsSomeRecordset

It splats the recordset right into an excel sheet, which can be given a name on a server share and the response then redirected or a new explorer window opened with that .xls file.

Not sure if it works in asp, but it's a thought,
--Jim


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
READ TEXT FILE BASED ON VARIABLE 1
Replies
2
Views
204
guitarzan
guitarzan
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
185
penguinspeaks
penguinspeaks
DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
171
DiDYRO_
DiDYRO_
Xsi
  • Locked
  • Question
Store data into an array
Replies
3
Views
104
ChrisHirst
ChrisHirst
gcoleman1
  • Locked
  • Question
Insert into Request.form field when empty
Replies
2
Views
103
gcoleman1
gcoleman1

Part and Inventory Search

Sponsor

Back
Top