Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VBScript
Hi
I am reading data from a recordset and writing it into an excel file. The problem is when i try to save the workbook using the SaveAs method the file is not saved and the asp page hangs until it generates a timeout . The code is shown below:
 
Set SURVEY_APP_CONNECTION = Server.CreateObject("ADODB.Connection")
 
   SURVEY_APP_CONNECTION= "DBQ=C:\Development\Websites\surveys.sawebdesign\db\SelectSurveyASPAdvanced.mdb;Driver={Microsoft Access Driver (*.mdb)};"
	Set rsExport = Server.CreateObject("ADODB.Recordset")
	
		dim strSQL
 
		Set xl = CreateObject("Excel.Application")
		'Set xlBk = xl.Workbooks.Add("C:\\template.xlsx")
       xl.Visible=True	
     Set xlBk =  xl.Workbooks.Add
      
 
        stop
	                    
            strSQL = "SELECT sur_response.response_id As ResponseID ,sur_item.item_alias As ItemAlias," & _
            "sur_item.item_text As ItemText, sur_response_answer.other_text, sur_item.other_text As ItemOtherText," & _
 
            "other_yn, sur_response_answer.answer_text As ResponseAnswerText, " & _
            "sur_item_answer.answer_text As ItemAnswerText, subitem_text " & _
            "FROM (((((sur_survey INNER JOIN sur_survey_to_item_mapping ON " & _
            "sur_survey.survey_id = sur_survey_to_item_mapping.survey_id)" & _
            " INNER JOIN sur_item ON sur_survey_to_item_mapping.item_id = sur_item.item_id)" & _
            " INNER JOIN ((sur_response INNER JOIN sur_response_answer ON  " & _
            "sur_response.response_id = sur_response_answer.response_id)" & _
            " LEFT JOIN sur_user ON sur_response.username = sur_user.username)" & _
            " ON sur_item.item_id = sur_response_answer.item_id)" & _
            " LEFT JOIN sur_item_answer ON sur_response_answer.answer_id = sur_item_answer.answer_id)" & _
            " LEFT JOIN sur_subitem ON sur_response_answer.subitem_id = sur_subitem.subitem_id) " & _
            " left JOIN sur_email_address ON sur_response.email_address_id = sur_email_address.email_address_id " & _
            "WHERE sur_survey.survey_id = 154"
 
						  rsExport.activeconnection=SURVEY_APP_CONNECTION
                          rsExport.CursorType=2
                          rsExport.Locktype=3
						
							rsExport.Open	strSQL
							If Not rsExport.EOF or  rsExport.BOf Then
			
			
		
                        With xlbk.Worksheets(1)
 
                            For i = 4 To rsExport.Fields.Count - 1
	
                                .Cells(1, i +1) = rsExport.Fields(i).Name
		                      
                           Next
                             .Cells(i-2, 1).CopyFromRecordset rsExport
                            End With
                        dim filename
                            filename="C:\dump\template2.xlsx"
 
                                 xl.Visible=True
                               ' xl.SaveWorkspace(filename)
                                
  
                            xlBk.ActiveWorkbook.SaveAs filename
                       
 
                          rsExport.close
                       xl.Quit
                        set xl= Nothing
                        set xlbk=nothing
                        set rsExport=nothing
                        stop
 
                        else 
               MsgBox "No Records"
		end if	
			
 
I have changed all the folder and file permissions to allow any user to edit but still its not saving . Please help
Posted 27-Jan-12 0:44am
Edited 27-Jan-12 0:50am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

i had to add the the following line .
 
  xl.ActiveWorkbook.Close savechanges=true
 

its now working
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 319
1 Kornfeld Eliyahu Peter 265
2 Maciej Los 249
3 BillWoodruff 205
4 Sergey Alexandrovich Kryukov 175
0 OriginalGriff 5,107
1 DamithSL 4,157
2 Maciej Los 3,595
3 Kornfeld Eliyahu Peter 3,450
4 Sergey Alexandrovich Kryukov 2,741


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 27 Jan 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100