Export help

[zgray]

Im trying to export a query to specific columns in excel and im using this code.

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("filePath")
Set xlWS = xlWB.Worksheets("Sheet1")
Set rst = CurrentDb.OpenRecordset("queryName")
xlWS.Range("E2").CopyFromRecordset rst
xlWB.Save
rst.Close
Set rst = Nothing

I found on another thread here yesterday. I tried it on other querys and it works but doesn't work on this specific one. I get an error on the "set rst = currentDb" line. And to my knowledge it doesn't work because I have a between two dates filter in it. So when I run it, I get an error saying I have too few parameters, expecting 2. And I have no idea how to fix it...

Also it seems to be exporting the lookup id's not the value when exporting(on the query that it did work on)

THanks for your help, it's been pretty frustrating.

 

[pr2-eugin]

zgary, looks like the queryName is a Parameterized Query.. As in it expects the user to input the values to be passed to the query.. That is the reason it comes up with the Expected error.. Either make the Query dynamic.. As in use..

Set rst = CurrentDB.OpenRecordset("SELECT theFields FROM theTable WHERE someField = 75")

Or look into the MSDN link..

 

[zgray]

Well you put me on the right track I think but I still cant seem to get it to work.

I put

Set rst = CurrentDb.OpenRecordset("SELECT * FROM table WHERE DateField BETWEEN " & Me.txtStartDate & " AND " & Me.txtEndDate & "")

And It runs and I get no error but when I got to my excel sheet there is no data in it when I know for a fact there is data between the two dates. But when I replace the dates with just Date()-2 and Date()-1 it works just fine.

 

[pr2-eugin]

Sounds like there is some International Date problem.. Try changing your Query as such..

Set rst = CurrentDb.OpenRecordset("SELECT * FROM table WHERE DateField BETWEEN [COLOR=Red][B]#" & Format(Me.txtStartDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.txtEndDate, "mm\/dd\/yyyy") & "#"[/B][/COLOR])

 

[zgray]

That was it! It works great now.

 

[pr2-eugin]

Glad to help.. Good Luck !!