export to excel with criterias

[jim Bob]

Hi,
I'm using the transferspreadsheet function to export my tables into
excel. Is it possible to export just certain columns? and is it possible
to export it without harding coding the file name and location?

Thanks in advance.
*** Sent via Developersdex http://www.developersdex.com ***

[fredg]

On Tue, 03 Jan 2006 05:42:55 GMT, jim Bob wrote:

Hi,
I'm using the transferspreadsheet function to export my tables into
excel. Is it possible to export just certain columns? and is it possible
to export it without harding coding the file name and location?

Thanks in advance.

*** Sent via Developersdex http://www.developersdex.com ***

Create a query with just the fields you wish to export. Export that
query.

You'll have to set the path and file name at some point.
If you want to leave it up to the user, you can use this simplest of
methods.

Dim strWhere as String
strWhere = InputBox("Enter path and file name")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"QueryName", strWhere

The user will be prompted for the path and file name.
Add whatever error handling you need to verify that the path and file
name are properly entered, as well as to exit the sub if the input box
is canceled.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Red]

You could always do it progmatically as well....

Here's a little snippet I like to use...

Public Sub MakeXLSReport2()
'Declare your variables
Dim rs As Recordset
Dim XLS As Object, Wkb As Object

'Set up your recordset
Set rs = Currentdb.openrecordset("MyTable")
if rs.recordcount < 1 then goto NoRecords 'It there's no records, let
the user know, and end sub

'Setup your Excel file
Set XLS = CreateObject("Excel.Application")
XLS.Application.Visible = True 'Watch it while it does it's thing
XLS.Application.workbooks.Add 'Make a new workbook
Set Wkb = XLS.Application.activeworkbook.worksheets(1) 'I like to setup
an object for my workbook, makes for less typing
XLS.Application.displayalerts = False 'Turn off alerts while you delet
the extra workbooks (You can skip this and the next 3 lines if you need
all 3 books)
XLS.Application.activeworkbook.worksheets(2).Delet e
XLS.Application.activeworkbook.worksheets(2).Delet e
XLS.Application.displayalerts = True

Wkb.NAME = "My Excel Spreadsheet"

'Now name the columns...
'This is where you can get special... this loop will add ALL the field
names.. you can specify individually if you'd like... of you can name
them yourself...
'i.e. wkb.cells(1,1) = "Name"

X = 1
For Y = 0 To 14
Wkb.cells(X, Y + 1) = rs.Fields(Y).NAME
Next Y
'Now we add all the data... you can do it all at once, or
individually...
'To do it individually, use this:
'wkb.cells(ROW,COLUMN) = rs.("Field Name").value
'NOTE: You CAN leave off the "value" at the end, I just leave it there
for refrence in case Microsoft requires it in later versions

rs.MoveFirst
X = 2
Do Until rs.EOF
For Y = 0 To 14
Wkb.cells(X, Y + 1) = rs.Fields(Y).Value
Next Y
X = X + 1
rs.MoveNext
Loop

GoTo EndMe

NoRecords:
lmsg = "There are no records please try again"
MsgBox lmsg
EndMe:
Set rs = Nothing
set wkb = nothing
set xls = nothing

End Sub

[jim Bob]

Hi Guys,
thanks for your advice.
Guess I will have to get my user to select the columns they want and
then build a query and THEN export this out.
Cheers.
Jim

*** Sent via Developersdex http://www.developersdex.com ***