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("ACCOU NT")
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

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning. (Rich Cook)

"Agnes" <ag***@dynamictech.com.hk> schreef in bericht
news:#E**************@TK2MSFTNGP10.phx.gbl...

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("ACCOU NT")
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

[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 Proost" <pp*****@nospam.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:%2******************@TK2MSFTNGP15.p hx.gbl...

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

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning. (Rich Cook)

"Agnes" <ag***@dynamictech.com.hk> schreef in bericht
news:#E**************@TK2MSFTNGP10.phx.gbl...

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("ACCOU NT")
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]

Hi,

maybe this can help you:

http://support.microsoft.com/default...b;en-us;317109

Greetz Peter
"Agnes" <ag***@dynamictech.com.hk> schreef in bericht
news:ub**************@TK2MSFTNGP15.phx.gbl...

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 Proost" <pp*****@nospam.hotmail.com>

¼¶¼g©ó¶l¥ó·s»D:%2******************@TK2MSFTNGP15.p hx.gbl...

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

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. (Rich Cook)

"Agnes" <ag***@dynamictech.com.hk> schreef in bericht
news:#E**************@TK2MSFTNGP10.phx.gbl...

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("ACCOU NT")
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