Solved

Export from Access table to Excel - How do I format to Yes/No

Posted on 2011-02-14
16
2,069 Views
Last Modified: 2012-05-11
The Format property of the boolean fields are all set to Yes/No.
But they export to Excel as True/False
I'm exporting directly from a table so I can't do an Iif([Field]= True, "Yes", "No").
How can I Force the export to use Yes/No?
0
Comment
Question by:kentgorrell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34893863
create a query and export the query.

select f1,f2, iif([f3]=-1,"Yes","No")
from table
0
 

Author Comment

by:kentgorrell
ID: 34893896
No the question was how to export directly from a table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34893899
you can export the table but you have is what you get.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34893903
you can export the table but what you have is what you get.
0
 

Author Comment

by:kentgorrell
ID: 35078349
There are more than a hundred fields in the table and they can change making manual maintenance of a query unpractical and open to error.

The only way to maintain a query is to write code to build the query def after any changes to the table structure.

Looks like my options are -
1. Dynamically build a querydef, substituing instant ifs for boolean fields, each time the schema changes
2. Dynamically build another table, wtih text fields instead of boolean filelds, each time the data changes
3. Use automation to open and modify the worksheet after export

unless you you guys have a better suggeston?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 35083592
<<There are more than a hundred fields in the table>>

Yikes! This *usually* means that tables can be better structured (worth looking into when you get the chance).

But for the issue at hand, this actually sounds like a perfect application for vba code, which the more I think about it the less complex it seems.

In summary, this is what the code should look like (pseudocode):

Open a query based on your table using "SELECT *"  (no specific fieldnames needed)
Loop through all fields
     Output field name as column header
End loop
Loop through all records 
      Loop through all fields in the record
              If data type is boolean 
                       use IIF to determine output written to Excel
              Else
                      Output data to Excel as-is
              End IF
      End Loop
End Loop

Open in new window


capricorn1 probably has similar samples if needed, and I'll give him a chance to follow up here first since he has already been working this, but I'll pitch in tonight or tomorrow if more help is needed.  
     
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35083719
take charge, miriam (too busy right now)
0
 

Author Comment

by:kentgorrell
ID: 35088479
I can easily build a procedure that loops through each field in the table and creates an SQL statement dynamically. eg
strSQL = "SELECT "
for each fld in rst.fields
if fld.Type = dbBoolean then
    ' need a little help here with the iif( - here is my attempt - does Thislook about right?
    strSQL = strSQL & ", " & "Iif(" & fld.name & ", """Yes""", """No""") AS " & fld.name
else
    strSQL = strSQL &  ", " & fld.name
end if
strSQL = strSQL & " FROM ...etc
set wdf = db.CreateQueryDef("qryOutputToExcel", strSQL)

so it's just the inserting of the Iif field into the querydef sql I need a little help with.
0
 

Author Comment

by:kentgorrell
ID: 35088505
Oh and just to note, I have no control over the table structure - it comes from an outside source.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 35090754
You've got the right idea overall.

<set wdf = db.CreateQueryDef("qryOutputToExcel", strSQL)>
However you do not need to create a querydef to export to Excel.

This can (and should) be done through Excel Automation.  Take a look at the following article for an example (and try the sample code to get a feel for it):
http://www.databasejournal.com/features/msaccess/article.php/3563671/Export-Data-To-Excel.htm

The idea is to iteratively output the data to Excel, changing boolean fields to Yes/No as you go (in other words, integrating the code you are attempting with a generic Excel Export such as that described in the databasejournal article).

Take a look at that article to get a feel for how Excel Automation works (setting up and writing to an Excel spreadsheet).

I haven't had a chance to put together a sample yet but may be able to do so tomorrow.

0
 

Accepted Solution

by:
kentgorrell earned 0 total points
ID: 35102545
Thanks for your guidance.

For my purposes it's going to be better to create a query in Access using code then allow users to view the result or export it.

The code for creating the query is fairly simple and the result works fine in Access.

Only problem is the export to Excel doesn't like the Field Alias being the same as the field that the Iif statement derives from. eg
Active:Iif([Active]=True, "Yes", "No")
The export feels that this is a circular reference even though Access has no problem with it.

It is important to retain the column headings in the worksheet so that they match when the worksheet is reimported.

So I ended up creating and executing a make query sql string then changing the column names.

For anyone else with this problem, you can see the code in the sample attached. CreateQuery.zip
0
 

Author Closing Comment

by:kentgorrell
ID: 35102556
Didn't quite fit my needs but led me to the outcome I needed.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 35108067
Excellent - and thanks for posting your solution!

Just so you know, if you come up with your own solution you have the option of accepting it as the answer, with or without assigning points to Expert comments as 'assisted answers'.

If you want to change this to show your comment as the Accepted Answer, post back and we can make that happen.
0
 

Author Comment

by:kentgorrell
ID: 35113323
I think my answer should be marked as an accepted answer so that others can see that the attached code contains a solution not just a problem

However the one I marked is also a solution, and should be marked as so, even if it isn't the one I ended up using and I'm happy to alot points to this solution.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 35124059
Okay - I've marked your answer as accepted and my answer as assisted, leaving the point allocation as you had initially assigned it.

Anytime you resolve your own questions, you can do this by using the "Accept and award points" button seen under your own comments in open questions, which marks your comment as the accepted answer (Author solutions receive zero points), and allows you to assign some or all of the points to others as assisted answers.

0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question