Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004

    Exclamation Unanswered: Exporting to Excel Question!

    Ok, I am developing an app and part of it is to export data into an excel file. I have all the code written but I need to format a column so that it's a date format. Can you direct me as to what the syntax is for this? So far I have:


    and I'm not sure what to put after that dot. So PLEASE help me! I'm sure it's something simple but I cannot seem to find it in the book that I have.


  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Here's a way to figure it out yourself. Go into Excel and select Tools/Macro/Record New Macro. Perform the action you want to automate. Stop the macro recording. View the code created. While this code may not always be exactly what you need, it will be close.

  3. #3
    Join Date
    Mar 2004
    Cool...that works great. for those of you who might want to use this code in the future here is a sample.

    xlSheet.Columns(4).NumberFormat = "mm/dd/yy h:mm AM/PM"

    where xlSheet is set as Excel.worksheet

    Thanks for the help!

  4. #4
    Join Date
    Apr 2004
    outside the rim

    FYI - Access to Excel Tip

    Hey, a quick note about exporting Access stuff to Excel.

    If you export a REPORT to Excel, Excel will treat every field as General - meaning that it will guess the data formats.

    Often, this is ok, except when you have data of one type that looks like another. An example would be a "Part Number" field, which is actually text. Let's say the nine digit number (sorry, I mean the nine character string) may start with a 0 and sometimes has an alpha character. Excel will treat all the data that is strictly numerals as a number (meaning the leading zeros will be lost). Furthermore, it will treat the values with an alpha character as text - leaving you with a column of mixed data types (can screw up sorting, among other things).

    An easy way around this is to export from a FORM instead. In this case, if you set the Format on each control that is likely to give you a problem, Excel will honor those format settings.

    Have fun
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Mar 2004
    Cool! Thanks for the heads up on that. I'll remember that for future use! With this particular application I'm exporting it from an Access Table into Excel and there are almost 2000 records so this is just the best way to do it in this instance and there are only 8 columns. So the only one that needed to be specifically formatted was that date field. All the other fields were either all straight alpha or straight numerical this time around. But this is good info for future applications! Thanks again.

Posting Permissions

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