Access to Excel Problem Revisited

[NobleDBell]

I got some help here earlier and it seemed, at the time, to work but...

What I want to do is this:
I have an Excel spreadsheet that contains data in different columns.

I want to be able to take data from an Access Database and add it to the Excel spreadsheet without distrubing the data in the Excel spreadsheet when the user clicks on a button in the Access form.

Any help would be appreciated on this as I need to get this project completed for my client asap.

Thanks in advance,
Noble

 

[sfm6s524]

The Excel Range CopyFromRecordSet method works pretty good:

Dim xlsApp As Object
Dim xlsWBook As Excel.Workbook
Dim xlsWSheet As Excel.Worksheet
Dim ObjCell As Excel.Range
Dim RS As Recordset
Dim SQL As String

On Error Resume Next
Set xlsApp = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then
Set xlsApp = New Excel.Application
Err.Clear
End If
On Error GoTo 0
Set xlsWBook = xlsApp.Workbooks.Add
Set xlsWSheet = xlsWBook.ActiveSheet
xlsApp.Visible = True
SQL = &quot;SELECT tblUsers.UserID, tblUsers.FirstName, tblUsers.LastName FROM tblUsers;&quot;
Set RS = CurrentDb.OpenRecordset(SQL)
'Set ObjCell = xlsWSheet.Cells(2, 2) ' Row 2, Column 2
Set ObjCell = xlsWSheet.Range(&quot;B2&quot ' Or Cell Address
ObjCell.CopyFromRecordset RS
Set ObjCell = Nothing
Set xlsWSheet = Nothing
Set xlsWBook = Nothing
Set xlsApp = Nothing

Of course, you'll need a valid recordset from your app. If you want to loop through recordset and cells, you could set each cells value to a value from the recordset, and do some formatting along the way. But the CopyFromRecordSet method is fast and works pretty slick. Not sure if that's what you were looking for.....Ken.

 

[NobleDBell]

I am assuming that all this is done from Access because the user may or may not have the Excel spreadsheet open at the time? The button is on a Access form not on an Excel spreadsheet.

Thanks,
Noble

 

[sfm6s524]

Yep! Put the above code on the On Click event.