my*****@hotmail.com (Jacky11) wrote in message news:<46**************************@posting.google. com>...
Subject: Hiding field names.
I'm exporting a query with a lot of strings to Excel.
When exporting - is there any way to have the field name hidden or
removed from the query by default?
I don't need the field name as a heading on the excel sheet.
Subject: Empty columns in a query.
I'm exporting a query to Excel.
I need an empty column between one of the columns in excel.
Is there any way to add an empty column between one of the columns in
the query?
Subject: Exporting to Text (Tab Delimited)
When exporting - where can I add "Text (Tab Delimited) (*txt)" in the
save as type dropdown?
The same with "CSV (Comma delimited) (*.CSV"
Subject: Event for Exporting.
....
Jacky,
1. When you use the export menu-ommand to crate a new worksheet then
you can't avoid the headers
- The headers could be hidden or deleted using a piece of VBA
automation code from an Access module. In that way tou could
accomplish also some layouting (column-withs etc.) if you want to
generate a presentable spreadsheet quickly.
- There is also another way of getting data from access into Excel as
a table in an existing Excel-worksheet in a fixed location by making a
linked Excel-table in Access, but a little more logical would it be to
use the query-tool within Excel to get table-data from Access and to
refresh the data from Excel when needed or when automatically when
opened. Possibly this is not what you want.
2. Just add a column containing '' or null as value and some
columnname in the query
3. You must use the export function and not 'save as'. Dont forget to
save the specification just before finishing. The name you save there
you could use it in a macro with a TransferText command.
4. You could create a menu-item or commandbutton on a form to start a
macro and/or VBA-procedure like in my example.
success, Marc
example based on one of my own applications:
I had a query to export to a spreadsheet and to do a little layouting
from access.
To hide the column-names you could add:
xl.application.selection.hidden = true
******** a macro
SetWarnings (no)
TransferSpreadsheet (Microsoft Excel 8-9; excellist; c:\excellist;
Yes; )
Runcode (sheetlayout())
MsgBox (Spreadsheet generated; Yes; None; )
******** the VBA-code:
Option Compare Database
Option Explicit
Public Function sheetlayout()
Dim xl As Object
Dim Name As String
On Error GoTo notopen
Set xl = GetObject(, "Excel.Application")
xl.Application.Visible = True
xl.Parent.windows(1).Visible = True
MsgBox "Excel still opened ... @please close first.", vbExclamation,
"Warning"
Exit Function
SheetOpen:
Name = InputBox("Filename xls-file:", "Save the result",
"c:\excellist")
Set xl = GetObject("c:\excellist.xls")
xl.Application.Visible = True
xl.Parent.windows(1).Visible = True
xl.sheets("excellist").Select
xl.sheets("excellist").Rows("1:1").Select
xl.Application.Selection.Font.Bold = True
'... here you could do the hiding of the columnname rows
xl.sheets("excellist").Columns("A:Q").Select
xl.Application.Selection.Columns.AutoFit
xl.sheets("excellist").Range("A1").Select
xl.Application.Selection.AutoFilter
xl.sheets("excellist").Rows("1:1").RowHeight = 25.5
xl.sheets("excellist").Rows("1:1").Select
xl.Application.Selection.VerticalAlignment = 1
xl.sheets("excellist").Range("A2").Select
xl.Application.activewindow.freezepanes = True
xl.Application.ActiveWorkbook.SaveAS (Name)
xl.Application.Quit
'xl.Application.Save
Set xl = Nothing
Exit Function
'Escape routine if Excel still open and busy ..
notopen:
If Err.Number = 429 Then Resume SheetOpen
MsgBox "Excel still opened, Please Close Excel first"
Exit Function
End Function
