Need help transfering Data From Access to Excell

  • Thread starter Thread starter Anonymous
  • Start date Start date
A

Anonymous

Good evening everyone!!

I'm still looking for a way to export data from Access to specific cell in
Excel??? I didn't get any response.

For exemple, I have a list of product with description and quantity and I
want to export the data to excell in just a click. Is it possible? I have
search the http://www.mvps.org/access/ web site but didn't find what I was
looking for...

When I click the analyse with Excel, the data are all messed up???

Just for your information, I'm building a querry to put the data that I want
in a temporary table. The table have a maximum of 15 fields and a unlimited
number of rows.

Thanks

JSP
 
You'll need to use VBA code to automate EXCEL in order to do what you seek.

ACCESS will not export data into an existing worksheet in an EXCEL file. The
TransferSpreadsheet command will create a new sheet in the workbook and put
the data on that new sheet.

If you want to put data onto existing sheet, you'll need to use Automation
to do that. Here's some info posted by John Nurick in another thread:
--------------------
If you're new to Automation, here's a useful article giving the
basics of controlling Excel from Access:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/default.aspx?scid=KB;en-us;123859

ACC2000: Using Automation to Create and Manipulate an Excel
Workbook (Q210148)
http://support.microsoft.com/default.aspx?scid=kb;en-us;210148

ACC: Using Automation to Create and Manipulate an Excel
Workbook (Q142476)
http://support.microsoft.com/default.aspx?scid=kb;en-us;142476

Recent versions of Excel have a Range.CopyFromRecordSet method
that can be very handy for importing data from Access.


Some sample code that may get you started:

Dim intColumn As Integer
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset)
If rst.EOF = False and rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
For intColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, intColumn).Value = rst.Fields(intColumn).Value
Next intColumn
Set xlc = xlc.Offset(1,0)
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
 
Back
Top