VB6 export to Excel, then error in the Excel file

[acjeff]

Hi,

I use VB6 to export data from Access to Excel.

My platform: XP Pro
Excel: 97
Access: 97
VB: 6
VB Project Reference: Microsoft Excel 8.0 Object Library

Part of my code:
Dim ObjExcel As Object
Dim ObjWorkBook As Object
Dim ObjWorkSheet As Object
Dim chObject As Object

Set ObjExcel = CreateObject("Excel.Application")
Set ObjWorkBook = ObjExcel.Workbooks.Add
Set ObjWorkSheet = ObjWorkBook.ActiveSheet
Set chObject = ObjWorkSheet.ChartObjects.Add(0, 168, 670, 340)

After populating data and open the Excel worksheet,

ObjWorkSheet.SaveAs ("WorkSheet1")
ObjExcel.Visible = True 'show worksheet
Set chObject = Nothing
Set ObjWorkSheet = Nothing
Set ObjWorkBook = Nothing
Set ObjExcel = Nothing

Problem:

Excel and the worksheet comes up with data correctly. I close the worksheet and Excel application, and close the VB application. Then, go to Windows Explorer and open the saved Excel file, it was opened with an error dialog:

"A document with the name 'WorkSheet1' is already open. You cannot open the document with the same name, even if the documents are in different folders. To open the second document, either close the document that's currently open, or rename one of the documents."

Even though I restart the computer and open the file again, the error still comes up. Maybe there is something sticks with the file.

Anyone please tell how come the error comes up and how to resolve it. I appreciate!

Jeff

 

[lendpoint]

Hey!

You must exit the excel application after creating the excel file. Try objExcel.Quit. You could also close the workbook & worksheet using Close method: objWorkSheet.close

Hope this helps!

-Gregor

---------------------------------
LendPoint Software

http://www.lendpoint.co.nr