reference to objects in access and excel

[pb21]

I have this code which references and opens the spreadsheet, now I would like to pass the recordset data to the fields but not sure how now I have reference to the excel object.

Dim auditspreadsheet As Object

Set auditspreadsheet = GetObject("c:\Audit.xls")

some actions

with recordset

add access field data to corresponding excel field.

regards
Peter

 

[MarkK]

Object Browser...

...is the first place I'd start looking for answers here. As time goes by I find I consult this for information more than help files. In the <All Libraries> combo box, click on Excel and check out the Worksheet, Range and Cell objects, to name a few.

 

[pb21]

access to excel

Hi lagbolt

I found lots of help to go get data from access to excel but not in access to excel the help hasnt helped.

 

[g.epema]

Transferspreadsheet

You may be interested to use the method Transferspreadsheet, which transfers all data in a spreadsheet path to an Access table where the fieldnames are identical.
Full code:
DoCmd.TransferSpreadsheet acImport, acspreadsheettype9,_
strTabelNaam, strBestand, True

where:
acImport is a constant
acspreadsheettype9 for EXEL97
strTablename is name of Access table
strBestand is full path name (including extension) of the EXEL file

regards Gerben Epema

 

[pb21]

create new spreadsheet from template

I have the following code below in my access code which allows me to take the random rows returned in a query and pass them to an excel spreadsheet this code is courtesy of Getz & Gilbert VBA developers handbook.
What I cannot find is how to create a new spreadsheet based on an existing template.
Q1 Do i need to worry about this or does the mere fact of opening the template give me a new spreadsheet.
Q2 If i do have to do something How do I do it. I can see how to create a new word document from a template ie objWord.Documents.Add ("invoice.dot") using that object model.

The excel way is eluding me.

Help as always well appreciated.
regards peter

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objQuerySheet As Excel.Worksheet
Dim objResultsSheet As Excel.Worksheet
Dim objXLRange As Excel.Range

Set objXLBook = GetObject("c:\Audit.xls")
Set objXLApp = objXLBook.Parent
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
Set objResultsSheet = objXLBook. _
Worksheets("AuditChecklist")