export to excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to put a button on a form to export a specific table (view) to
Excel. I've checked the sites I know (Access Web, etc) for that code and
didn't find what I needed. Does somebody have a link to vba code that does
that? Thanks so much.
 
hi,
I'd like to put a button on a form to export a specific table (view) to
Excel. I've checked the sites I know (Access Web, etc) for that code and
didn't find what I needed. Does somebody have a link to vba code that does
that? Thanks so much.
Click to expand...
Search for

DoCmd.TransferSpreadSheet

in the help.


mfG
--> stefan <--
 
You can also use two more methods

1.DoCmd.OutputTo
2.Use DAO or ADO

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("<provide the name of the excel file
here")

Set wks = appExcel.Worksheets(7)
wks.Activate


sSQL = "<sql query to retrieve data"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL)
For i = 1 To 27
Range("a8").Offset(0, i - 1).Value = rst.Fields(i - 1).Name
Next i
Range("a9").CopyFromRecordset rst
Set dbs = Nothing
 
Dear amabarish
its really helpful note
but i have an problom of exporting many querys (which are based on many
date criteria )to an perticular worksheet and do further calculations and
retain it
(1) how to access such excel file from inside the form on click event
transfer spre sheet method is inserting many workbooks for each query
which is not so help ful for me i want all exported querys in one field
(2) how to save the formulas and other cell formats in the excel file
kindly advise please
Balasubramanya
 
dear sir,
i would like to have in this way is this possible
dim exl as excel.application
set exl = createobject(excel.sheet)
exl.application.visible = true
exl.application.cells(5,1).value = MYREPORT
dim value1 as integer
value1 = 2+2-5-*458*25
exl.application.range(a1:c1).value = value1
fine it works BUT
how to have the value such as
exl.application.cells(1,1).value = query1
exl.application.cells(5,1).value = query2
querys are my access querys
reason fore this method is, any esxternal excel files addressed in docmd
method if deleted accidentaly or changed adderess will cause error
how to export many querys to single created excel sheet please.
 
Back
Top