Excel filtered rows not selected on import

I was doing an import on a excel file i recently had done before into Access 2010 using the following
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9  etc...

The rows were not coming into my table. I soon discovered that someone had put a filter on the file and the rows I wanted were not in the filter. Is there anyway I can have Access import the spreadsheet and ignore the filters?
Chuck LoweAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
The only way that I can think of is for Access to automate Excel to open the file and remove the filter and then import the rows.  Using the TransferSpreadsheet method, Access would respect the Excel settings.
Jeffrey CoachmanMIS LiasonCommented:
I believe Access will look at whatever Excel presents.

There are techniques in Excel that will allow you to see at a glance if the sheet is filtered.

You can also set code in Excel to remove any filters when the workbook closes.
something like this perhaps:
   
    Sheets("Sheet1").Activate
    ActiveSheet.ShowAllData

Lets see if another Expert has something more precise...

JeffCoachman
Chuck LoweAuthor Commented:
And what are the commands or VBA code to remove the filter?

Additionally I think I would get an error when I close Excel that states the file has been changed and asks if I want to save it.

We have users doing this and they would get confused by that if it happened.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

PatHartmanCommented:
Jeff posted the code to remove the filter.  You would not save the spreadsheet after Access opened it unless you really wanted to.  You would loop through the sheet and get the data manually.  Then close the workbook.  If you wanted to just open the workbook and turn off the filter,  one of the save options saves without prompting.  Then you could use TransferSpreadsheet to import the data.
Chuck LoweAuthor Commented:
I thought people here were experts!
I googled it and found a solution and it works.

.Sheets("Worksheet1").AutoFilterMode = False
Roy CoxGroup Finance ManagerCommented:
Here's code to check if AutoFilter is on and if so switch it off.

   If Sheet1.AutoFilterMode Then
        MsgBox "Filter on"
        Sheet1.AutoFilter.Range.AutoFilter
    End If

Open in new window


members' expertise differs, but you were offered suggestions so there is no need for a rude response!
Chuck LoweAuthor Commented:
Did not mean to be rude. But I myself , if I'm not sure of the answer I will not offer it. I'm under a lot of pressure to get this project done and have paid my own money (after being laid off for almost 6 months) for this subscription so I get a little annoyed when I'm told it can't be done and I find it out on the web.

Sorry if I insulted anyone.
Roy CoxGroup Finance ManagerCommented:
No problem. I do agree with you that answers should be tested before submitting. I believe that the other answers were guides from Access users.

Did you try my code? It does a check if AutoFilter is on before attempting to remove it.
Chuck LoweAuthor Commented:
Actually no. I'm not gonna bother checking. i'm just gonna turn it off and make sure I set
.DisplayAlerts = False.

Thanks for the feedback.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
This was the easiest solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.