Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    74

    Unanswered: error number 1004

    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

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

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    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.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Nov 2003
    Posts
    74
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •