After export to Excel, that excel cannot open

24 Feb 2006 2:27 AM

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

24 Feb 2006 3:36 PM

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)

Show quoteHide quote

"Agnes" <ag***@dynamictech.com.hk> schreef in bericht
news:#EuyjnOOGHA.1132@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("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
>
>

27 Feb 2006 6:49 AM

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

Show quoteHide quote

"Peter Proost" <pproost@nospam.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:%23H%23nmhVOGHA.2***@TK2MSFTNGP15.phx.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:#EuyjnOOGHA.1132@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("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
>>
>>
>
>

27 Feb 2006 2:53 PM

Peter

Hi,

maybe this can help you:

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

Greetz Peter

"Agnes" <ag***@dynamictech.com.hk> schreef in bericht
news:ubyc1n2OGHA.3460@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" <pproost@nospam.hotmail.com>

> > 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:#EuyjnOOGHA.1132@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("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
> >>
> >>
> >
> >
>
>