By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Trying to export a query to an existing excel template.

P: 2
Hi Guys, really no idea about VBA code but I trying to learn.

I have a database which I am trying to set up to replace a number of existing excel documents. I do however need the database to transfer a query to an existing excel template.

So for example the database is called "test".
The query is called "testquery"
The excel document is called "testspreadsheet"
The Tab it needs to go to is called "template"
And I need it to start pasting into cell A2.

I have been looking at the transfer spreadsheet method but I my limited understanding of VBA is becoming a problem.

Any help or advice would be greatly appreciated.
Oct 13 '17 #1
Share this Question
Share on Google+
1 Reply

P: 2
Mananged to get the code to work incase anybody else comes across this and wants a fix.

Expand|Select|Wrap|Line Numbers
  1. Public Sub Access2XL()
  2. Dim rst
  3. Dim XL As Excel.Application
  4. Set XL = CreateObject("excel.application")
  5. Dim vFile
  7. vFile = "C:/testspreadsheet.xls"
  9. Set rst = CurrentDb.OpenRecordset("testquery")
  10. With XL
  11.    .Visible = True
  12.    .Workbooks.Open vFile
  13.    .Sheets("Template").Select
  14.    .Range("A2").Select
  15.    .ActiveCell.CopyFromRecordset rst
  16.    .ActiveWorkbook.Save
  17. End With
  18. Set rst = Nothing
  19. Set XL = Nothing
  20. End Sub
I also had to make sure the Excel Object library in the VBA Menu references was ticked.
Oct 13 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.