copy data from access to specific cell in an excel file?

[behedwin]

Hey

How can i from data in a table send it to an excel file and fill specific cells with the data from a table in access?

For example
I have a table in access with columns named
NAME
AGE

how can i export NAME (copy the data) and put it into an excelfile (paste the data) into a specific cell.
then do the same with AGE

 

[Caleeco]

Sounds like you would need to setup a Data Connection. Data Ribbon > from Access

You can find more info here:
https://support.office.com/en-us/ar...workbook-a1952878-7c58-47b1-893d-e084913cc958

You can also do this in VBA, but I would need more information regarding filenames, locations, the data etc.

Caleeco

 

[Micron]

Isn't that link all about transferring Excel data to Access when the OP wants to go the other way? As noted, you can do this from Access using
- DoCmd.TransferSpreadsheet, which allows you to copy data from a table or select query into a spreadsheet but not a specific range
- the Export function in Access
- with vba (using Automation) - there are lots of code samples out there for this method. Basically, you loop through a recordset and write field1 into the sheet starting point, then move right one cell and write field2, then move next in the recordset and move to the first sheet column but down one row. Rinse and repeat until no more records.

You can also pull data into Excel using MS Query.

 

[alansidman]

This should be helpful

http://accessblog.net/2006/07/export-to-excel-range.html

 

[Micron]

Well, I for one would be very interested to know if it works as documented at that link. The information is from 2006 and claims that at the time, the Range argument isn't supported for exporting from Access to Excel. Here we are 12 years later and the documentation hasn't changed in that regard:

When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

I would be interested to know if it works as I don't recall ever trying it due to the assertion that it doesn't. While reading the article for the posted site link, I can't help but wonder if sometimes the reference to a workbook and a range is about a range at the sheet level or workbook. IIRC, a range can be created for either.

 

[behedwin]

im lost....

dont know how to even start attempting this...

take data from access table X row Y and copy each data value to specific cells in an excel file at a specific range

for example
i open data in access in a form
when i view a record from access in my form.
i want to add a button that says "export to excel"

this should open an excel file
copy data from that recordset and paste it into specific cells.

 

[Micron]

You want only 2 fields from 1 record on a form to be transferred? What's supposed to happen when user migrates to another record on same form? New sheet? New workbook? Same sheet next row? Won't happen? You are being too vague to get much in the way of help.

 

[UncleCatwoman]

What I do takes a little more code but does give you a LOT more control. You will need to make sure you have the Excel reference library included in your project.

Dim xlApp AS Excel.Application
Dim xlWB AS Excel.Workbook
Dim xlWS AS Excel.Worksheet
Dim intRow as Integer

' this opens an instance of Excel for you:
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add
Set xlWS = xlWB.Sheets.Add

' now you can do what you want with it:
xlWS.Name = "OutputFromAccess"
xlWS.Range("A1").Value = "Field1Header"
xlWS.Range("B1").Value = "Field2Header"
xlWS.Range("C1").Value = "Field3Header"

intRow=1
' loop through your form (or the source table for it) and, for each record:
' move to the next row
intRow = intRow+1
xlWS.Range("A" & intRow).Value = FormField1.Value
xlWS.Range("B" & intRow).Value = FormField2.Value
xlWS.Range("C" & intRow).Value = FormField3.Value

Like I said, it takes a little more code but gives you a whole lot more control of the data on both sides of the transfer. Plus, since you have an actual Excel instance open, you can add formatting, formulas, subtotals, etc. with the code too. Which are all things TransferSpreadsheet can't do.

Hope this helps!