Excel: How to Select Records and Print Them Out?

[pvsmith5]

I have a client who wants to create a spreadsheet that will have a column that alternates between a "0" and "1" flag. He wants to send all the "1" flag records to a separate spreadsheet and print out the area. I could easily do this in Access with a query and make a new table, but how do you query for specific rows of records in Excel and send them to a new worksheet? And also, how do you format the print area to expand and contract with the length of the records selected? Also, I'm trying to do this in Excel 2007 and it is a little awkward for me.

 

[yogia]

Hi pvsmith5:

If I understand you correctly, you can use AdvancedFilter to extract records per specs in another worksheet for printing. Please try it and post back if you want to discuss this further.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[pvsmith5]

Yes, Yogi, you're right, but he wants to do it with a macro so it is automated. I don't know if filters can be automated.

 

[yogia]

Yes, Yogi, you're right, but he wants to do it with a macro so it is automated. I don't know if filters can be automated.

Hi pvsmith5:

Yes the use of AdvancedFilter can be automated using VBA.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Hi,

Alternating 0 & 1 -
In a new column...
[tt]
=MOD(ROW(),2)
[/tt]
how do you query for specific rows of records in Excel ...

On a new sheet, Data/Get Extenal Data...
Using MS Query to get data from Excel faq68-5829
Query criteria on the new column 1/0 values.

Skip,
[sub]
[red][/red]
[/sub]

 

[pvsmith5]

Thanks for the posting, but unfortunately, Microsoft quit including MS Query in any Excel version past 2002. So I can't use this in 2007 or 2003. I'll try to do something with automating the Autofilter, but it doesn't look too clean.

 

[yogia]

.... I'll try to do something with automating the Autofilter, but it doesn't look too clean.

Hi pvsmith5:

If you want select records (those Flagged 1) extracted to a separate worksheet, the ideal way is to use AdvancedFilter with criterion ...

Flag = 1

You can use the criterion range with the column Label and 1 to make up the criterion or you can use computed criterion in a cell =Flag=1, and all the records Flagged 1 would be extracted.

And for automating you can perhaps use Worksheet_Change event to trigger execution of the AdvancedFilter automatically or you can have it triggered by clicking on a command button. I think this would be a very clean operation.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

"Microsoft quit including MS Query in any Excel version past 2002. So I can't use this in 2007 or 2003"

Hmmmmmmmmm. That's odd. I've been using MS Query nearly every day in my Excel 2003 to return data from Oracle, DB2, MS Access, text files and Excel Workbooks.

Are you sure that you just failed to include that capability when you loaded MS Office?

I'd be lost without MS Query!!!

Skip,
[sub]
[red][/red]
[/sub]

 

[pvsmith5]

Here's what Microsoft says about MS Query. I'll try to download it and put it in my 2007 version because I thought it was a great add-in too.

http://support.microsoft.com/kb/288118/en-us

 

[SkipVought]

Gotta read the "fine print"...
[tt][highlight white]
The following add-ins are no longer included with Excel 2002 and with later versions of Excel. However, they are available for download from the Excel section of the Microsoft Tools on the Web (

http://office.microsoft.com/)

Web site. Alternatively, you can visit one of the following Microsoft Web sites:• Access Links (

http://www.microsoft.com/downloads/...9-5D8A-4F53-ABCB-3D3D22774AAF&displaylang=en)

• Report Manager (

http://www.microsoft.com/downloads/...2-D511-4760-8094-2754D82A4E2F&displaylang=en)

• Template Wizard with Data Tracking (

http://www.microsoft.com/downloads/...8-094D-4ED1-88DA-F4B8B2AD28BD&displaylang=en)

• ODBC Add-in (xlodbc.xla) (

http://www.microsoft.com/downloads/...7-13a0-4895-9942-5b177846ab8a&displaylang=en)

These following add-ins are no longer included with Excel 2002 and with later versions of Excel. Additionally, they are not available for download: • Autosave Add-in (Autosave.xla) [highlight yellow]
• MS Query [red]1.0/2.0 Compatibility (xlquery.xla) [/red][/highlight yellow]
• Template Utilities (tmpltnum.xla)
• Update Add-in Links (updtlink.xla)
[/highlight][/tt]

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

BTW, MS Query is NOT an Add-In. XLODBC.XLA was.

Data/Get External Data...

Skip,
[sub]
[red][/red]
[/sub]