Importing Access Data: I get too much. How to filter

[JPWRana]

I recently figured out how to import to excel from our Access Database (I don't know why I didn't figure it out sooner). This is on Sheet 1. However, now that I do have the data that I want, it's actually too much. I only want a subset of that amount of data.

When I import data from Access, I found that the data set contains 43 columns and over 43k rows of data. I only need the 80 rows of data from Column B (Log Number. Each log has around 80 rows of data). Even then, there are over 500 different log Numbers. I only need it for one of them.

How can I (either by the wizard menu / help / or macro) import just the Log Number for one specific log that I have already typed out on Sheet2!A1. (Even then, I didn't type it out, it is the result of a index match formula)

Would it be through the icons on the ribbon? Or through a macro?

[JPWRana]

Any help is appreciated.

[satabp]

Hi,

I suggest have the data filtered in Access Database itself before you import. Because it is bit easier to have a Query designed in Access.

And just import the data from that query.

If you want to do it in excel only, then macro can be do this activity.

It will be great help if you provide a sample workbook with sample data.

[JPWRana]

Sure thing... here is the file.

If simple command buttons can be used from the ribbon panel on top, then I can go ahead and record it as a macro. The Log name will be different every time though.

Note: I had to cut it down to 4k rows of data because the files size was too large for me to upload, but that should not mean that what I hope to be achieved can't be done. I still want a filer for only one set of rows that have Log Number (Column B) =Sheet2!A2.

I can't get it to work under advance filter.

See attachment.

[JPWRana]

I don't want to touch any commands or macros in Access because this is supposed to be part of a bigger excel streamline project where other users will use this file.

Those other users are older individuals where they haven't been used to computers as much, so making it as user friendly as possible will be a HUGE PLUS.

Besides, it will save time if I don't need to interface with Access.

[satabp]

Hi,

Please find the attached updated file with macro and command button.

Thanks

[JPWRana]

satabp, this is ALMOST what I want.

I have a few questions though for the macro:

Please Login or Register  to view this content.

Where in these lines of code did you put in what the value is for =Sheet2!A2 (which is where this will change every month for me)?

I'm trying to convert it over to my existing template but I can't seem to find it.

Btw, I was able to find a way to create a 1kb Microsoft Query file so that it can save me time when I'm doing this at home, since the remote connection is slower. In fact... is it supposed to save me time since it still needs to do a partial filter for only the columns I want/need?

If it wont save me time to import from a query instead of an Access Database, then I'll stick to what you gave me.

[satabp]

Where in these lines of code did you put in what the value is for =Sheet2!A2 (which is where this will change every month for me)?

Yeah, in fact this macro will look at all the data from Sheet2 Column A. So, you can also give more than one value.

Sure, please let me know, if you have any further issues..

Thanks