export and open an excel file from access

[Guest]

dear friends
can any one please help me one more thing please for which im looking over
whole news group!
(1) i want to export many querys to excel using outputto method ofcourse
its doing the job but its creating an new work sheet for every query
how to put all data into one work sheet
(2) or else open an excel file in mydocuments / orelse anywhere and import
all the necessary table and do canculations but
(A) by this method how to use date criteria for importing data from tables
(b) how to access such excel files which will be in the my documents folder
on click event and come back to access again

 

[Pieter Wijnen]

An example posted yesterday by me (blush)

Query: SingleCompany:
Parameters Forms!myForm!CompanyID Long;
SELECT C.* FROM MyTableOrQuery C
WHERE C.CompanyID=Forms!myForm!CompanyID

Sub OutPutCompanies()
Dim Db As DAO.Database
Dim QdfBase As DAO.QueryDef, QdfTemp As DAO.QueryDef
Dim RsCo As DAO.Recordset
Dim CID As Access.TextBox

Set Db = Access.CurrentDb
Set CID = Access.Forms!MyForm!CompanyID
Set QdfBase = Db.QueryDefs("SingleCompany")
Set RsCo = Db.OpenRecordset("Select CompanyID, CompanyName FROM Company
Order By 2", DAO.DbOpenSnapshot)
While Not RsCo.EOF
CID.Value = Rs.Fields(0).Value
On Error Resume Next
set QdfTemp = Nothing
Set QdfTemp=Db.CreateQueryDef(Rs.Fields(1).Value,QdfBase.SQL) ' Might
have to do some Replaces for invalid characters
If QdfTemp Is Nothing Then
Set QdfTemp=Db.QueryDefs(Rs.Fields(1).Value)
QdfTemp.SQL = QdfBase.SQL ' In case you've changed it
End If
On Error Goto 0
' the next line not really neccessary, but what the heck
QdfTemp.Parameters(0).Value = CID.Value ' or
Access.Eval(QdfTemp.Parameters(0).Name)
Access.DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97,
QdfTemp.Name,"C:\MyFile.xls"
QDfTemp.Close
Db.QueryDefs.Delete QdfTemp.Name

Rs.MoveNext
Wend
Rs.Close : Set Rs = Nothing
Set QdfTemp= Nothing : Set QdfBase = Nothing
Set Db = Nothing
End Sub

HTH (bar my usual typos)

Pieter

 

[Guest]

dear broyher,
Sorry for late replay,
your code worked but what exactly i am looking for is this
on cmdclick
(1) create an excel object
(2) work sheet . cell(1,5) value = " MY report "
(3) Worksheet.cell(2,1).value = query1
(4) worksheet.cell(2,5).value = query2
and so on
(5) worksheet .cell(2,6).value = worksheet.cell(1,1) + worksheet . cell(2,6)
and so on
how to do it please
im not so familier with excel please help me