Help to Modify this vba code.

mardi 21 avril 2015

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

Help to Modify this vba code.

0 commentaires:

Enregistrer un commentaire

Labels