Bypassing the Security Warning when opening Excel file

[Dale Fye]

I've got an Access application that has to read some data (and perform some
manipulation of it) from several Excel files.

Unfortunately, when I either import or link the Excel spreadsheets, Access
is misinterpreting a field type and I am losing data. Because of this, I am
using automation to open Excel and read the records one row at a time, doing
the appropriate data type conversions. Unfortunately, I keep getting the
annoying macro security warning whenever I open the Excel files. Is there a
way to bypass this warning and disable the Excel macros programmatically?

 

[Minton M]

I've got an Access application that has to read some data (and perform some
manipulation of it) from several Excel files.

Unfortunately, when I either import or link the Excel spreadsheets, Access
is misinterpreting a field type and I am losing data. Because of this, I am
using automation to open Excel and read the records one row at a time, doing
the appropriate data type conversions. Unfortunately, I keep getting the
annoying macro security warning whenever I open the Excel files. Is there a
way to bypass this warning and disable the Excel macros programmatically?

You didn't mention which version of Excel you're using - the security
has changed in each version. I would recommend solving the first
problem - the data type issue. Access often does this if one fo the
fields in a column changes type unexpectedly. This most often happens
with dates - frequently, they'll be a text format field where someone
has frigged up the date to look like a date format and that will cause
the import to blow up.

-- James

 

[mr tom]

Give this a try.

Open Excel.

Hit tools, options, security

Set macro secutiry to low.

Exit excel.

Hopefully this change will be picked up when you import into access.

 

[Dale Fye]

Using Office 2003.

Yeah, I was hoping there was a way to do this programmatically. Something
like:

Set xlObj = CreateObject("Excel.Application")
xlObj.MacroSecurity = 1
....
xlObj.MacroSecurity = 3
xlObj.close

Dale

 

[Dale Fye]

James, I'm running Office 2003.

I've had this problem with dates in the past.

In this instance the Excel field is Task, and is formatted as text, although
the numbers look like:

1.1
1.1.1
1.1.2
1.2
1.2.1
1.1.2

Interestingly, the numbers I am having difficulty with are 1.1 and 1.2; the
others are formatted properly.

Dale

 

[Edd]

Hi Dale:

Were you ever able to sucessfully code the bypassing of this annoying macro
security message?

Thanks,

Ed.

 

[Dale Fye]

No.

I got around it by digitally signing code and having my users add my to
their accepted digital signatures list.

It makes sense, if you could bypass it with code, there would be no macro
security!