Open Excel with Macros using Access

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

Hi -

Simple question below but long story.

Through some of your help, I managed to put together a
code in Access that calls the Open File dialog box for
the user to select the Excel file that is to be imported
into the Access database.

However, I think that because my excel file has macros, I
get the following error msg:
" Run Time error '3011'; The Microsoft Jet Database
engine could not find the object 'tbl_A'. Make sure the
object exists and that you spell its name and path name
correctly."

My question is, how do I turn off the 'Enable Macros'
prompt that pops up each time the user opens the excel
file?
When I open the excel file and run the code, I do not see
this error.

Please Advise!
Thanks much.
 
The error message is not caused by your EXCEL file having macros. It's
caused by an error in your code that is running in ACCESS. You'll need to
post the code for us to see and review it.

Only way to turn off the EXCEL macro warning is to manually set EXCEL's
security setting to Low on your PC. That change applies to all EXCEL files
from that point on (whether opened by program or by user), so you may or may
not want to do that. Or you'll need to do it just before you want the ACCESS
program to be run, and then change it back after the ACCESS program runs.
 
Hi Ken,

Thanks! However, I do not see the error when the excel
file is already open. This is why I am thinking it has to
do with the 'pause' in the file when the user is prompt
to enable/disable macros.

Below is the entire code. Not much :)

Private Sub cmd_Import_Data_Click()

On Error GoTo Err_cmd_Import_Data_Click

Dim z As Integer

Dim XLFile As String
Dim XLSheet As String

Dim SheetCount As Integer
Dim SheetName(100) As String

Dim MessageText As String


'Find Standardized 1804 file
Dim strFilter As String
Dim strInputFileName As String

MsgBox "Please specify the location of the File",
vbOKOnly, "Locate File"

strFilter = ahtAddFilterItem(strFilter, "Excel Files
(*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave
(Filter:=strFilter, OpenFile:=True, DialogTitle:="Please
select file...", Flags:=ahtOFN_HIDEREADONLY)


Dim XLApp As Object

'Set Object only works when there is an instance of excel
running
Dim blXLisAllMine As Boolean
On Error Resume Next
blXLisAllMine = False
Set XLApp = GetObject(, "Excel.Application")
If XLApp Is Nothing Then
Set XLApp = CreateObject("Excel.Application")
blXLisAllMine = True
End If
If XLApp Is Nothing Then
MsgBox "Sorry, couldn't get hold of Excel."
Exit Sub
End If
On Error GoTo 0

Set XLApp = GetObject(, "Excel.Application")

XLApp.Visible = True
XLFile = strInputFileName

SheetCount = XLApp.ActiveWorkbook.Sheets.Count

For z = 1 To SheetCount

SheetName(z) = XLApp.ActiveWorkbook.Sheets(z).Name
MessageText = MessageText & z & ".) " & SheetName(z) & " "
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel8, XLApp.ActiveWorkbook.Sheets
(z).Name, XLFile, True, SheetName(z)

Next z

MsgBox "Data Imported Successfully"

XLApp.Quit
Set XLApp = Nothing
Set XLwb = Nothing

Exit_cmd_Import_Data_Click:
Exit Sub

Err_cmd_Import_Data_Click:
MsgBox Err.Description
Resume Exit_cmd_Import_Data_Click


End Sub
 
Back
Top