473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

help for TransferSpreadsheet Method

Dear all,

I am developed one interface which search records form my database .
I want to export my search result to excel .Transfer spreadsheet method export's whole database.

But I only want to export searched result only. Can any body provide me the solution .......
Expand|Select|Wrap|Line Numbers
  1. cmd_click()
  2. ssqltext = "SELECT * FROM DATABASE "
  3.  
  4. If Not IsNull(Me.FNAME) And Len(Me.FNAME) > 0 Then
  5.                   ssqltext = ssqltext & "WHERE [firstName] like '" & Me.FNAME & "*'"
  6.               End If
  7.  
  8.  
  9.  'LASTNAME SOLUTION
  10.              If Not IsNull(Me.LNAME) And Len(Me.LNAME) > 0 Then
  11.                  If ssqltext = "SELECT * FROM DATABASE " Then
  12.                             ' MsgBox "EQUAL TO" & sSqlText
  13.                               ssqltext = ssqltext & "WHERE [LASTName] like'" & Me.LNAME & "*'"
  14.                               Else
  15.                               ssqltext = ssqltext & " AND [LASTNAME] LIKE '" & Me.LNAME & "*'"
  16.                               End If
  17.  
  18. END SUB
Jan 27 '07 #1
1 2901
NeoPa
32,556 Expert Mod 16PB
This exerpt from Access Help may help.
Your selection SQL must be saved as a QueryDef before it can be used by TransferSpreadsheet.
TransferSpreadsheet Method
See AlsoApplies ToExampleSpecificsThe TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
expression Required. An expression that returns one of the objects in the Applies To list.

TransferType Optional AcDataTransferType.

AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.


SpreadsheetType Optional AcSpreadSheetType.

AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
Note You can import from and link to Lotus .WK4 files, but you can't export Microsoft Access data to this spreadsheet format. Microsoft Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0 spreadsheets by using this method.

If you leave this argument blank, the default constant (acSpreadsheetTypeExcel8) is assumed.


TableName Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.

FileName Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames Optional Variant. Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

UseOA Optional Variant.
Jan 27 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: O'Donnell Tribunal | last post by:
Hello Everybody, I am using the TransferSpreadsheet method to import spreadsheets for processing. say my code looks like this: DoCmd.TransferSpreadsheet acImport, 0, _ "tbltempImport1", ""...
2
by: NRB | last post by:
Dear friends, Please help me.I am new to VBA programming in access.I need to open a query in MS access database and send those data to a existing excel sheet with the formulas. Please help me...
1
by: barma16 | last post by:
I've hit a bit of a brick wall here, and could use some advice. I have an Access application whose output is a four-tab Excel spreadsheet where three of the four tabs are the result of database...
0
by: jpodesta | last post by:
Hello- I am fairly new to MS Access and would like to use some macros in .xls in an Access Module. I have tried to do this on my own but failed to make it work. I have included the xls macros...
4
by: jpodesta | last post by:
I would like to add the xls macros below to the module listed. I am looking for advice as to the best way to go about this. module Public Function clean_pn(pn_in As Variant) As String
4
by: Google Boy of Company C | last post by:
Hi Does anyone know why there seems to be a file name limit of 60 characters in Transferspreadsheet. I am exporting some records and this is giving me severe grief. I keep getting a message...
13
by: aleksandra_83 | last post by:
Hello, I have searched all over google groups and internet, but I have not found a solution to this problem that has worked. The solutions I found helped me single out the line that is causing...
6
by: sara | last post by:
I have a procedure to automate bringing several Excel files into our Access tables, on a daily basis. The problem is that if the user has a problem, and tries to run the import again (maybe 3...
2
by: DeanL | last post by:
Hey Guys, Does anyone know of a way to format the output of the TransferSpreadsheet method in Access 97 so that the exported spreadsheet has some formatting (column widths, row heights, cell...
3
by: Icarus | last post by:
I'm using TransferSpreadsheet to import an Excel file in to MS Access 2003. DoCmd.TransferSpreadsheet acImport, 8, strTable, strFileName, True, "" One of the columns in the Excel file is a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.