Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Table to Excel (Access 2000)

    I'm trying to export an Access recordset to an Excel file via a little VBA code. The problem is when the export line is read I get a 2498 error (which says I entered an incorrect data type for one of the arguments). Needless to say, I'm not an expert in VBA coding <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    Here's the code:

    -----------------------
    Private Sub B_GENERATE_Click()
    Dim rs_excel As Recordset
    Dim Query, EXCEL_NAME
    Set rs_excel = New ADODB.Recordset

    Query = "select FIELD_A, FIELD_B, FIELD_C from TABLE where CONDITION"
    rs_excel.Open Query, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    EXCEL_NAME = "C:myfolder" & InputBox("Input Excel file name" & Chr(13) & "(do not include the .xls extension)", "Input Name") & ".xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, rs_excel, EXCEL_NAME

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

    Edited to add: I tried the SQL query in the SQL Query Analyzer and it worked


    Do you know what might be going wrong?
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export Table to Excel (Access 2000)

    Chr(13) is a carriage return. That is an illegal character in a file name.
    But the major problem is that DoCmd.TransferSpreadsheet can only export tables and saved queries, not recordsets. So ypu'll have to create a query with the correct SQL and save it. Then you can export it to Excel.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Table to Excel (Access 2000)

    Hi Hans,

    The Chr(13) is part of an InputBox prompt, and thus does not form part of the file path/name.

    After a whole morning trying to accomplish this and not finding anything related to an Access recordset DoCmd.TransferSpreadsheet export to Excel, we suspected the problem had to do with the recordset object. Your response confirms our suspicions <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Thanks!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export Table to Excel (Access 2000)

    Sorry, I didn't look closely enough at the definition of EXCEL_NAME.

Posting Permissions

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