VBA to write Access Query to Excel, Document Calculated Fields in queries, free tool
Do you have calculated fields in queries of your database? To see all your equations and fields with aliases, make a query with this SQL statement:
SELECT mQ.Name1 AS FieldName
, mo.Name AS QryName
, mQ.Expression
FROM MSysObjects AS mo
INNER JOIN MSysQueries AS mQ
ON mo.Id = mQ.ObjectId
WHERE ( (mQ.Name1 Is Not Null)
AND (left(mo.Name,1) Not IN ("~","{"))
AND (mQ.Expression Is Not Null)
AND (mQ.Attribute=6) )
ORDER BY mQ.Name1
, mo.Name;
*****
This demo show a free tool you can download to put the results of the SQL statement into Excel and format them. In addition to getting great documentation for your database, see how to use VBA (Visual Basic for Applications) to:
Stack field names into an array to write the labels across the top of a worksheet
Write data from Access to Excel
Use CopyFromRecordset (which I originally learned about from NateO)
Add data Filters in Excel
Format Excel ranges with borders and shading
Bold face text in Excel cells
Page Setup including Margins, Page Headers, and Orientation
Best-fit all columns
Freeze Panes -- top row and left column
Create an Excel workbook with the formatted SQL results that is easy to share!
~~~
Links
Download free tool to Document Access Query Calculated Fields from RogersAccessLibrary.
Document Calculated Fields in Queries
http://www.rogersaccesslibrary.com/fo...
To learn about Access, read my free Access Basics book
http://www.AccessMVP.com/strive4peace
... and watch the videos here:
Learn Access Playlist on YouTube
http://www.youtube.com/view_play_list...
** Original Music **
excerpts from "Slade", by JD Live (Jerome Dixon, Houston)
JD's Websites
http://myspace.com/jdlivepro
/ jdlivepro
______________________________
The VBA source code is included in the free download. If you are a developer and expand it, or are interested in being on a development team, please let me know so your great stuff can be shared, thanks ~
happy learning! ... and documenting :)
______________________
Intro and End: Microsoft Office PowerPoint.
Screen-shots: Techsmith SnagIt.
Screen capture and production: Techsmith Camtasia
Audio edited with GoldWave
~~~~~~~~
Capture and Production Settings:
Video: H.264, 24-bit color, 30 fps, 1280x720
Audio: PCM, 44,100 kHz, 16-bit, 172 kb/sec
~~~~~~~~
Render Format: QuickTime MOV
~~~~~~~~
let's connect *
Do you need help? You know your business. I know how to teach, write programs, and develop applications. If you have a budget and want control of your project, let's connect and build it together!
visit MsAccessGurus and contact me today!
http://msaccessgurus.com
I look forward to hearing from you ~
warm regards,
crystal
Remote Programming & Training -- connect to me, let's built it together
~ have an awesome day ~