exporting a record from access to excel

[ashitaka65]

Hello,
i'm writting a databse using access and would like to export certain records to an excel file.

i have coded a form that allows me to find a record by using the item number called "C#" then sets that record in a new form. so the new form now has the record i did the FindRecord for, now i am looking to export that record using a button into an excel file. i'm not sure what command to use, i have tried DoCmd.OutputTo
DoCmd.TransferT ext
and a few others and just cant seem to get it to work.
can anyone help me?
thank you in advance =D

[FishVal]

What about DoCmd.TransferS preadsheet method?

[ashitaka65]

that would allow me to export a table, not just the record, i would like it to export a single record

[Briansmi1116]

Could you use a filter to get the record, then output it to excel?

[ashitaka65]

i cant get it to export the current record only, not the entire sheet

[Briansmi1116]

What I mean is I have an Access database, that I can filter, and sort it, and only output to excel those records. Is tere a feild you can sort to that one record to export it? I can give you the code I used to output to excell if you can.

[ashitaka65]

i do, but what i would really like to know is if there is a way to do it with the current record only and not the entire form, i cant get it to export the current record only insted of the full form.

ex. Private Sub export_btn_Clic k()
DoCmd.OutputTo acOutputForm, ****me.CurrentR ecord*****, acFormatXLS, "C:\Documen ts and Settings\RCTO\D esktop\orders\c urrentorder.xls ", n, , MS - DOS
End Sub

i want it to focus on the current record and not the full form

[Briansmi1116]

I'm sorry, I thought I might be able to help out. Maybe fishval will be able to help you.

[FishVal]

• Create a query filtering records in your Form.RecordSour ce table/query by the value of PK (or other unique) field, e.g.
  [code=sql]
  SELECT * FROM [Your table name] WHERE [PK field name]=Forms![Your form name]![Name of form control bound to PK field];
  [/code]
• Use DoCmd.TransferS preadSheet method to export record returned by the query, e.g.
  

  DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"[Query name]","X:\export.xls"

[ashitaka65]

ok i will try that, =D

[ashitaka65]

now the problem i am having is i want to be able to export to that same file multiple times and have it add the new records but what its doing is just replacing the same record over and over again so i can never export more then one

[ashitaka65]

i'm sorry. i guess what it is is i'm having problems with it appending..
or i shuld say not appending

[FishVal]

now the problem i am having is i want to be able to export to that same file multiple times and have it add the new records but what its doing is just replacing the same record over and over again so i can never export more then one

Forget about all you've done in this direction. You need to use application automation to achieve this.
Something like the following:
[code=vb]
Private Sub btnXL_Click()

Dim appExcel As Excel.Applicati on
Dim wkbWorkBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet
Dim rngOutputTo As Excel.Range

' creates Excel application
Set appExcel = CreateObject("E xcel.Applicatio n")
' opens workbook for output (C:\target.xls)
Set wkbWorkBook = appExcel.Workbo oks.Open("c:\ta rget.xls")
' get reference to sheet for output (Sheet1)
Set wksSheet = wkbWorkBook.Wor ksheets("Sheet1 ")

' get A-cell in the first empty row
Set rngOutputTo = wksSheet.Cells. SpecialCells(xl CellTypeLastCel l)
Set rngOutputTo = wksSheet.Cells( rngOutputTo.Row + 1, 1)

' output all fields in the record
For Each fld In Me.Recordset.Fi elds
rngOutputTo.Val ue = fld.Value
Set rngOutputTo = rngOutputTo.Off set(ColumnOffse t:=1)
Next

' save and quit
wkbWorkBook.Sav e
appExcel.Quit

Set rngOutputTo = Nothing
Set wksSheet = Nothing
Set wkbWorkBook = Nothing
Set appExcel = Nothing

End Sub
[/code]

[ashitaka65]

whats wrong with what ive done? does it not append? is there no way to make it append?

[FishVal]

whats wrong with what ive done? does it not append? is there no way to make it append?

Access Help: TranferSpreadSh eet method

The TransferSpreads heet method carries out the TransferSpreads heet action in Visual Basic.

expression.Tran sferSpreadsheet (TransferType, SpreadsheetType , TableName, FileName, HasFieldNames, Range, UseOA)

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.