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?

On Jan 7, 7:55 am, "Dale Fye" <dale....@nospam.com> wrote:
> 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

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" wrote:

> 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?
>
>
>

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

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
news:8A746795-3A75-4374-87A2-(E-Mail Removed)...
> 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" wrote:
>
>> 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?
>>
>>
>>

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

"Minton M" <(E-Mail Removed)> wrote in message
news:5f72ccff-6e65-4d08-82cc-(E-Mail Removed)...
> On Jan 7, 7:55 am, "Dale Fye" <dale....@nospam.com> wrote:
>> 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

Hi Dale:

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

Thanks,

Ed.


"Dale Fye" wrote:

> 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
>
> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> news:8A746795-3A75-4374-87A2-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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?
> >>
> >>
> >>
>
>
>

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!

"Edd" <(E-Mail Removed)> wrote in message
news:875084F3-08B0-43CD-93CC-(E-Mail Removed)...
> Hi Dale:
>
> Were you ever able to sucessfully code the bypassing of this annoying
> macro
> security message?
>
> Thanks,
>
> Ed.
>
>
> "Dale Fye" wrote:
>
>> 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
>>
>> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
>> news:8A746795-3A75-4374-87A2-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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?
>> >>
>> >>
>> >>
>>
>>
>>