hi, i am doing an exporting of data to excel file and after a period of time of testing my code, i will recieve this error :

error number 1004
error msg : Method 'Range' of object '_Global' failed

how can i solve/prevent this error??? when this error occurs, my exporting won't work. The error occurs at this line

"Excel.Range(Chr(Col) & Row).Select"

pls advice
nono

=------------------------------------------------------------------=
Do While Not rstCustData.EOF
Col = 65
Row = 1

For x = 0 To rstCustData.Fields.Count - 1

Excel.Range(Chr(Col) & Row).Select
Excel.ActiveCell = rstCustData.Fields(x)
Col = Col + 1

Next x

Row = Row + 1
Col = 65

rstCustData.MoveNext

Loop

=------------------------------------------------------------------=

I'm not sure if 'Excel' in your code is a variable or not. Here's how I address Excel's hierarchy (I have tried a few methods, but this seems to work best for my uses):

Dim xlObject As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlObject = New Excel.Application
Set xlWB = xlObject.Workbooks.Add
Set xlSheet = xlWB.ActiveSheet

xlSheet.Range("A1").Value = "ValueA"

etc.

BTW, if I'm reading your code correctly, you are outputting a recordset into Excel. If you're using the Excel 10.0 Object Lib (and possibly 9.0), the Range object exposes a .CopyFromRecordset method that will do that for you. All you would need to do is create the headings.

hi,

i have managed to solve the error, the way i place the code for retrieving the range is abit wrong organized, now everything is solved.

by the way thanks for the advice!!!

cheers
nono