Exporting from Access to Excel via Macro / VBA Help

[ViNNiE]

Hi All,



Long time reader, first time poster.

I am currently setting up a database that I would like to export a query with 1 column that has parameters set (single date) to an excel template from into a certain cell range.

I do not have a lot of experience in this field (VBA) but have pieced together something from here.

Now my issue that this code exports the query without parameters set but as soon as I set the date parameter the code doesn't work.

I was able to add the first code entry into the second but it only copied the date column across and didn't sort by the parameter.

Any help fixing this would be greatly appreciated.

A A Ron

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDbSet qdf = db.QueryDefs("table1 query")
 
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Public Function Access2XL()

Dim rst
Dim XL As Excel.Application
Set XL = CreateObject("excel.application")
Dim vFile


vFile = "\\Mac\Home\Documents\TEST DB\testss.xlsx"


Set rst = CurrentDb.OpenRecordset("table1 query")


With XL
   .Visible = True
   .Workbooks.Open vFile
   .Sheets("Sheet1").Select
   .Range("c4").Select
   .ActiveCell.CopyFromRecordset rst
   .ActiveWorkbook.Save
End With
Set rst = Nothing
Set XL = Nothing
End Function

Thanks

[pbaldy]

The line to set the recordset would use the qdf variable instead of CurrentDb.

[Gicu]

Try:

Public Function Access2XL()


Dim rst as DAO.Recordset
Dim XL As Excel.Application


Dim vFile
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set XL = CreateObject("excel.application")


Set db = CurrentDb
Set qdf = db.QueryDefs("table1 query")
 
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm



vFile = "\\Mac\Home\Documents\TEST DB\testss.xlsx"




'Set rst = CurrentDb.OpenRecordset("table1 query")
Set rst=qdf.openrecordset


With XL
   .Visible = True
   .Workbooks.Open vFile
   .Sheets("Sheet1").Select
   .Range("c4").Select
   .ActiveCell.CopyFromRecordset rst
   .ActiveWorkbook.Save
End With
Set rst = Nothing
Set XL = Nothing
End Function

[ViNNiE]

Hi Gicu,

Thank you for your reply.

I have entered the above and has seemed to fix this problem.

The next issue I am having however is a runtime error.

Runtime Error'2482' .Microsoft Access cannot find the name 'Enter Date' you entered in the expression.

'Enter Date' being the parameter I set in the criteria column on the design view in my query.

Thanks Again

A A Ron

[ViNNiE]

Hi Paul,

Thanks for your reply.

I'm not exactly sure wat yo meant by that.

As said before I dnt have much experience with the VBA side of Access.

Thanks

A A Ron

[pbaldy]

Gicu gave you the code i meant. The code is meant to handle form parameters, so you're getting an error. It doesn't know what value you want to enter. I'd have the user enter a date in a form textbox and have the query get it from there. Most of us avoid the bracketed parameter method.

[ViNNiE]

Hi All,

Just an update.

I have got it working by using a form to run the script and take the date needed.

Thank you both for your help Paul and Gicu.

A A Ron

[pbaldy]

Happy to help and welcome to the site by the way!