access export to excel cell spacific

[DHill009]

i despratley need help, am going round and round in circles and getting no
where!

I have an access database and need to transfere data in to apreset exclee
spredsheet.

i need to export all the fields in my access query call "RSA" to the excell
spredsheet called "RSA_Payment" but the fields from the query need to be
placed in spacific cells.

ie invoice number ----> C25 etc.

ANy help you can give would be great. ;-)

 

[Arvin Meyer MVP]

Set a reference to Excel for early binding, then:

Dim your variables:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

First build a recordset from your query:

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from RSA Where ID =" & Me.txtID,
dbOpenSnapshot)

Then use that recordset to fill specific cells:

Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\FolderName\RSA_Payment.xls")
Set wks = wkb.Worksheets(1)

With wks
.Cells(25, 3).Value = rst![Invoice number]
.Cells(25, 4).Value = rst!Field1
.Cells(25, 5).Value = rst!Field2
End With

That should get you started. Remember to close your recordset when you're
done.

 

[DHill009]

Thanks for your quick reply.

I should have said I dont have great exprience with VBA and have tryed to
use what you just posted and alligotwas errors.

could you dumm it down for me?

Set a reference to Excel for early binding, then:

Dim your variables:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

First build a recordset from your query:

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from RSA Where ID =" & Me.txtID,
dbOpenSnapshot)

Then use that recordset to fill specific cells:

Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\FolderName\RSA_Payment.xls")
Set wks = wkb.Worksheets(1)

With wks
.Cells(25, 3).Value = rst![Invoice number]
.Cells(25, 4).Value = rst!Field1
.Cells(25, 5).Value = rst!Field2
End With

That should get you started. Remember to close your recordset when you're
done.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

i despratley need help, am going round and round in circles and getting no
where!

I have an access database and need to transfere data in to apreset exclee
spredsheet.

i need to export all the fields in my access query call "RSA" to the
excell
spredsheet called "RSA_Payment" but the fields from the query need to be
placed in spacific cells.

ie invoice number ----> C25 etc.

ANy help you can give would be great. ;-)

 

[Arvin Meyer MVP]

Post the names of your folders, files, the form name, and the names of the
controls on your form.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks for your quick reply.

I should have said I dont have great exprience with VBA and have tryed
to
use what you just posted and alligotwas errors.

could you dumm it down for me?

Set a reference to Excel for early binding, then:

Dim your variables:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

First build a recordset from your query:

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from RSA Where ID =" & Me.txtID,
dbOpenSnapshot)

Then use that recordset to fill specific cells:

Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\FolderName\RSA_Payment.xls")
Set wks = wkb.Worksheets(1)

With wks
.Cells(25, 3).Value = rst![Invoice number]
.Cells(25, 4).Value = rst!Field1
.Cells(25, 5).Value = rst!Field2
End With

That should get you started. Remember to close your recordset when you're
done.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

i despratley need help, am going round and round in circles and getting
no
where!

I have an access database and need to transfere data in to apreset
exclee
spredsheet.

i need to export all the fields in my access query call "RSA" to the
excell
spredsheet called "RSA_Payment" but the fields from the query need to
be
placed in spacific cells.

ie invoice number ----> C25 etc.

ANy help you can give would be great. ;-)