Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Connecticut, USA
    Thanked 0 Times in 0 Posts

    Checkboxes and excel (Access 2000)

    I am trying to make a built in function that query search results to an excel spreadsheet. Right now I am using this code under an onclick event:
    DoCmd.OutputTo acOutputQuery, "Master_Table_Query_Builder", acFormatXLS, , True
    I haven't had much experience with coding excel in an Access event. After the data is sent, I want to create code that automatically formats some of the data. I want the column headers from C1 to N1 to have a -90 text orientation. In my database, I have several fields that store data from checkboxes. When I send this data to excel, it is displayed as "0" and "-1". It might be worth while to mention that I make the user imput a value for the checkboxes, so the data isn't triplestate, it must be a "0" or "-1". I want to automatically format this data so "0" becomes an empty cell and "-1" becomes an "X". The range of the columns is C1 to N1 (the data corresponds to the column headers mentioned above). Since this data is from a query, the amount of rows can vary.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    A Magic Forest in Deepest, Darkest Kent
    Thanked 1 Time in 1 Post

    Re: Checkboxes and excel (Access 2000)

    Hi GoodAJ

    Can I suggest you do it in a slightly different way, if I may.

    I have done a similar thing before when I was extracting data from a database to Excel. Instead of the cumbersome code of doing from Access to Excel, I created an Excel spreadsheet and connected to an Access database.

    Sheet 1 was called Raw Data and I connected to the database Data|Import External data|New Database query. I run through the wizard and connect to the table or query I was working on. In sheet 2 I formatted, created my functions and made all the changes you need. To get -1 and 0 to display properly you could use the if statement. When you open the excel workbook and go to sheet 1 (Raw data) you can refresh the downloaded data and the info in sheet 2 will automatically change. Much easier than hard coding the access database as you can manipulate easily from Excel. I hope this helps

Posting Permissions

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