Export Query to Excel File

[timleonard]

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
 
 

[MMcCarthy]

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

[ahmedtharwat19]

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)

[timleonard]

@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]

@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.DisplayAlerts = 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.

[timleonard]

@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.

[timleonard]

@msquared
Ok i tried the modification and it still gives the same error "91 - Object variable or With block variable not set in Export_Submittal_to_Excel_Click" it also gives a "0 - in Export_Submittal_to_Excel_Click" and a "20 - Resume without an error in Export_Submittal_to_Excel_Click" 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 "FollowHyperlink strXLFile, NewWindow:=True" and this seems to stop the white screen issue.

[MMcCarthy]

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

[timleonard]

@msquared
Yes I did comment out the xlApp.DisplayAlerts = 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]

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.

[timleonard]

@msquared
No the DoEvents did not help...But I did put an Exit Sub after the If MsgBox and that seems to stop the code at the right spot and prevent the errors stated above from happening.

If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then  
        FollowHyperlink strXLFile  
 
    End If  
    Exit Sub
 

[MMcCarthy]

@timleonard
Weird ............. lol

I've never tried to program a follow hyperlink command after populating a spreadsheet but someone else may have done so and will have some ideas. At least its not causing a problem any more.

I'd love to know why this works though :)

[timleonard]

@msquared
I'd love to learn more as to why myself...

Thank you though for sticking with me to work the problem out

[NeoPa]

Without the Exit Sub, the process follows the code following and into the error-handling code. I would guess this is not required so there should be code that handles contolling the process after the execution of the hyperlink call (Not necessarily the hyperlinked process(es)). An Exit Sub is an example of something that would work.

It appears that the original code finished after that line anyway, so the modified code should unless the error handling code is required, which is very doubtful.

[MMcCarthy]

@NeoPa
NeoPa much as I love you, could you say that again in English please lol!

[TheSmileyCoder]

In the code you posted (msquared) you forgot to add a Exit Sub before the error handling. Therefore the error handling code will be called each time the sub is called, regardless of whether or not an error has occurred. If the Err has not been cleared, you might even end up seing an old error message.

I think the DoEvents actually solved the problem, but since the error handling code was executed without there actully being an error, it seemed to not solve it. With the addition of the Exit Sub you now no longer proceed into the error handling.

Hope that made sense :P

[MMcCarthy]

@TheSmileyOne
lol much clearer, now why can't NeoPa just say Mary you made a mistake and forgot the Exit Sub before the error code.

[NeoPa]

msquared: I'd love to know why this works though :)

I'm always a bit literal Mary. I wasn't trying to answer what was wrong with your code, but specifically why the fix worked. Luckily Smiley popped up and gave a more sensible answer anyway :)