Export From Access 2013 To Excel 2013

[esmatullaharifi]

Hello Dears.
I used this vba code to export the query from access to excel but it exports in excel97 format how to change this to get compatible with access 2013 and output must be excel 2013.

Option Explicit

Const fPath = "C:\My Documents\" 'adjust to suit
Const fname = "Formatter File.xls" ' Code file
Const fName2 = "Exported Data.xls" ' Data file to overwrite

Function OpenExcel()

On Error GoTo ErrorHandler

   Dim appExcel As Excel.Application
   Dim bks As Excel.Workbooks
   Dim sht As Excel.worksheet
   Dim strSheet As String
   Dim MyFile As String
   
   MyFile = fPath & fname2 'Point to the Data file

   DoCmd.OutputTo acQuery, "qryCourseScheduleRpt", "MicrosoftExcel(*.xls)", _
MyFile, True, "" 

   'Open the Code file and run the processing macro
   MyFile = fPath & fName ' point to the Code file
   Set appExcel = GetObject(, "Excel.Application")
   appExcel.Workbooks.Open (MyFile)
   Set sht = appExcel.ActiveWorkbook.Sheets(1)
   sht.Activate
   'appExcel.Application.Visible = True
   With appExcel
    .Run "OpenAndProcess" 'Change to suit name of Excel routine
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err = 429 Then
'Excel is not running; open Excel with CreateObject
      Set appExcel = CreateObject("Excel.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

 

[chicagocomputerclasses]

Have you tried simply changing .xls references in your code to .xlsx ?

DoCmd.OutputTo acQuery, "qryCourseScheduleRpt", "MicrosoftExcel(*.xlsx)", _
MyFile, True, ""

 

[Ben Nevis]

Your code references filenames with a .xls extension
Excel versions higher than 97 use the .xlsx extension

 

[esmatullaharifi]

I changed the extensions but problem something else that i cant find out that. VBA getting error in here.

   Dim appExcel As Excel.Application
   Dim bks As Excel.Workbooks
   Dim sht As Excel.worksheet

 

[healey21]

Have you checked the Reference to use Excel, in Access use Alt + F11 then select the Tools Menu and References and search down for Microsoft Excel 15.Object Library and click the box.

 

[chicagocomputerclasses]

--

 

[esmatullaharifi]

Yes I have already added the reference.

 

[chicagocomputerclasses]

try "Excel Workbook (*.xlsx)" instead of "MicrosoftExcel(*.xls)"

the fact that your Excel.worksheet is not capitalized would usually mean either library is not loaded or somewhere in your code you've made a function called worksheet which you should probably rename.

 

[esmatullaharifi]

What would be the library for Excel.Worksheet to add it.

 

[esmatullaharifi]

Yes I found the library and the code work well. But another problem. This code run a macro in formatter file.xls and i have a macro in Exported Data.xls and i want to run that macro in the exported data.xls Now the macro runs in formatter file.xls