-
Hey guys.
Can anybody tell me why this seemingly basic code is crashing my excel? I'm just copying some data from a text file to rows in Excel.
First the code:
(with a reference to excel 9.0 Object library
Private Sub Command1_Click()
Dim xlsWorkBook As Excel.Workbook
Dim xlsWorkSheet As Excel.Worksheet
Set xlsWorkBook = GetObject("C:\My Documents\File.xls")
Set xlsWorkSheet = xlsWorkBook.Worksheets("Sheet1")
'Text1.Text = xlsWorkSheet.Cells(1, 1).Value
For intCount = 0 To 50
xlsWorkSheet.Cells(intCount + 2, 1).Value = strVAR1(intCount)
xlsWorkSheet.Cells(intCount + 2, 2).Value = strVar2(intCount)
xlsWorkSheet.Cells(intCount + 2, 3).Value = strVAR3(intCount)
xlsWorkSheet.Cells(intCount + 2, 4).Value = strVar4(intCount)
Next intCount
xlsWorkBook.Save
'xlsWorkBook.Close
MsgBox ("Done")
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Okay, first, if I take the REM off of the last line (xlsWorkbook.Close) then I get an Excel error message that the program has committed an error and will now close.
Then, with or without closing, the result is that the information gets written to the page, but when I open the xls in Excel, nothing opens at all. The filesize shows that the data has been written, and opening the spreadsheet in notepad, I can find a large portion of the data that I wrote to it. So, is there an error in this code?
Anybody?
Thanks.
-
I don't know if this will help, but make sure you set all your variables to nothing at the end
Code:
set xlsWorkSheet = Nothing
set xlsWorkBook = Nothing
Also, i use CreateObject, then use the Open Method, you must set Visible prop to true, but it works create for me.
-
You might try using the workbook collection instead of the workbook class.
xlApp.workbooks('sheet1').Close SaveChanges:=True
or (if it's the only sheet in the workbook
xlApp.workbooks(1).Close SaveChanges:=True
This will close and save in one line. Afterwords, do as gwdash suggests and set the workbook object to nothing.
