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

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.

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

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" wrote in message
...
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.

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

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

"LT" wrote in
message
...
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.



.