473,326 Members | 2,061 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,326 software developers and data experts.

HELP!Access VBA calling Excel problem

Ho I need help. I am running a piece of code which opens a spreadsheet deletes one of the worksheets and then saves it.
My problem is that although the spreadsheet opens and I can delete the sheet ok when I try to save it I get an error message that the object isn't supported. I have tried saving it as per code I have picked up on the forums with the same result.
Please kind people out there give me some suggestions.
The code I am using and the error message is as follows:-
Expand|Select|Wrap|Line Numbers
  1. Dim stdoc As String
  2. Dim appexcel As Object
  3. Dim book As Excel.Workbooks
  4. Dim sheet As Excel.Worksheet
  5. Dim myfile As String
  6. myfile = "H:\ESF DS\Interim Delivery Statement.xls"
  7. Set db = CurrentDb()
  8. Set rs = db.OpenRecordset("UKPRN", dbOpenDynaset)
  9. Set rs1 = db.OpenRecordset("TotalData", dbOpenDynaset)
  10. Set appexcel = CreateObject("Excel.Application")
  11. appexcel.DisplayAlerts = False
  12. DoCmd.SetWarnings False
  13. rs.MoveFirst
  14. uk = rs.fields("[UKPRN Number]").Value
  15. Debug.Print uk
  16. Forms!frmentry!Combo77 = uk
  17. appexcel.Workbooks.Open myfile
  18. appexcel.Visible = True
  19. appexcel.Sheets("ukprnquery").Select
  20. appexcel.Sheets("ukprnquery").Delete
  21. appexcel.Workbook.SaveAs (myfile)
ERROR MESSAGE READS : Object doesn;t support properties or method.
Jul 11 '08 #1
4 1585
Stewart Ross
2,545 Expert Mod 2GB
Hi. We'd need to see the code you are using to do your deletion followed by save; without this it really is impossible to advise you what might be wrong.

-Stewart
Jul 11 '08 #2
Code posted - would be grateful for help
Jul 11 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
Hi. There is a minor error in your SaveAs statement, which should refer to the current active workbook:
Expand|Select|Wrap|Line Numbers
  1. appexcel.ActiveWorkbook.SaveAs (myfile)
Although this next point has nothing to do with your error, I should also point out that you don't need line 19, the one selecting the sheet before you delete it. You may have used the macro recorder to record what happens when you delete a sheet, but when writing VBA code yourself as long as you refer to the correct object you don't need to select it before performing an action. I mention this because object selection when using Excel as an automation server from code running in another application is a real performance drain and is to be avoided if at all possible.

-Stewart

ps if you find that Excel is asking for confirmation of actions such as Save or Delete you can turn these off by placing the following lines in your code:

Expand|Select|Wrap|Line Numbers
  1. AppExcel.Application.DisplayAlerts = False
  2. ... 
  3. (rest of your code as before)
  4. ...
  5. AppExcel.Application.DisplayAlerts = True
Jul 11 '08 #4
Stewart - Thank you so much! You're a star
Jul 11 '08 #5

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

Similar topics

21
by: Dave | last post by:
After following Microsofts admonition to reformat my system before doing a final compilation of my app I got many warnings/errors upon compiling an rtf file created in word. I used the Help...
9
by: Tom | last post by:
A question for gui application programmers. . . I 've got some GUI programs, written in Python/wxPython, and I've got a help button and a help menu item. Also, I've got a compiled file made with...
4
by: Sarir Khamsi | last post by:
Is there a way to get help the way you get it from the Python interpreter (eg, 'help(dir)' gives help on the 'dir' command) in the module cmd.Cmd? I know how to add commands and help text to...
2
by: Sudheer Kareem | last post by:
Dear All Please tell me how to assosiate help files with my Vb.net Project. Regards Sudheer
6
by: wukexin | last post by:
Help me, good men. I find mang books that introduce bit "mang header files",they talk too bit,in fact it is my too fool, I don't learn it, I have do a test program, but I have no correct doing...
6
by: d.warnermurray | last post by:
I am doing a project for school that involves creating help files for a html authoring tool. If you could help me with answers to some questions it would really help. 1. What tasks do you expect...
0
by: tbatwork828 | last post by:
If you were like me trying to figure out how to launch context sensitive help topic by the context id, here is the link: http://weblogs.asp.net/kencox/archive/2004/09/12/228349.aspx and if...
5
by: Steve | last post by:
I have written a help file (chm) for a DLL and referenced it using Help.ShowHelp My expectation is that a developer using my DLL would be able to access this help file during his development time...
8
by: Mark | last post by:
I have loaded Visual Studio .net on my home computer and my laptop, but my home computer has an abbreviated help screen not 2% of the help on my laptop. All the settings look the same on both...
1
by: trunxnirvana007 | last post by:
'UPGRADE_WARNING: Array has a new behavior. Click for more: 'ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"' 'UPGRADE_WARNING: Couldn't resolve...
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
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...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...

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.