Export Query to Excel File

**MMcCarthy** · Jun 12 '10, 09:13 AM

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.

Code:

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** · Jun 12 '10, 10:01 AM

With the permission of my brothers Experts

you can use this code

TO Un-View

Code:

DoCmd.OutputTo acOutputQuery, _
"yourQuery",acFormatXLS, "yrquey.XLS", 0

TO View

Code:

DoCmd.OutputTo acOutputQuery, _
"yourQuery",acFormatXLS, "yrquey.XLS", -1

(Medo)

**timleonard** · Jun 12 '10, 07:58 PM

Originally posted by msquared

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.

Code:

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

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** · Jun 12 '10, 08:11 PM

Originally posted by timleonard

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.

Hi Tim,

I was only using AnyEvent as a sample not specific to your code.

Your code should look like this ...

Code:

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.

**timleonard** · Jun 12 '10, 08:12 PM

Originally posted by ahmedtharwat19

With the permission of my brothers Experts

you can use this code

TO Un-View

Code:

DoCmd.OutputTo acOutputQuery, _
"yourQuery",acFormatXLS, "yrquey.XLS", 0

TO View

Code:

DoCmd.OutputTo acOutputQuery, _
"yourQuery",acFormatXLS, "yrquey.XLS", -1

(Medo)

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** · Jun 12 '10, 10:44 PM

Originally posted by msquared

Hi Tim,

I was only using AnyEvent as a sample not specific to your code.

Your code should look like this ...

Code:

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.

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** · Jun 12 '10, 10:53 PM

Did you comment out the line

Code:

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?

Code:

wkb.Worksheets("Template-Run Macro").Select

**timleonard** · Jun 12 '10, 11:00 PM

Originally posted by msquared

Did you comment out the line

Code:

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?

Code:

wkb.Worksheets("Template-Run Macro").Select

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** · Jun 12 '10, 11:30 PM

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.

Code:

    DoEvents

    DoCmd.Hourglass False 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQryName, strXLFile, True

Let me know if this makes any difference.

**timleonard** · Jun 13 '10, 07:45 AM

Originally posted by msquared

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.

Code:

    DoEvents

    DoCmd.Hourglass False 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQryName, strXLFile, True

Let me know if this makes any difference.

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.

Code:

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

**MMcCarthy** · Jun 13 '10, 10:15 AM

Originally posted by timleonard

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.

Code:

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

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** · Jun 13 '10, 04:01 PM

Originally posted by msquared

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 :)

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

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

**NeoPa** · Jun 14 '10, 01:54 AM

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** · Jun 14 '10, 02:25 AM

Originally posted by 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.

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

Export Query to Excel File

Export Query to Excel File

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment