Export to Excel 2000

[Richard_Marx]

Access 2000
Excel 2000

I want to push a button, have that button run a query, and then take the query results and transpose them into an Existing Excel Workbook so that the results of the query are written starting in B10 and going down, so B11, B12 etc etc. What is the access VBA to perform this?

EDIT ---


Also, give a popup asking if the user wants to close access-- if yes close access, if no keep access open.

[June7]

What do you mean by 'transpose'?

Access export to Excel is a common topic - search forum or Google. Here is a start:
http://forums.aspfree.com/microsoft-...el-413629.html



If MsgBox("Do you want to quit Access?", vbYesNo) = vbYes Then
Application.Quit
End If

[Dal Jeanis]

Geoff G posted the necessary code here -

http://social.msdn.microsoft.com/Forums/office/en-US/adf2dc3f-d43e-451f-ad58-5617865b1e82/access-2007-using-vba-to-export-a-table-or-recordset-into-excel-2007

1) Verify against Geoff's DLL notes for MDB files.

2) I don't see anything that makes me think it won't work for 2000 -- 2000 did use DAO 3.6 -- but you may find you need to adjust something or other.

3) Obviously, as always, do testing against a test copy of your database and back up often.

[Richard_Marx]

Geoff G posted the necessary code here -

http://social.msdn.microsoft.com/Forums/office/en-US/adf2dc3f-d43e-451f-ad58-5617865b1e82/access-2007-using-vba-to-export-a-table-or-recordset-into-excel-2007

1) Verify against Geoff's DLL notes for MDB files.

2) I don't see anything that makes me think it won't work for 2000 -- 2000 did use DAO 3.6 -- but you may find you need to adjust something or other.

3) Obviously, as always, do testing against a test copy of your database and back up often.

The code on that website anytime I try to execute gives me
Error 3061 To few parameters. Expected 2?

[Richard_Marx]

Also, what about transposing the data? I do not see either posted link covering that topic.

[Dal Jeanis]

"Too few parameters" generally means that one of your variables hasn't been set (it's Null).

If a direct copy won't work for you due to the requirement to transpose, there are two solutions.

(1) (A) create a temp worksheet (or a temp workbook).
(B) do the direct copy into the temp worksheet.
(C) Use .Copy to pick the data back up to the clipboard.
(D) Use .PasteSpecial with Transpose = True to paste the data to the right worksheet area.
(E) Delete the temp worksheet.


(2) Using VBA, read the data line by line from the query's recordset and paste it column by column in the worksheet. If your number of columns is manageable, then this might be the way to go. This thread has some sample code. http://www.access-programmers.co.uk/...d.php?t=203513 You, of course, would reverse the order of the .Cells (x, y) in your code.

That's two of the ways you can skin your cat.

Pick one and see if you can make it happen. If you run into problems, paste up your code with the word CODE in square brackets before [] the code, and the word /CODE in square brackets after, and we'll see what we can do to help you work it out.

[Richard_Marx]

"Too few parameters" generally means that one of your variables hasn't been set (it's Null).

(2) Using VBA, read the data line by line from the query's recordset and paste it column by column in the worksheet. If your number of columns is manageable, then this might be the way to go. This thread has some sample code. http://www.access-programmers.co.uk/...d.php?t=203513 You, of course, would reverse the order of the .Cells (x, y) in your code.

This approach seems like it will cover what I am after. I am still hitting the same error tho (To few parameters), on the line Set rst = CurrentDB.OpenRecordset("ShowData") 'ShowData is my query name that displays the results I want to export.

[Dal Jeanis]

1) In the immediate window (alt-f11 to open, if needed), enter this

DoCmd.OpenQuery("ShowData")

This should cause the query to open in the database window.

If it doesn't work, it means something is wrong with the query itself. Try rebuilding the query from scratch with a new name.

If it works, then I'm stumped. All the parms for OpenRecordset are optional, besides the query name.

[Richard_Marx]

