I know next to nothing about VB

[Mike Hughes]

Please help I know next to nothing about VB.

I have the attached DB.
On the form I have a button which will send the results of the Access Query2 to an Excel spreadsheet-OK, this works and no problem.

But what I would like to do push the button and it will send the results of the Access Query2 to an existing spreadsheet called caseloadmgt.xls which is located on my desktop - C:\Documents and Settings\Jim.Black\Desktop\caseloadmgt.xls
And the next time I push the button to run the query again I want the new data to start 2 cells below the ending of the first query results.

I have tried to follow all the examples I could find and I guess I just don’t know enough about this to make it work. What I need is someone to correct/ or change my code and put the new/corrected code in the DB where it should go

 

[qdogfball]

Mike,

I do something like this but I append to a table in Access every time it is run, then I export that table to Excel.

This works fine.

I have 3 queries, the last which appends information to a table.

The information from that table gets extracted by the qryStats, and goes into the Download-MM-YY tab in the spreadsheet.

Private Sub Run_Stats_report_Click()

Dim db As DAO.Database

Set dbs = CurrentDb

' Runs the 3 queries to gather the information to export

DoCmd.RunMacro ("macroStatsbyReasonExport")

' Exports the report to the monthly tab in Excel

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryStats", "c:\stats\2007 Stats.xls", True, "Download" & Format(Now()-10, "mm-yy")

dbs.Close
MsgBox ("The report is ready!")

End Sub

 

[OlcayM]

Hi,

I believe this link will help you....
http://www.mvps.org/access/modules/mdl0035.htm

 

[Mike Hughes]

I guess the code looks good, but I don't know what to do with it or where to put it and how do I make it run. I'm not very smart......

 

[OlcayM]

Sending revised version of your mdb attached . Please do not forget "set a reference to "Microsoft Excel 8.0 Object Library" under Tools Menu/References first." as Ashish said. Of cource it can also be Microsoft Excel 9.0 Object Library.

HTH
OlcayM

 

[Mike Hughes]

Thank you OlcayM! This works great, I could not have figured it out with you. Mike