Excel files won't open after export

  • Thread starter Thread starter Graeme
  • Start date Start date
G

Graeme

Hi

Got a wee problem after exporting dataset to XL I'd like some help on. Maybe
I'm not closing the XL object correctly (see code below) but after executing
this proc, my data goes to XL fine, but when I double-click on the XL file
I've just created OR ANY OTHER XL FILE, XL sort of hangs ... well the
toolbars and borders show at top and bottom of screen, but the spreadsheet
doesn't seem to load fully - like there's a large piece missing in the
middle, so most of the Desktop can be seen. Even rebooting doesn't seem to
help.

But if I open XL by first running the app (Start,Programs,Microsoft Excel)
then navigate to any XL file and open that way, no problem! So that's the
only way I can run XL at the moment, which is a bit frustrating.

A side issue: What's a good way to adjust below proc to automatically open
XL? Currently it's all done behind the scenes.

Thanks, Graeme

Private Sub bExportToXL_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles bExportToXL.Click
Dim oExcel As Object

Dim oBook As Object

Dim oSheet As Object

oExcel = CreateObject("Excel.Application")

oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets(1)

oSheet.Range("A1").Value = "HEDL Asset Register" :
oSheet.Range("A1").Font.Bold = True

oSheet.Range("D1").Value = "Generated: " & Now()

oSheet.Range("A2").Value = "Filtered by: " & cboODVGroups.Text & ", " &
cboODVCats.Text & ", " & cboODVItems.Text & ", " & cboFeeders.Text

oSheet.Range("A3").Value = "Commissioned Date range: " & tbxStartDate.Text &
" to " & tbxEndDate.Text

'Create the QueryTable object.

Dim oQryTable As Object

oQryTable =
oSheet.QueryTables.Add("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
_

& tbxDataPath.Text & ";", oSheet.Range("A5"), tbxSQL.Text)

oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2

oQryTable.Refresh(False)

Dim MyValue As Integer

Randomize() ' Initialize random-number generator.

MyValue = CInt((9999 * Rnd()) + 1) ' Generate random value between 1 and 6.

Dim myTime$

myTime = CStr(Now())

myTime = Replace(myTime, "/", "-")

myTime = Replace(myTime, ".", "")

myTime = Replace(myTime, ":", ".")

oBook.SaveAs(tbxSaveXLPath.Text & "ODRC " & myTime & ".xls")

oQryTable = Nothing

oSheet = Nothing

oBook = Nothing

oExcel.Quit()

oExcel = Nothing

Dim MsgBox As DialogResult

MsgBox = MessageBox.Show("ODRC file saved as " & tbxSaveXLPath.Text & "ODRC
" & myTime & ".xls", "File Saved", MessageBoxButtons.OK,
MessageBoxIcon.Information)

End Sub
 
Further note: Just found that EXCEL.EXE is sometimes retained when looking
in Task Manager, which gives rise to the problem. So it appears my XL object
not closing sometimes??
 
Graeme,
You need to call Marshal.ReleaseComObject on all the excel objects you
reference and then set them to Nothing. This should allow the Excel
instance to terminate.

Ron Allen
 
Doing what Ron suggested will also solve your problem with double-clicking
on an Excel workbook. What is happening is that it's trying to load the
workbook into the currently running instance of Excel.

If for some reason, while reports are generating you think you (or someone
else on the same machine that the reports are generating on) might
double-click on an Excel file, you may want to set IgnoreRemoteRequests =
true on your excel object. Just remember to set it back to false after your
reports are done.
 
Thanks Guys. Have invoked garbage collector - seemed easier : ) - but will
play further with Marshal.ReleaseComObject.
 
Back
Top