Another way =IF(SUMPRODUCT(–(NOT(ISERR(SEARCH({“Gingrich”,”Obama”,”Romney”},C1)))))>0,”1″,””) Also, if you keep a list of values in, say A1 to A3, then you can use =IF(SUMPRODUCT(–(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))>0,”1″,””) The wildcards are not necessary at all in the Search() function, since Search() returns the position of the found string.
Excel’s interface for SQL Server queries will not let you have a custom parameters. A way around this is to create a generic Microsoft Query, then add parameters, then paste your parametorized query in the connection’s properties. Here are the detailed steps for Excel 2010: Open Excel Goto Data tab From the From Other Sources button choose … Read more
Use SpecialCells to delete only the rows that are visible after autofiltering: ActiveSheet.Range(“$A$1:$I$” & lines).SpecialCells _ (xlCellTypeVisible).EntireRow.Delete If you have a header row in your range that you don’t want to delete, add an offset to the range to exclude it: ActiveSheet.Range(“$A$1:$I$” & lines).Offset(1, 0).SpecialCells _ (xlCellTypeVisible).EntireRow.Delete
I kept getting all kinds of weird errors when working with .xlsx files. Here’s a simple example of using Apache POI to traverse an .xlsx file. See also Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF). import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; … Read more
I think your problem is that it’s closing the document that calls the macro before sending the command to quit the application. Your solution in that case is to not send a command to close the workbook. Instead, you could set the “Saved” state of the workbook to true, which would circumvent any messages about … Read more
I know you have accepted a solution now that will work for you and is much simpler than the below, but if you’re interested, this would be the more direct answer to your question. You need to create a class to handle the button clicks, so every time the button is clicked it uses the … Read more
I think (from experimenting – MSDN is unhelpful here) that there is no direct way of doing this. Setting Criteria1 to an Array is equivalent to using the tick boxes in the dropdown – as you say it will only filter a list based on items that match one of those in the array. Interestingly, … Read more
EDIT: Converted this to a BLOG post for VBA and VB.Net. I have never been in favor of Sendkeys. They are reliable in some case but not always. I have a soft corner for API’s though. What you want can be achieved, however you have to ensure that workbook for which you want to un-protect … Read more
AFAIK you cannot use VBA Excel to create custom tab in the Excel ribbon. You can however hide/make visible a ribbon component using VBA. Additionally, the link that you mentioned above is for MS Project and not MS Excel. I create tabs for my Excel Applications/Add-Ins using this free utility called Custom UI Editor. Edit: … Read more
“External table is not in the expected format.” typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0 Using the following connection string seems to fix most problems. public static string path = @”C:\src\RedirectApplication\RedirectApplication\301s.xlsx”; public static string connStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + … Read more