Need help transfering Data From Access to Excell 
Author Message
 Need help transfering Data From Access to Excell

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.*-*-*.com/ '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



Wed, 15 Nov 2006 12:07:23 GMT  
 Need help transfering Data From Access to Excell
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

--
       Ken Snell
<MS ACCESS MVP>


Quote:
> 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



Wed, 15 Nov 2006 12:31:45 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Transfering data from Excell to Access

2. New computer, need help transfering data

3. NEED URGENT HELP - problems transfering Access app with own ActiveX on another machine

4. NEED URGENT HELP - problems transfering Access app with own ActiveX on another machine

5. Help Saving Data in Access from Excell

6. Need program for Transfering excel data

7. Appending data from txt file to access and exporting data to excell sheet from report

8. Newbie needs help - I need to publish a form with data in html from Access 2k

9. Help with Access to Excell HELP PLEASE PLEASE HELP

10. transfering from word to excell


 
Powered by phpBB® Forum Software © phpBB Group