Click here to Skip to main content
12,403,391 members (73,148 online)
Rate this:
 
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 26-Jan-12 23:44pm
Updated 26-Jan-12 23:50pm
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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 27 Jan 2012
Copyright © CodeProject, 1999-2016
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