After export to Excel, that excel cannot open

[Agnes]

Dim dsExcelExport As New System.Data.DataSet
Dim daExcelExport As New System.Data.SqlClient.SqlDataAdapter

Dim Excel As New Excel.Application

Dim strExcelFile As String
Dim strFileName As String
dsExcelExport.Clear()
daExcelExport.SelectCommand = New SqlCommand
daExcelExport.SelectCommand.Connection =
dtsclass.DatabaseConnection.GetDbConnection("ACCOUNT")
daExcelExport.SelectCommand.CommandText = "select * from myInvoice"
daExcelExport.Fill(dsExcelExport)

Try
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

.Range("A1").Value = "BRANCHID"
.Range("B1").Value = "BILLCODE"
.Range("C1").Value = "BILLNAME"
.Range("D1").Value = "COCODE"
.Range("E1").Value = "CONAME"
.Range("F1").Value = "INVNO"


Dim dr As DataRow
Dim i As Integer = 2
For Each dr In dsExcelExport.Tables(0).Rows

.Range("A" & i.ToString).Value = dr("BRANCHID")
.Range("B" & i.ToString).Value = dr("BILLINGCODE")
.Range("C" & i.ToString).Value = dr("BILLINGNAME")
.Range("D" & i.ToString).Value = dr("COCODE")
.Range("E" & i.ToString).Value = dr("CONAME")
.Range("F" & i.ToString).Value = dr("INVNO")
i += 1
Next


strExcelFile = _pFilePath
.ActiveWorkbook().SaveAs(strExcelFile)
.ActiveWorkbook.Close()
End With
MessageBox.Show("File exported sucessfully.", "Exporting done",
MessageBoxButtons.OK, MessageBoxIcon.Information)
''NormalExit:
Excel.Quit()
Excel = Nothing

I think I can export the file sucesfully, However, I cannot open the excel
in the first Time, it is hang . I need to kill the process in Task Manager
and then open the excel again.
Does my procedure got anything wrong ??
thanks a lot

 

[Peter Proost]

Hi,

I once had the same problem, it has to do with how you close Excel, I do it
like this

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

' Start Excel and get Application object.
oXL = DirectCast(CreateObject("Excel.Application"),
Excel.Application)
oXL.Visible = False

' Get a new workbook.
oWB = oXL.Workbooks.Add
oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)

' Add the values
oSheet.Cells(row, column).Value = "Something"

' Make sure Excel is visible and give the user control
' of Excel's lifetime.
oSheet.SaveAs("c:\yourfile.xls")
oXL.Visible = True
oXL.UserControl = True

' Make sure that you release object references.
oSheet = Nothing
oWB = Nothing
oXL.Quit()
oXL = Nothing

I hope this hepls,

Greetz, Peter

 

[Agnes]

Thanks Peter,
I had changed all my code as yours .Howerver, After Excel is generated. I
still find an EXCEL.exe in my task manager . and I am fail to open the excel
until I delete the that task

 

[Peter]

Hi,

maybe this can help you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

Greetz Peter