473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Close An Open Excel Spreadsheet from Access VBA

I have Access 2000 programmed to run a very simple code to run a query and OutputTo an Excel spreadsheet:

DoCmd.OutputTo acOutputQuery, "XLS Applicants", acFormatXLS, "C:\My RMBC Reports\Applicants.xls", True

If, however, the user attempts to run the code a second time, without closing the Excel spreadsheet, they get a Runtime Error. I need to know how to close the spreadsheet (if it is opened) and re-run the command.

Any help will yield my undying gratitude.
Sep 15 '08 #1
2 10734
jg007
283 100+
half a reply :)

I have not included any error checking to test if the file is open etc but incase it helps you can open excel first and then output the query and you will then have a handle to excel

You will also need to import microsoft excel object library

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim xe As New Excel.Application
  3. xe.Visible = True
  4.  
  5. DoCmd.OutputTo acOutputQuery, "XLS Applicants", acFormatXLS, "C:\Applicants.xls", True
  6.  
  7. xe.Workbooks.Close
  8. xe.Quit
  9.  
  10.  
Sep 16 '08 #2
Thank you for the code.. it does work. However, I actually want the spreadsheet to remain open after it is created. I want to close any open spreadsheet before the 'DoCmd.OutputTo...' is run and leave the resulting spreadsheet open until the code is run again.
Sep 23 '08 #3

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

Similar topics

1
by: Johnny Meredith | last post by:
Dear All, I have an Access database that tracks the progress of income tax audits. When the taxing authorities make a change (an "Adjustment"), I record the pertinent information in the...
6
by: Syvman | last post by:
Here's what I've got: I'm trying to grab some data out of an Excel spreadsheet and bring it into Access. I'm able to do it, but only if the Excel spreadsheet is not opened by any other users. I...
8
by: SteveS | last post by:
I'm attempting to close EXCEL from within my VB.NET application. Using the excel object library to write data to my spreadsheet is working fine but when I try to quit application object it does not...
0
by: ragtopcaddy via AccessMonster.com | last post by:
I am exporting queries to a 4 sheet XL workbook. The transfer spreadsheet method puts a ' in front of all of my numbers, necessitating a formatting procedure to remove them and properly format the...
2
by: Peter S. | last post by:
I have an ASP.NET page that invokes a web control written in C#. What I want to do is (based on the session ID) display a certain spreadsheet that exists on a network drive. I want the webcontrol...
3
by: toffee | last post by:
Hi all, I got a pre-formatted spreadsheet. would it be possible using js to copy the data from a table on the current webpage, open the spreadsheet and paste the content ? if so, anyone got any...
4
by: Scott | last post by:
I'm trying to get access to open, refresh and then close an excel spreadsheet for me. Below is the code I'm using to open and close Excel, I just can't get it to refresh my data linked back to my...
9
by: fahadqureshi | last post by:
I am running two visual basic modules in Access and keep coming across an annoying problem. Both the vb modules transfer an excel spreadsheet to an access database but for some reason after the...
0
by: Tony Hine | last post by:
Problem for Excel Developers One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets. MS Access is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.