Results 1 to 2 of 2
2005-07-12, 18:18 #1
- 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.
2005-07-12, 18:53 #2
- Join Date
- Feb 2002
- A Magic Forest in Deepest, Darkest Kent
- Thanked 1 Time in 1 Post
Re: Checkboxes and excel (Access 2000)
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 helpsJerry