Error 1004: Method 'Cells' of object '_Global' failed

[LT]

I always get the above error when my code was executed the
second time continuously. If I only run it once, there are
no error.

-----------------------------------------
Public Sub CreateExcelFile()
Dim xlApp As Object
Dim xlWorkBook As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
Set xlWorkBook = xlApp.Workbooks.Open("C:\test.xls")

'This portion will format the column B into a text
column with leading zeros.
Dim i As Integer
Dim strT As String
i = 1
strT = ""
Do While Cells(i, "B").Text <> ""
strT = Cells(i, "B").Text
Cells(i, "B").Formula = "=text(" & strT ", ""00000"")"
i = i + 1
Loop

xlWorkBook.SaveAs "C:\test1.xls", xlNormal
xlWorkBook.Close
Set xlWorkBook = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub
-----------------------------------------

I tried searching in the MS Knowledge Base & it returned
article 815406, suggesting to install a post-Office 2000
SP3 fix. However I can't find the link to this fix...

I'm not sure if the article is related to my error & how
do I overcome it?

Thanks!
LT.

 

[Tom Ogilvy]

code like this
Do While Cells(i, "B").Text <> ""
strT = Cells(i, "B").Text
Cells(i, "B").Formula = "=text(" & strT ", ""00000"")"
i = i + 1
Loop

creates a reference to excel that can not be released until your application
closes.

First, if you are doing this from within Excel you don't need to do
createobject.
If you are doing it from word or access or some other application, then
qualify all you references

Do While xlapp.activesheet.Cells(i, "B").Text <> ""
strT = xlapp.activesheet.Cells(i, "B").Text
xlapp.activesheet.Cells(i, "B").Formula = "=text(" & strT ", ""00000"")"
i = i + 1
Loop

Regards,
Tom Ogilvy

 

[LT]

Thanks Tom.

-----Original Message-----
code like this
Do While Cells(i, "B").Text <> ""
strT = Cells(i, "B").Text
Cells(i, "B").Formula = "=text(" & strT ", ""00000"")"
i = i + 1
Loop

creates a reference to excel that can not be released until your application
closes.

First, if you are doing this from within Excel you don't need to do
createobject.
If you are doing it from word or access or some other application, then
qualify all you references

Do While xlapp.activesheet.Cells(i, "B").Text <> ""
strT = xlapp.activesheet.Cells(i, "B").Text
xlapp.activesheet.Cells(i, "B").Formula = "=text(" & strT ", ""00000"")"
i = i + 1
Loop

Regards,
Tom Ogilvy




.