issues with saving and opening an Excel file via Access VBA

[purpledawn]

I have an access appliation where the user will run a query, and the results of the query will be displayed to them using an Excel file.

A macro writes the saved query to an Excel file, using the OutputTo action.

A sub writes additional metadata to cells in the Excel file, and does some minor formatting, using the Excel 9.0 automation tools. The workbook is opened using the Shell function.

Public Sub editWorkbook()

  Dim appXL As Excel.Application
  Dim wsXL As Excel.Worksheet
  Dim strcountryname, strwetlandname, strspeciesname, strsurveytypeES, strsurveytypeEN As String
    
  Set appXL = CreateObject("Excel.Application")
  
  strsurveytypeEN = DLookup("[SURVEY_TYPE_EN]", "SURVEY_TYPE", "[SURVEY_TYPE_ID] =" & intsurveytype)
  strsurveytypeES = DLookup("[SURVEY_TYPE_SP]", "SURVEY_TYPE", "[SURVEY_TYPE_ID] =" & intsurveytype)
  strcountryname = DLookup("[COUNTRY_NAME]", "COUNTRY", "[COUNTRY_CODE] = '" & strcountrycode & "'")
  
  appXL.Workbooks.Open ("C:\TEMP.XLS")
  
  Set wsXL = appXL.ActiveWorkbook.activeSheet
  
  With wsXL
    .Rows("1:6").Insert shift:=xlDown
    .Range("B2").value = "País/Country:  "
    .Range("C2").value = strcountryname
    .Range("B4").value = "Tipo de conteo/Survey Type:  "
    .Range("C4").value = strsurveytypeES & "/" & strsurveytypeEN
    .Range("B5").value = "Fechas/Dates:  "
    .Range("C5").value = datefirstdate & " - " & datefinaldate
    .Range("B2:B5").HorizontalAlignment = xlRight
    .Columns("A:A").EntireColumn.AutoFit
    .NAME = "Survey Data"
    
  End With
  
  appXL.ActiveWorkbook.Close True
  appXL.Quit
  
  Shell "C:\Program Files\Microsoft Office\Office\Excel.exe C:\temp.xls", 1
  
End Sub

I'm having two problems that I can't figure out. One is that when it closes the workbook, it asks if you would like to save the file in the current version of Excel, since is was originally saved in Excel 5. Is there possibly a way to change the Excel version that the macro saves the query to? Is there a way to stop this message from appearing?

The other problem is that when the Shell function opens the file, it doesn't make it active on the desktop. Instead, it keeps Access active, and flashes the Excel icon on the taskbar. I need the Excel file to open up on the screen. Is there maybe a way to do this using the automation tools instead?

If anyone can help, I'd be very grateful.

 

[VBslammer]

Instead of closing the workbook and quitting Excel, just save the workbook and make the application visible:

  appXL.ActiveWorkbook.Save
  
  appXL.Visible = True

VBSlammer

Unemployed in Houston, Texas

 

[purpledawn]

Thank-you so much. That makes it work perfectly.

I hope you find work soon!