I seem to be over my head and can use some help...
I have pieced together some code from several internet searched that is supposed to export a query from access to an existing excel file. I also wanted it to clear any existing data on the excel sheet before the query is exported. It seems to work sometimes, other times when the excel file opens it has a white screen and the toolbars are frozen, but if I close and reopen it all the data is there. The question I have is,
1. Which part of the code could be causing to problem with the screen
2. Does the code need more fine tuning and if so what is recommended
3. Is there a better way to do the export to an existing file and be sure all the contents of the sheet are overwritten
Thank You for any feed back you can offer -
Private Sub Export_Submittal_to_Excel_Click()
-
-
DoCmd.Hourglass True
-
-
Dim xlApp As Excel.Application
-
Dim wkb As Excel.Workbook
-
Dim wks As Excel.Worksheet
-
Dim strQryName As String, strXLFile As String
-
-
strDB = CurrentDb.Name
-
strCurrentDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))
-
-
strXLFile = "" & strCurrentDir & "Submit Road Rewards.xls"
-
strQryName = "Submit Road Rewards" 'Query Name
-
-
Set xlApp = CreateObject("Excel.Application")
-
Set wkb = xlApp.Workbooks.Open(strXLFile)
-
-
xlApp.Visible = False
-
xlApp.DisplayAlerts = False
-
xlApp.Application.ScreenUpdating = True
-
-
wkb.Worksheets("Submit_Road_Rewards").Cells.ClearContents 'Delete
-
wkb.Worksheets("Template-Run Macro").Select
-
-
wkb.Save
-
wkb.Close
-
xlApp.Quit
-
-
Set xlApp = Nothing
-
Set wkb = Nothing
-
Set wks = Nothing
-
-
DoCmd.Hourglass False
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQryName, strXLFile, True
-
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
-
FollowHyperlink strXLFile
-
-
End If
-
-
End Sub
-
-
17 7500 MMcCarthy 14,534
Recognized Expert Moderator MVP
There doesn't appear to be any obvious problems with your code. I suspect the problem lies with the RESUME.XLW file. When you use Office automation to create a new instance of Excel it is done with the creation of a workspace, hence the RESUME.XLW file.
If for any reason your code gets interrupted or your file doesn't properly save, this workspace will retain all the information from the previous attempt. Then when you try to run it again it gets stuck. A check you can do is to run task manager and look at the process list. If you quit all open excel files on your desktop first, then there shouldn't be any Excel.exe processes running. If there is then they are instances that didn't properly close.
One thing I've found to be very useful is to use error handling to force the xlapp.quit code to run even if the code is interrupted. - Private Sub AnyEvent()
-
On Error GoTo Err_AnyEvent
-
-
'Your code here
-
-
Exit_AnyEvent:
-
-
' from your code move these statements to the exit point
-
' not sure about the workbook save statement, you may want to leave that outside of this point
-
wkb.Save
-
wkb.Close
-
xlApp.Quit
-
-
Set xlApp = Nothing
-
Set wkb = Nothing
-
Set wks = Nothing
-
-
Err_AnyEvent:
-
-
MsgBox Err.Number & " - " & Err.Description & " in AnyEvent"
-
Resume Exit_AnyEvent
-
-
End Sub
With the permission of my brothers Experts
you can use this code TO Un-View - DoCmd.OutputTo acOutputQuery, _
-
"yourQuery",acFormatXLS, "yrquey.XLS", 0
TO View - DoCmd.OutputTo acOutputQuery, _
-
"yourQuery",acFormatXLS, "yrquey.XLS", -1
( Medo)
@msquared
Thanks for the replys. I tried the On Error GoTo Err_AnyEvent. I am not sure but I think there must be an error somewhere because after the code runs and it follows the link to open the XLS file, it still at times displays the white screen. I did check the task manager and there is no other instance of Excel shown. Also once the XLS file is closed and the code should exit out it displays a error message of "91 - Object variable or With block variable not set in Anyevent" This message does not respond to the "ok" button and the only way to close it is the end task from the task manager.
MMcCarthy 14,534
Recognized Expert Moderator MVP @timleonard
Hi Tim,
I was only using AnyEvent as a sample not specific to your code.
Your code should look like this ... - Private Sub Export_Submittal_to_Excel_Click()
-
On Error GoTo Err_Export_Submittal_to_Excel_Click
-
-
DoCmd.Hourglass True
-
-
Dim xlApp As Excel.Application
-
Dim wkb As Excel.Workbook
-
Dim wks As Excel.Worksheet
-
Dim strQryName As String, strXLFile As String
-
-
strDB = CurrentDb.Name
-
strCurrentDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))
-
-
strXLFile = "" & strCurrentDir & "Submit Road Rewards.xls"
-
strQryName = "Submit Road Rewards" 'Query Name
-
-
Set xlApp = CreateObject("Excel.Application")
-
Set wkb = xlApp.Workbooks.Open(strXLFile)
-
-
xlApp.Visible = False
-
xlApp.DisplayAlerts = False
-
xlApp.Application.ScreenUpdating = True
-
-
wkb.Worksheets("Submit_Road_Rewards").Cells.ClearContents 'Delete
-
wkb.Worksheets("Template-Run Macro").Select
-
-
Exit_Export_Submittal_to_Excel_Click:
-
-
wkb.Save
-
wkb.Close
-
xlApp.Quit
-
-
Set xlApp = Nothing
-
Set wkb = Nothing
-
Set wks = Nothing
-
-
DoCmd.Hourglass False
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQryName, strXLFile, True
-
-
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
-
FollowHyperlink strXLFile
-
-
End If
-
-
Err_Export_Submittal_to_Excel_Click:
-
-
MsgBox Err.Number & " - " & Err.Description & " in Export_Submittal_to_Excel_Click"
-
Resume Exit_Export_Submittal_to_Excel_Click
-
-
End Sub
I would also comment out the line
xlApp.DisplayAl erts = False
That way you can see what errors may be occuring in Excel. You can always put it back later when your code is fixed.
@ahmedtharwat19
Thanks for the reply, but I am under the impression that the DoCmd.OutputTo command will overewrite the file. I need to output to an existing XLS file as the data is then processed further from the file.
@msquared
Ok i tried the modification and it still gives the same error "91 - Object variable or With block variable not set in Export_Submitta l_to_Excel_Clic k" it also gives a "0 - in Export_Submitta l_to_Excel_Clic k" and a "20 - Resume without an error in Export_Submitta l_to_Excel_Clic k" The only way to get out of the error loop is through the task manager.
BTW- I added the switch NewWindow:=True to the line "FollowHyperlin k strXLFile, NewWindow:=True " and this seems to stop the white screen issue.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Did you comment out the line - xlApp.DisplayAlerts = False
and if so what excel alert messages did you see?
Also I'm not sure what this line of code is doing? - wkb.Worksheets("Template-Run Macro").Select
@msquared
Yes I did comment out the xlApp.DisplayAl erts = False and it does not give any errors related to the openning and closing of the excel file. The errors come after the followhyperlink command. In regards to the wkb.Worksheets( "Template-Run Macro").Select. ..I have a macro on that worksheet so that when the file is opened it is already on the required worksheet.
MMcCarthy 14,534
Recognized Expert Moderator MVP
The issue may simply be a timing one. Add in a DoEvents command to make sure all code executes fully before the transfer spreadsheet statement runs. -
DoEvents
-
-
DoCmd.Hourglass False
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQryName, strXLFile, True
Let me know if this makes any difference.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Robin Cushman |
last post by:
Hi all,
I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel spreadsheet. I'm having trouble
with my code at the point at which it hits ".ReadRecords" -- the
module just runs and runs without generating anything. I've...
|
by: David |
last post by:
(dhl)Using 2K. I have a query I want to export to excel. There are
35,000+ lines for this query. I created a macro to run on a schedule
to export the excel file to a network shared drive. I can manual
export the query to excel, but when I run the macro, access said
"There are too many rows to output, based on limitation specified by
the...
|
by: sanju |
last post by:
hi guyz .. can anyone send sample code to export a excel file into sql
server table using web interface .
ie programatically read and select some columns of excel and insert into a
table.
please give me an idea on this
thank u very much
san
auckland
|
by: sanju |
last post by:
hi guyz .. can anyone have an idea to export a excel file into sql server
table using web interface .
please give me an idea on this
thank u very much
san
auck
|
by: Server Control |
last post by:
Hi,
I am exporting my datagrid to Excel using the standard Export to Excel
Method using :
dg.RenderControl(). and
Page.Response.AddHeader("Content-Disposition", "attachment;filename=" +
FullFileName); The problem is that while Exporting, the user is
prompted twice with "Open/Save As" dialog box on some machines. I am
using a Windows XP m/x...
| |
by: cassey14 |
last post by:
Hi!
Is it possible that the result in the subform will export to excel file??
Please help..
|
by: johnlim20088 |
last post by:
Hi,
Can someone help me on this?
What can I done on code?
I able to export csv file in http://mypage.com,
but when i change to https://mypage.com,
it unable to export, error "cannot display internet site.."
can someone help me?
|
by: sethson22 |
last post by:
Hello ,
How to import and export the excel file into Access through VB Code
let me know , if anybody knows the same
|
by: sbettadpur |
last post by:
hello,
i want to export reports into excel file,
i got some class files which will export into excel file.
Now i need to change the font size, font should be in bold.
how i can do that one.
|
by: Ivan Stefanov |
last post by:
Hello, I have the following problem:
I created a website project and I want in that website to be make a button, which will export some data in an excel file. This is the code that I have:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel...
|
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...
| |
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |