export data to specific field in excel, is this possible?

[Miez]

Hello,
iv made a contact database , but now i want to export adress data to an
existing excel sheet. The cells in the excel sheety already have names like
name, street etc.
please help, at this moment i only know how to export an report to excel,
what is simple..

miez

 

[Jennifer Crawford]

In order to set the value of a specific cell when
exporting to Excel, you must know the number of the row
and the column for each cell that will contain data. To do
this, you cannot use DoCmd.TransferSpreadsheet - you
actually have to open an instance of Excel from within the
function (it's your choice whether to make it visible or
not), and then dump data into the worksheet wherever you
need it.

The format when setting Cell values is .Cells(Row,
Column).Value =

So for example, to dump data into a new worksheet:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

With xlSheet
.Cells(1, 1).Value = "I am in Row 1, Column A"
.Cells(3,3).Value = "I am in Row 3, Column C"
.....and so on, filling in the cells
End With

I do this by pulling data from a query and then setting
the various data points into specific cells on the screen.

Helpful hint - if you want to include any formulas or
formatting, go to a blank Excel worksheet, start recording
a Macro, and then do the formula or formatting in the
worksheet. Once you've done that formatting, stop the
Macro, then open it and you'll see the VBA to use from
within the VBA in your Access function.

 

[Guest]

Miez,

Here's a fuction to copy the records in a table or query to a specific
sheet, cell or range in an Excel workbook.

Steve


Public Sub CopyRs2Sheet(strSql As String, strWorkBook As String, _
Optional strWorkSheet As String, Optional strCellRef
As String)
'Uses the Excel CopyFromRecordset method
'strSql: Sql Select string
'strWorkBook: Full path and name to target wb, will create if doesn't
exist
'strWorkSheet: Name of target worksheet, will create if doesn't exist
'strCellRef: Upper Left cell for data, defaults to A1


On Error GoTo ProcError
DoCmd.Hourglass True

'using late binding on Excel
Dim objXLApp As Object 'Excel.Application
Dim objXLWb As Object 'Excel.Workbook
Dim objXLSheet As Object 'Excel.Worksheet
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim i As Integer
Dim iSheets As Integer

'set rs from sql, table or query
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)

'start Excel
Set objXLApp = CreateObject("Excel.Application")

'open workbook, error routine will
'create it if doesn't exist

'only create workbooks with 1 sheet
iSheets = objXLApp.SheetsInNewWorkbook 'save user's setting
objXLApp.SheetsInNewWorkbook = 1 'set for only 1 sheet
Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
objXLApp.SheetsInNewWorkbook = iSheets 'restore user's setting

'select a worksheet, if sheet doesn't exist
'the error routine will add it
If strWorkSheet = "" Then
strWorkSheet = "Sheet1"
End If

'If Range is missing default to A1
If strCellRef = "" Then
strCellRef = "A1"
End If

'select desired worksheet
Set objXLSheet = objXLWb.Worksheets(strWorkSheet)

'insert recordset into Excel Worksheet using CopyFromRecordset method
objXLSheet.Range(strCellRef).CopyFromRecordset rs
objXLSheet.Columns.AutoFit

'Save wb
objXLWb.Save
objXLWb.Close

'close up other rs objects
If Not rs Is Nothing Then rs.Close
Set rs = Nothing

Set objXLSheet = Nothing
Set objXLWb = Nothing

'quit Excel
If Not objXLApp Is Nothing Then objXLApp.Quit
Set objXLApp = Nothing

DoCmd.Hourglass False
Exit Sub

ProcError:

Select Case Err
Case 9 'Worksheet doesn't exist
objXLWb.Worksheets.Add
Set objXLSheet = objXLWb.ActiveSheet
objXLSheet.NAME = strWorkSheet

Resume Next

Case 1004 'Workbook doesn't exist, make it
objXLApp.Workbooks.Add
Set objXLWb = objXLApp.ActiveWorkbook
objXLWb.SaveAs strWorkBook

Resume Next

Case Else
DoCmd.Hourglass False
MsgBox Err.Number & " " & Err.Description
Stop
Resume 0
End Select

End Sub