Page 1 of 1

Writing the data to Excel Sheet

Posted: Tue Oct 05, 2010 1:41 pm
by dal
Hi Team,

I am using the following code to write the data to Excel Sheet, but the Excel process is still ON (in Task Manager) because of it Excel Sheet opens as ReadOnly, which doesn't allow me to write the another data successfully as the Sheet opened as ReadOnly mode.

Have done more analysis by accessing the .Net Forum as well, But no success.
Can you please guide me where the disconnect is?

Attaching the code here....

Code: Select all

Public Shared Sub ExcelWrite (ByVal ExcelPath As String, ByVal SheetName As String, ByVal RowNo As Integer, ByVal ColumnName As String, ByVal WriteValue As String)
						
	        Dim xcApp As New Excel.Application
	        Dim xcWB As Excel.Workbook = xcApp.Workbooks.Open(ExcelPath)
	        Dim xcWS As Excel.Worksheet = CType(xcWB.Worksheets(SheetName), Excel.Worksheet)
	        Dim xcRange As Excel.Range
			Dim columnCount As Integer
						
			Try
				xcRange = xcWS.UsedRange
				
				For columnCount = 1 To xcRange.Columns.Count
					Dim cellData As String
					
					xcRange = XcWS.Cells(1, columnCount)
					cellData = xcRange.Text
					If cellData = ColumnName Then
						Exit For
					End If
				Next
				
				xcRange = xcWS.Cells(RowNo,columnCount)
				xcRange.Value = WriteValue
				xcWB.Save()
				
			Catch e As Exception
				Report.Error("Writing the Data to Excel Sheet - Unexpected Exception Occured"+ e.ToString())
			End Try
			
		    'clean up
		    xcRange = Nothing
		    xcWS = Nothing
	        xcWB.Close()
	        xcWB = Nothing
	        xcApp.Quit()
	        xcApp = Nothing
	        
	       TerminateExcel()	       
	       
  		End Sub  		
  	      		  		
		Private Shared Sub TerminateExcel() 

			Dim ClassName As String = "XLMain"
			Dim WindowHandle As Int32
			Dim ReturnVal As Int32
			Const WM_QUIT = &H12

			Do	

				WindowHandle = FindWindow(ClassName, Nothing)
				If WindowHandle Then
					ReturnVal = PostMessage(WindowHandle, WM_QUIT, 0, 0)
				End If
			Loop Until WindowHandle = 0
		
		End Sub

Regards,
Dal...

Re: Writing the data to Excel Sheet

Posted: Tue Oct 05, 2010 2:27 pm
by sdaly
This is how I kill/close processes... Give it a try...

Public Sub checkAndKillProcess(processName As String, Optional gracefullKill as Boolean = False)

Dim processesToKill As Process() = process.GetProcessesByName(processName)
For Each process As Process In processesToKill

If gracefullKill Then
report.Info("Process", "Gracefull kill " & process.ToString)
process.CloseMainWindow

process.WaitForExit(10000)
If Not process.HasExited Then
report.Info("V3", "Process never ended after 10 secs so killed")
process.Kill
End If

Else
report.Info("Process", "Killing " & process.ToString)
process.Kill
End If
Next

End Sub

Re: Writing the data to Excel Sheet

Posted: Wed Oct 06, 2010 8:41 am
by dal
It didn't work...

I am able to see the Process (EXCEL.EXE) still running in Task Manager, because of it the Excel sheet opened as Read-only. May be this is due to the Excel Sheet is not visible as one of the Document in System.
But my case it will open and update in the background by using the following code...

Kindly suggest some other way of doing it.

Code: Select all

Public Shared Sub ExcelWrite (ByVal ExcelPath As String, ByVal SheetName As String, ByVal RowNo As Integer, ByVal ColumnName As String, ByVal WriteValue As String)
						
			Dim xcApp As New Excel.Application
	        Dim xcWB As Excel.Workbook = xcApp.Workbooks.Open(ExcelPath)
	        Dim xcWS As Excel.Worksheet = CType(xcWB.Worksheets(SheetName), Excel.Worksheet)
	        Dim xcRange As Excel.Range
			Dim columnCount As Integer
						
			Try
				xcRange = xcWS.UsedRange
				
				For columnCount = 1 To xcRange.Columns.Count
					Dim cellData As String
					
					xcRange = XcWS.Cells(1, columnCount)
					cellData = xcRange.Text
					If cellData = ColumnName Then
						Exit For
					End If
				Next
				
				xcRange = xcWS.Cells(RowNo,columnCount)
				xcRange.Value = WriteValue
				xcWB.Save()
				
			Catch e As Exception
				Report.Error("Writing the Data to Excel Sheet - Unexpected Exception Occured"+ e.ToString())
			End Try
			
		    'clean up
		    xcRange = Nothing
		    xcWS = Nothing
	        xcWB.Close()
	        xcWB = Nothing
	        xcApp.Quit()
	        xcApp = Nothing
	        
	       checkAndKillProcess("EXCEL")	       
	       
  		End Sub

Code: Select all

  		Public Shared Sub checkAndKillProcess(processName As String, Optional gracefullKill as Boolean = False)

			Dim processesToKill As Process() = process.GetProcessesByName(processName)
			For Each process As Process In processesToKill
			
			If gracefullKill Then
			report.Info("Process", "Gracefull kill " & process.ToString)
			process.CloseMainWindow
			
			process.WaitForExit(10000)
			If Not process.HasExited Then
			report.Info("V3", "Process never ended after 10 secs so killed")
			process.Kill
			End If
			
			Else
			report.Info("Process", "Killing " & process.ToString)
			process.Kill
			End If 
			Next
			
		End Sub

Re: Writing the data to Excel Sheet

Posted: Wed Oct 06, 2010 9:01 am
by sdaly
Hmm not sure why, killing the process should get rid of "EXCEL" from task manager...

Are you opening and saving the excel sheet everytime you are writing to it? Cant you just create an array or list of the data then write it once?

Take a look at - http://www.ranorex.com/forum/writing-ex ... t1605.html

Re: Writing the data to Excel Sheet

Posted: Wed Oct 06, 2010 9:55 am
by dal
As per the sample code given I wrote a single data to Excel and just saved once, I have not tried with multiple times. Even then the EXCEL is not been killed it TM, and opened as ReadOnly.

I am thinking of writing those dynamic data captured into an XML and read the data from it.

Regadrs,
Dal...