When I use DoCmd.OpenQuery("ShowData") nothing is returned in the immediates window.

My query takes parameters from the user form to know the date range of which to display the data. The query executes perfectly on it's own, and perfectly if I set the onclick() Event of the command button to run the query.

Just attempting to display the results in this method is not working

[Dal Jeanis]

1) That's why I said it should cause it to open in the database window.
2) "Parameters off the user form." Got it. If the form isn't open, or the fields haven't been set, then that returns nulls, which errors for a missing parameter. That makes total sense from the immediate window.

So, the question is why CurrentDB.OpenRecordset isn't being able to find those parameters, if it's being executed from that form with the parms set.

At this point, I'd tend to be switching my focus to those parms. I'd use temp variables, have the afterupdate of the parm controls on the form setting the value of the temp variables, and before executing the OpenRecordset, check those values and default them to long ago and long from now. I might also clone the query and use a second query that autodefaults using NZ. Something like Between NZ(startdate, #01/01/1900#) And NZ(enddate, #12/31/2100#) and obviously adjust for any required quotes.

[ssanfu]

I found an error in the code from
http://social.msdn.microsoft.com/For...nto-excel-2007

There are two backslashes after the drive letter - there should only be 1 backslash.

I modified and tested the code in A2000, so the attached MDBs execute with no errors (for me).
There are two MDBs - one to just transfer the data and one to 'transpose' the data.

--------------------------------
There are some good examples of Access to Excel at Ken Snells's site
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

Also see:
Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)
http://www.accessmvp.com/kdsnell/EXC...m#WriteRstFile

[Richard_Marx]

1) That's why I said it should cause it to open in the database window.
2) "Parameters off the user form." Got it. If the form isn't open, or the fields haven't been set, then that returns nulls, which errors for a missing parameter. That makes total sense from the immediate window.

So, the question is why CurrentDB.OpenRecordset isn't being able to find those parameters, if it's being executed from that form with the parms set.

At this point, I'd tend to be switching my focus to those parms. I'd use temp variables, have the afterupdate of the parm controls on the form setting the value of the temp variables, and before executing the OpenRecordset, check those values and default them to long ago and long from now. I might also clone the query and use a second query that autodefaults using NZ. Something like Between NZ(startdate, #01/01/1900#) And NZ(enddate, #12/31/2100#) and obviously adjust for any required quotes.

I think I see what the problem is (per your help). My ShowData query is running about 8 subquery's to show all of the data in ONE centralized location. When I attempt to run the code, it looks like the VBA is just attempting to execute my ShowData query and not run the subquery's which take the date parameters, and is causing the query to have no data to show.

For example, I have a query that this is the SQL for:

SELECT COUNT(combinedTable.UserID) As CountOfUID FROM cobinedTable WHERE (((combinedTable.UserID)="RM1219") AND ((combinedTable.LogInDate) Between[forms]![form1]![start]AND [forms]![form1]![end]

If I input parameters on the user form and run my ShowData query all is well, but it seems that when I Select Dates, and attempt to export to Excel that is where the underlying issue ensues.

[Richard_Marx]

I found an error in the code from
http://social.msdn.microsoft.com/For...nto-excel-2007

There are two backslashes after the drive letter - there should only be 1 backslash.

I modified and tested the code in A2000, so the attached MDBs execute with no errors (for me).
There are two MDBs - one to just transfer the data and one to 'transpose' the data.

--------------------------------
There are some good examples of Access to Excel at Ken Snells's site
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

Also see:
Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)
http://www.accessmvp.com/kdsnell/EXC...m#WriteRstFile

Thank you for those examples. It helps me see the code in action.

[Dal Jeanis]

Did you cut and paste, or did you type it in?

I saw this in the middle of that subquery:

FROM cobinedTable

I also note a missing space before AND in this part:

Between[forms]![form1]![start]AND [forms]![form1]![end]

[jo15765]

This code example, may be posted in someone else's suggested response, but it isn't this exact site, so thought I would add 2 cents in
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm