By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,044 Members | 1,720 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,044 IT Pros & Developers. It's quick & easy.

4 different questions.

P: n/a
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.
I would like to export a query to Excel by clicking on a command
button.
Can anyone please help write an event for a command button?

Thanks for your help.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

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?

If your query is select firstname , lastname from clients and you want
an empty column between them just use a query like this

select firstname , '' as MyEmpty,lastname from clients
--
Posted via http://dbforums.com
Nov 12 '05 #2

P: n/a
"Jacky11" <my*****@hotmail.com> wrote in message
news:46**************************@posting.google.c om...
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.
I not sure what you mean by the above. Are you saying that you don't want
certain fields to be included in the export? Then simply build a query where
you leave those fields out. That is the whole idea as to why you use a query
in place of the table to export (you can choose whatever fields you want).
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"
Just choose text file,and give the data a file extension of csv. After you
type in the file name, and hit export, you are given additional options such
as TAB delimited etc. (the export wizard will launch AFTER YOU TYPE in the
file name and hit export).

Subject: Event for Exporting.
I would like to export a query to Excel by clicking on a command
button.
Can anyone please help write an event for a command button?


Check out the help files on TransferText Method
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
Thank you
It really saved me a lot of time.
But is there any way to automatically delete the single quotation mark
that gets added while exporting?
Also I have to export this to a text (Tab delimited) file. Can you
help me with this?

Thanks again

M.***********@uva.nl (Marc) wrote in message news:<ae**************************@posting.google. com>...
my*****@hotmail.com (Jacky11) wrote in message news:<46**************************@posting.google. com>...
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,

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; )

Nov 12 '05 #5

P: n/a
Please see your code I'm using now.
Public Function sheetlayout()
On Error GoTo Err_Command1_Click
Dim xl As Object
Set xl = GetObject("c:\windows\desktop\123.csv")
xl.Application.Visible = True
xl.Parent.windows(1).Visible = True
xl.sheets("excellist").Select
xl.sheets("excellist").Rows("1:1").Select
xl.Application.selection.Delete = True
xl.Application.Save
xl.Application.Quit

Set xl = Nothing
Exit Function

Err_Command1_Click:
MsgBox Err.Description
Resume Next

Exit Function
End Function
However, after "xl.Application.selection.Delete = True" I'm getting a
message "Unable to set the delete property of the range class"
After "Resume Next" a new file is created on the Desktop with the name
Resume.
What am I doing wrong?
Please help!!
Thanks


M.***********@uva.nl (Marc) wrote in message news:<ae**************************@posting.google. com>...
my*****@hotmail.com (Jacky11) wrote in message news:<46**************************@posting.google. com>...
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,
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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.