Annoying 2007 Excel link to Access Trait

[Sade]

Hello. We recently switched to Office 2007, and a lot of the "new improvements" have actually slowed me down and made things worse rather than better.

That said, my question: From Excel, I link to an Access database query. It imports fine, except for the formatting. The fields brough into Excel are created with a filter dropdown arrow attached to them. Can these be removed from the default process?

Also, the data comes in with an alternating background (Blue ... Light Blue ... Blue ...) from line to line. Again, can this be removed from the linking process?

I've messed with the "Properties" and "Connections", but can't find anything.

Thanx

 

[Smitty]

I don't know that the default behavior can be removed, but we're trying to figure it out. If you go the other way and Export the data from Access to Excel you won't have that problem, same if you use VBA to query the Acess info directly from Excel.

Once imported you can remove the table characteristics by chosing the convert to range option in the Table Tools tab though.

HTH,

 

[MargAtCanon]

Smitty,
I have been having a problem the same as the above and need to insert some VBA script into my macro to convert an imported data table to a range. Can you help?

I used this yesterday (by recording a macro) and it worked:

    ActiveSheet.ListObjects("Table_DSR_SBR_Maintenance.accdb654233").Unlist

I am assuming that today the reference number (accdb654233) has changed. I am importing data from a 2003 xls file into a 2007 xlsm file.
Margaret