Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > VB.NET 2002/2003 Basics
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 3rd, 2006, 10:52 PM
Authorized User
 
Join Date: Nov 2004
Location: North Ryde, NSW, Australia.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Automating Excel from Access

Hi all,

I have an MS Access 2000 application send the data to Excel spread sheet, this done via a VBA code runs behind a button on a form, the application works well when the user run the procedure for the first time.

But when trying to execute it again it gives an error and stops execution, if the user quits the application and restarted again, the code will work for the first time.

I am using these variable Excel Application (xlapp) Workbook (xlbook) and Work sheet (xlsheet) where I declare them at the beginning and set them to nothing before End Sub statement.

Please help me how to make the application work every time properly without quitting after every time Excel is being called from the application.

Thanks


Reply With Quote
  #2 (permalink)  
Old May 5th, 2006, 10:06 AM
Registered User
 
Join Date: May 2006
Location: Carriere, MS, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Zaff,

It's been a while since I've worked with VBA, but I recall a problem I had like yours, and it had to do with closing out the connection after doing the operation. If I didn't do that, the application would still be open, and I would get an error because the program was trying to create the connection when it was already connected. Is this the case for you?
Reply With Quote
  #3 (permalink)  
Old May 8th, 2006, 01:48 AM
Authorized User
 
Join Date: Nov 2004
Location: North Ryde, NSW, Australia.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mate,
Thanks for you kind reply, it is kind of close to my problem.

I am using Access application as s frond end (MDB file) to contact another Access Database in the back end, tables in the front end are link tables to ones in the backend.

Set db = currentdb - this line in my VBA is to initialise the DB and before the of end sub I kill it by: Set db = nothing

and I still have the probelm


Reply With Quote
  #4 (permalink)  
Old May 8th, 2006, 10:49 AM
Registered User
 
Join Date: May 2006
Location: Carriere, MS, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Zaff,

What kind of error are you getting? It may help in figuring out what is causing the problem.
Reply With Quote
  #5 (permalink)  
Old May 8th, 2006, 08:14 PM
Authorized User
 
Join Date: Nov 2004
Location: North Ryde, NSW, Australia.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dragondayz,

Scenrio: the user will ask the forntend application to output data to Excel file

Steps: Access will do:
    1- Run the query to collect data from the tables which are Linked tables to the Back end
    2- Create Excel objects
    3- Output the data to Excel
    4- Save the workbook as file in a certian location
    5- quit excel.
    5- Distroy the Objects by using set obj = nothing

Reply With Quote
  #6 (permalink)  
Old May 8th, 2006, 08:20 PM
Authorized User
 
Join Date: Nov 2004
Location: North Ryde, NSW, Australia.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry mate I hit enter by mistake so the reply went unfinished,

In real application when user press the button on the form to start the otuput process Access will go throught all the steps.

The problem starts when the user wants to output to Excel again, then Access will preform step1, and step2 and when it comes to select a a certian cell in Excel worksheet it gives an error meassge stating that it is out og range.

If the user quits the appilcation and relogs again the application will output to Excel for the first use, then after the erro appears.

Thanks.

Reply With Quote
  #7 (permalink)  
Old May 11th, 2006, 09:02 AM
Registered User
 
Join Date: May 2006
Location: Carriere, MS, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Zaff,

This might be a stupid question but... When it goes to select a cell in Excel, do you have a watch on the variable to see if it is out of range or to see what is being passed to Excel? It sounds like there is something being left behind from previous execution... a variable not being reset, an object not being closed... something.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting excel data to Access using excel VBA ShaileshShinde VB Databases Basics 1 April 26th, 2006 07:57 AM
javascript automating through excel... darkhalf Javascript 0 March 3rd, 2006 01:26 PM
Automating an access database miller2000 Access 5 June 23rd, 2004 09:38 PM
Excel automating problem xwang4 Excel VBA 3 September 19th, 2003 01:37 PM



All times are GMT -4. The time now is 06:22 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.