472,353 Members | 1,935 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Saving Access Query in Excel

I was using Docmd.Transferspreadsheet to to populate query data in Excel.
My code so far
Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As Excel.Application
  2. Dim xlWb As Excel.workbook
  3. Dim xlWs As Excel.Worksheet
  5. Set x1App = New Excel.Application
  6. xlApp.Visible = True
  7. Set xlWb = xlApp.Workbooks.Open("C:\Daily Open Report Template.xls")
  8. Set x1Ws = x1Wb.ActiveSheet
  9. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "7aquery", "c:\DAILY OPEN REPORT TEMPLATE.xls", True, x1Ws
The idea is to populate one of the queries in the worksheet(x1Ws) and work on this sheet to make different excel functions. However, this code throws this error:
Object Varialbe or with block variable not set.
I have set all the objects as it is above.
What am i doing wrong here?
How should I code to work with excel functions on query exported by docmd.transferspreadsheet?
Thanks for your time.
Nov 2 '07 #1
4 2592
3,080 Expert 2GB
To direct the data from a query to a specific sheet just use this one-liner:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "7aquery", "c:\DAILY OPEN REPORT TEMPLATE.xls", True, "x1Ws"
Nov 2 '07 #2
To direct the data from a query to a specific sheet just use this one-liner:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "7aquery", "c:\DAILY OPEN REPORT TEMPLATE.xls", True, "x1Ws"
Hi Nic,
Thank you very for your response.
I wanted to work use Excel Functions after transferring the data. Like formatting and making the spreadsheet presentable.
How can I make this happen after transferring the data with DoCmd? This was reason behind me sending the data in x1Ws so that i get the Excel Application object.

Nov 2 '07 #3
3,080 Expert 2GB
Then first use the DoCmd.TransferSpreadsheet to create the Workbook with the sheet and (as you do) open the created spreadsheet to manipulate the sheet as you like with automation.

For some idea's on automation check:
Application Automation

Nov 2 '07 #4
32,511 Expert Mod 16PB

Please try to remember in future when posting to TheScripts :
  • To use the [ CODE ] tags provided for code (The # button).
  • To post the question in the Access Forum rather than the Access Articles area.
We can fix them up afterwards for you but it would save time and effort if you'd remember to do them yourself.

Anyway, welcome to The Scripts :)
Nov 3 '07 #5

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

Similar topics

by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or...
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database...
by: Helmut Blass | last post by:
hi Access-freaks, in MS Access, I generate an Excel file where the user should have the possibility to determine the excel file name by file...
by: etwebbox | last post by:
Hoping someone has some ideas on how to solve this issue: I have a macro in excel which runs a query against an Access DB (it actually calls a...
by: tt40 | last post by:
Anyone know how to prevent Access 2002 from automatically breaking all the incorrect joins in a query and then automatically saving the broken...
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with...
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe...
by: NeoPa | last post by:
Many of us have noticed that there are some very useful functions available to you when using Excel, but these same functions are not available, as...
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.