473,569 Members | 2,621 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VBA Excel paste from access function fails every second time it runs!

I am pasting in a recordset from access to an excel sheet via VBA
successfully. Then my routine copies a range of cells and pastes them
into another range, this works fine every other time it runs. Every
other run of the code it fails at the line Selection.Copy with a
Runtime error 91:. I then run it again and it works fine. This is
really puzzling me a snippet of my code is below. Any ideas?
>>>>>>>>>>> >>>>>>>

ExcelApp.Workbo oks.Open ("C:\Test.xl s")

Set ExcelSheet = ExcelApp.Worksh eets("Sheet1")
ExcelSheet.Rang e("B13").CopyFr omRecordset rs
ExcelSheet.Rang e("O13:X13").Se lect
Selection.Copy ' Code fails here every second time it runs with Runtime
error 91:
ExcelSheet.Rang e(Cells(14, 15), Cells(rec + 12, 24)).Select
ActiveSheet.Pas te

ExcelApp.Active Workbook.Save
ExcelApp.Quit

Nov 13 '05 #1
2 6602
Try using
ExcelSheet.Sele ction.Copy

Also be sure to close your ActiveWorkbook before you do your Quit,
and set ExcelApp=Nothin g

Once you've run this code, it's a good idea to check in your Task Manager on
the Processes tab to make sure you're not leaving a "zombie" Excel instance.

HTH

<in**@lowerbill .co.uk> wrote in message
news:11******** *************@g 47g2000cwa.goog legroups.com...
I am pasting in a recordset from access to an excel sheet via VBA
successfully. Then my routine copies a range of cells and pastes them
into another range, this works fine every other time it runs. Every
other run of the code it fails at the line Selection.Copy with a
Runtime error 91:. I then run it again and it works fine. This is
really puzzling me a snippet of my code is below. Any ideas?
>>>>>>>>>>>> >>>>>>>

ExcelApp.Workbo oks.Open ("C:\Test.xl s")

Set ExcelSheet = ExcelApp.Worksh eets("Sheet1")
ExcelSheet.Rang e("B13").CopyFr omRecordset rs
ExcelSheet.Rang e("O13:X13").Se lect
Selection.Copy ' Code fails here every second time it runs with Runtime
error 91:
ExcelSheet.Rang e(Cells(14, 15), Cells(rec + 12, 24)).Select
ActiveSheet.Pas te

ExcelApp.Active Workbook.Save
ExcelApp.Quit

Nov 13 '05 #2
Thanks for the tips. Yes a "zombie" excel instance was being left.
Sorted by ensuring the ActiveWorkbook was properly closed. Also
hooking up to an already running instance when initiating. Solutions
to these problems found on this message board.

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
35509
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet and extract information from specific worksheets and cells. I'm not really sure how to get started with this process. I ran the COM Makepy...
1
10493
by: Alex | last post by:
Hi all, I've seen this noted in many posts, but nothing I've checked out gives me any clue on how to do this. Basically as my topic says, I have a DTS and I simply need to export some data from a table in MS SQL 2000 to an Excel spreadsheet. I also need to automate this process so it can run nightly and each new day a new spreadsheet...
7
4181
by: Ottar | last post by:
I've made a program sorting incomming mail in public folder. The function runs every minute by using the form.timer event. In Access XP it runs for weeks, no problem. Access 2003 runs the same code for 6 hours and stops. I've found the problem to be the Set MySession = CreateObject("MAPI.Session")
11
4038
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it again, Excel hangs. OR if I open Excel again (say from a desktop icon) before I close Access, Excel hangs. (this has happened for both 97 & 2000...
6
9340
by: Steve Richter | last post by:
I am getting error in a vbscript: ActiveX component cant create object: Excel.Application. The vbscript code is: Dim objExcel Set objExcel = CreateObject("Excel.Application") I am pretty sure it is a permission issue because the script works when I point the browser directly at the .htm file on the c: drive:...
8
9712
by: Sarah | last post by:
I need to access some data on a server. I can access it directly using UNC (i.e. \\ComputerName\ShareName\Path\FileName) or using a mapped network drive resource (S:\Path\FileName). Here is my problem: my vb.net program has problems with UNC. If the UNC server is restarted or goes off-line, my VB.net program crashes. The code for UNC...
2
3795
by: John Henry | last post by:
I posted the following message to the Pywin32 list but if anybody here can help, it would be appreciated very much. ============================ Hi list, I have a need to copy 3 rows of data from the top of my Excel spreadsheet to another location. I would have throught that this should be very straightforward since I've done a fair...
2
4917
by: Nicholas Dreyer | last post by:
The following error Run-time exception thrown : System.Runtime.InteropServices.COMException - Error loading type library/DLL. happens while running the code listed at the bottom of this message in the environment shown here: Operating System: Microsoft Windows Version 5.1 (Build
10
9656
by: afromanam | last post by:
Regards, Please help What I'm trying to do is this: (and I can't use reports since I must export to Excel) I export some queries to different tabs in an excel workbook I then loop through each tab and apply autowidth to columns and apply autofilter to the first row of every tab in the workbook. I've this down
0
7710
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7625
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8144
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7992
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5519
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3677
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2128
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1235
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
969
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.