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

    Question Help to Modify this vba code.


    Hi to every one,
    I am using this code to export my query data to excel program but now i changed the destination excel file to macro enabled file to in this case i also changed the extensions in my code from xlsx to xlsm but the code does not now the xlsm extension. How i can successfully change this code to work with macro enable workbook of excel.
    Code:
    Option Compare Database
    
    Option Explicit
    
    Const fPath = "C:\Users\Esmatullah Arifi\Documents\"
    Const fName2 = "Exported Data.xlsx" ' 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, "asa", "Excel Workbook(*.xlsx)", _
    MyFile, True, ""
    
       appExcel.Workbooks.Open (MyFile)
       Set sht = appExcel.ActiveWorkbook.asa
       sht.Activate
       appExcel.Application.Visible = True
       With appExcel
        .Run "Edit"
       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,549
    you cant. the only format is .xlsx
    then open it in xl and save as .xlsm

  3. #3
    trevor40's Avatar
    trevor40 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    407
    I do this after formatting my sheets, it saves the file as xlsm then deletes the old file. it can all be done from access. (90% of code removed for clarity.)


    this creates the files...

    Code:
    Private Sub Command147_Click()          'create manual time sheet
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim rstActual As DAO.Recordset
        Set rstActual = db.OpenRecordset("manual_sheet")
        gg = "C:\aaa\timesheets\Employee timesheet master.xls"
        bb = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
        fn = "FirstName"
        Ln = "LastName"
        DoCmd.RunSQL "DELETE * FROM manual_sheet"
        t = Forms![main menu].start_date
        tt = Forms![main menu].End_date
        aa = tt - t
            For i = 1 To aa
            Set rstActual = db.OpenRecordset("manual_sheet", dbOpenDynaset)
            rstActual.AddNew
            rstActual!new_date = t
            rstActual.Update
            rstActual.Close
            t = t + 1
        Next i
        For i = 1 To bb
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch for manual sheet", gg, True, "FirstName" & i & " " & "LastName" & i
        Next i
        Call format_sheets_now
        t = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & "          And saved in the following directory" & vbCrLf & vbCrLf & "                   C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
    End Sub
    
    then i clean it up here...

    Code:
            Excel_Workbook.SaveAs filename:="C:\aaa\timesheets\Employee Timesheet for - " & sdt & " to " & edt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
            t = Len(Dir("C:\aaa\timesheet backups"))
            If t = 0 Then
            Else
                    Excel_Workbook.SaveAs filename:="C:\timesheet backups\Employee Timesheet for - " & sdt & " to " & edt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
            End If
            Excel_Application.DisplayAlerts = True
            Excel_Application.Visible = True
    
            Kill "C:\aaa\timesheets\Employee Timesheet Master.xls"

    1000 ways to skin a cat, allways looking for another one...
    Use MDB format for sample post. If your issue is fixed, mark the thread solved.
    Click on the star below if this has helped.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-14-2015, 07:21 AM
  2. Replies: 6
    Last Post: 09-10-2014, 01:15 PM
  3. How to modify code to open report instead of form
    By kassem in forum Programming
    Replies: 4
    Last Post: 06-10-2014, 07:02 PM
  4. SQL*Plus code in MsAccess, how to modify?
    By suverman in forum Queries
    Replies: 1
    Last Post: 05-20-2011, 07:01 AM
  5. Word code in Access - How to modify my current code
    By Alexandre Cote in forum Programming
    Replies: 0
    Last Post: 11-15-2010, 08:26 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