Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Export Access 2003 to Excel

    I have this code which works fine.

    I have Access 2003 on XP Pro Windows and Excel 2003 (11.8120.8122) SP2

    This file is exported in an earlier Excel version, 3 if I remember correctly.

    Each time I save the file in Excel it ask me if I want to save it in the current format or a newer format.

    If I do this, then I lose many calculations on the Excel sheet based on the new exported file. Am I explaining this correctly.

    Question. How can I make this code export in the current version of Excel?

    Thanks . . . Rick

    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2003, "qryRanconCustProg", "D:\My Documents\, True"

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Rick

    Are you sure there is a constant acSpreadsheetTypeExcel2003?

    If there isn't then the code will use 0 which is the equivalent of acSpreadsheetTypeExcel3.

    Perhaps that's where the problem stems from?

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Rick Schreiber
    If I do this, then I lose many calculations on the Excel sheet based on the new exported file. Am I explaining this correctly.

    Question. How can I make this code export in the current version of Excel?
    What do you mean by losing calculations? Are you trying to have formulas appear in the exported document? This is possible, but not through the docmd procedure.

    It shouldn't matter what version of the Excel format you use because any relatively recent version supports formulas.

    If I were you, I'd avoid the docmd procedure and roll my own XML file. Just go into Excel, create a spreadsheet that looks like what you want and save it as XML. Then open that up in a text editor.

    It's actually pretty easy to read the XML for a small spreadsheet, and for the most part writing it just means a ton of print commands.

    The XML format allows you to generate the file silently and to put exactly what you want in it, whether it's formatting or formulas. (So long as you don't want pictures...) Oh, and if you name the file .xls no one will ever know it's an XML file.

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by norie
    Rick

    Are you sure there is a constant acSpreadsheetTypeExcel2003?

    If there isn't then the code will use 0 which is the equivalent of acSpreadsheetTypeExcel3.
    Facts and advice first:

    By default, VB has Option Let Me Waste Hours Debugging To Save Ten Seconds Writing A Dim Statement enabled.

    To disable this behavior, you need to put Option Strict at the top of your files. If you had that, the compiler would have caught your incorrect constant. Do it now, put it at the top of every module and form you've written.

    VB bashing / rant:

    What's the point of having constants in VB if you can misspell them?

    Yeah, you have Option Strict. But you shouldn't need Option Strict for the compiler to notice that, hey, this "variable" has never been declared and maybe we should warn the programmer. At the simplest you could require that a constant look different from a variable so that it could enforce that constants always be declared.

    At the very least, if I'm writing a library function that expects a named constant, don't allow 0 as valid input!!

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    sco08y

    Could you please explain what you are getting at?

    As far as I can see the OP simply wants to export data to an Excel file.

    There's no mention of formulas in their post.

    But they do seem to be indicating there is a problem with calculation.

    Also why goto all the bother of XML when a simple one line of code can export the data to Excel?

    Facts and advice:

    The OP isn't using VB but VBA.

    Option Strict is not available in VBA.

    PS You do realise that Excel version 3 probably dates back to about 1990.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Steady now ....

    I'm more on sco08y's wavelength here - I like to avoid docmd methods where I can since they are specific to the Access application object and so not very portable. You are also in a real ballache if you need to do anything more fancy than move data into excel.

    Here's my take on it. Not original but pretty powerful.
    http://www.dbforums.com/showpost.php...81&postcount=4
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2006
    Posts
    163
    pootle flump

    I'm not saying sco08y is wrong but I just don't see the need for the extra effort when the only problem I can see is there seems to be a problem with the constant being used to specify the version of Excel.

  8. #8
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by norie
    As far as I can see the OP simply wants to export data to an Excel file.

    There's no mention of formulas in their post.

    But they do seem to be indicating there is a problem with calculation.

    Also why goto all the bother of XML when a simple one line of code can export the data to Excel?
    Well, I could *assume* the OP is doing something trivial and that one line of code should work.

    But then I'm confronted by the fact that it *didn't* work. Even Excel 3 can represent a simple table of data, so why would his "calculations" not export?

    So I ask myself: what does he (or she) mean by calculations? Well, maybe he's got a query with a calculated column and expects DoCmd to export the calculated column as an Excel formula.

    I think it's not unreasonable, when posting on an advice forum, to assume that people have logical reasons for asking questions and to accomodate the fact that many of them don't know exactly how to phrase their questions.

    Facts and advice:

    The OP isn't using VB but VBA.

    Option Strict is not available in VBA.
    I'm betraying my Perl background here... I meant Option Expliclt. For the record, I could apply that same rant to Perl.

    PS You do realise that Excel version 3 probably dates back to about 1990.
    You do realize that PS doesn't make any sense unless you're writing on paper.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by sco08y
    ...You do realize that PS doesn't make any sense unless you're writing on paper.

    oooh I dunno postscript is still used in Linux & MAC systems extensively, and it rears it head in windows worlds as well (but only when PCL isn't looking)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2006
    Posts
    163
    Don't see any mention of calculations in Access.
    calculations on the Excel sheet
    This file is exported in an earlier Excel version, 3 if I remember correctly.

    Each time I save the file in Excel it ask me if I want to save it in the current format or a newer format.

    If I do this, then I lose many calculations on the Excel sheet based on the new exported file. Am I explaining this correctly.

    Question. How can I make this code export in the current version of Excel?
    Isn't the OP asking here how to
    make this code export in the current version of Excel?
    ?

    The current code appears to be exporting in Excel 3 format, then when the OP goes to save it, from Excel, they are being prompted to save in a later version.

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Yes . . . Export in Current Version of Excel

    I need to be able to export from Access 2003 to Excel 2003 version I quoted at begining.

    Then I pastLink into another Excel page and run calculations from that page.

    There must be a sinple answer!

    Thanks all . . . Rick

Posting Permissions

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