Results 1 to 9 of 9
  1. #1
    Esmatullaharifi is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    40

    Question Export From Access 2013 To Excel 2013


    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.
    Code:
    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You dont need all that code, it is 1 line:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, vQry, vFile, True, vCaption

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    because of this

    "MicrosoftExcel(*.xls)"

  4. #4
    Esmatullaharifi is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    40
    Error happening in this Area:
    Code:
       Dim appExcel As Excel.Application
       Dim bks As Excel.Workbooks
       Dim sht As Excel.worksheet
    I also changed the "MicrosoftExcel(*.xls) to "Excel Workbook(*.xlsx) that work fine just problems are in variables.

  5. #5
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what is the error? have you set the appropriate references?

  6. #6
    Esmatullaharifi is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    40
    Yes, I have already add reference.

  7. #7
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so what is the error?

  8. #8
    Esmatullaharifi is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    40
    Yea I found the error thanks all of you.

  9. #9
    Esmatullaharifi is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    40
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Opening Excel in Access 2013
    By etorasso in forum Access
    Replies: 0
    Last Post: 12-18-2014, 03:10 PM
  2. Replies: 4
    Last Post: 03-28-2014, 10:54 AM
  3. Linking Excel, Word and PDF files in Access 2013
    By accessmacroman in forum Import/Export Data
    Replies: 4
    Last Post: 03-08-2014, 03:29 PM
  4. Outlook 2013 + Access 2013 + HTML
    By Yann63 in forum Programming
    Replies: 2
    Last Post: 11-26-2013, 02:39 PM
  5. Access 2013 Web App with Sharepoint 2013
    By miguel.escobar in forum Access
    Replies: 7
    Last Post: 06-17-2013, 09:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums