Posts by carlmack

    Re: Huge SQL Statement, VBA, Timeouts

    Agree with Craig - use stored procedure if poss.

    I see there is an Excel ODBCTimeout property that you could try eg.

    Application.ODBCTimeout = 100

    To convert SQL can use a bit of gen.

    Paste SQL in Sheet1 A1, run code, result in Sheet2.

    Re: OLAP Pivot Drillthrough Completion Trigger

    Try something like

    Dim NewWs As Worksheet
        ' Replace Sheet3.Range("E10") with range of measure in Pivot Table
        Set NewWs = ActiveSheet
        NewWs.Range("A3").Value = "Write Something"

    Re: Datatype mismatch when trying to execute Recordset.Open procedure

    If the customer Id is a string in the database you will need to add single quotes around it. Its a bit difficult to see but I added them here

    strRequest = "SELECT [ContactName], [Phone], [eMail], [Company] FROM ContactList WHERE [CustNum] = '" & CustID & "';"

    If the customer Id is a integer in the database you need to dimension it as Long at the top of your code.

    Re: OLEDB MSSQL connections. Passing an Excel cell into a parameterised query ??

    I believe Excel always stores the connection properties in the workbook (since 97) and does not use the DSN file. I guess the DSN is just used when you want to create another connection in a new workbook. This is complicated by the new odc files which you can instruct Excel to look at all the time.

    If you want to use parameters then I think you still do need to go through MS Query in Excel 2007/2010. Try it on a users computer.

    You may experience a problem if you have used the SQL Native Client as this may not be on the user machines so use the std SQL Server provider.

    If you are ok with VBA you would have more control using ADO.


    Re: Seeking a method to compare a range against an Access database

    Hi Bill,

    I can not think of an easy solution but :-

    1. You could automate a MS Query with a parameter. If you create a query then setup the item code as a parameter and link it to a cell (you should be able to find examples by searching here) you could loop (using VBA) through the range and copy the item codes one by one to the parameter cell then copy the results back to the range. You need to make sure the code stops and waits for the query to finish (use Application.CalculateUntilAsyncQueriesDone for 2007/2010).

    2. Probably the best performance solution would be to create a single field (item code) table in Access and join it to the main item table in an Access query. You would then pump in the items into the new table using VBA and ADO and you could then use MS Query to query the Access query. Not easy.

    3. Not very elegant but you could just query all the whole table to Excel temporarily and lookup the results. Using VBA you could then range value the results and drop the table.

    4. A neat solution would be to create UDFs for each parameter using VBA/SQL/ADO. Technically difficult.

    5. Use SQL to cross query a named range in Excel with the Access table. I think technically very difficult and prone to memory leaks.

    Unfortunately tomorrow I am on holiday for a couple of weeks so I can not help you work through any of these but if nobody else chimes in and you are still stuck drop me a pm

    Re: MS Query Excel ->VBA -> MSQ -> Excel

    You can use parameters in MS Query by entering square brackets (eg [myparam] ) in the criteria in the MS Query query builder (not the wizard). These can then be mapped to cells in the receiving workbook so that a change in a cell will set off the query.

    The alternative is to use VBA + ADO which gives you full control but it's much more complicated.

    You should be able to find examples on this site of both but if not pop back and we can talk you through

    Re: GetPivotData VBA in Excel 2003

    Try something like

    Private Sub GPT()
    Dim Rowcounter As Long
    Dim ColumnCounter As Long
    ColumnCounter = 3
    For Rowcounter = 5 To 13
        Sheet1.Range("A" & Rowcounter).Value = "=GETPIVOTDATA(""Sum of Field1"",'[PivotWorkbook.xls]Sheet1'!R6C1,""Code"",'[Copy of SPREADSHEET TEMPLATE V1.xls]Data'!R" & Rowcounter & "C" & ColumnCounter & ")"
    Next Rowcounter
    End Sub

    Re: Display multiple images based on data in multiple cells

    Hi Leon,

    By using

    If Target <> Range("B4") Then Exit Sub

    it will exit if they select B4 --- it will not reach the code for defenders.

    Try something like

    Re: Automate Excel report that is based off SSAS cube

    If you they have Excel 2007/2010 and the SSAS cube is 2005 or 2008 then you could use inbuilt cube formulas to talk to the cube. In this way you would not need to send out the reports as they could update automatically from the users desktop.

    An alternative would be to use VBA and write use defined functions that call MDX statements against the cube.

    A last resort is to use a pivot table and getpivotdata functions.

    To email out you could write something in VBA and set it off with with windows scheduler

    I think using SSIS would be hardwork to update cells in a complex spreadsheet from a cube.

    I think 4.5 million records and 43 fields is a bit beyond Access but maybe you should try to create a Access linked table to the SQL database and only create real tables with summary data in the actual Access db. That way you will keep the bulk of the data out of Access and use the SQL engine to do the summarisation.

    When manipulating large amounts of data I'd use SQL where at all possible rather than a programming language.

    What are you going to do with the data when you get it ?

    Re: Export To Access

    Looks like you are populating variables strTank, strDate , strR1, strR2 ... from a worksheet but you are only using the strDate when writing into the table.

    Can you attach a sample workbook and confirm the fields of the table you want to write too in Access.

    Re: Run SQL Query with ODBC to Oracle DB

    Hi Andrew,

    You could try something like


    Re: Multiple Criteria Lookup With Dates Across Workbooks

    Copying from other posts

    If you set up a named range in the "database" workbook and save the workbook then you can pull the data to a new workbook using a query.

    From the menu in the new workbook Data > Import External Data > New Database Query > and choose the Excel driver. If you follow the wizard and point at the "database" workbook you should be able to choose the fields you want and set up a query.

    This creates a range in the new workbook which is linked to the data in the "database" workbook.

    At the moment all the data comes in but you can add parameters to narrow it down. You can get back to the wizard by right click > Edit Query from anywhere in the new query range.

    At the end of the query wizard choose "View Data and edit in Microsoft Query". Select View and make sure Criteria is ticked. In the "criteria field" choose a field then in the "Value field" area type [xxxx] (actually any name as long as it has the square brackets [] ). Now it will probably prompt you for a value for the parmeter "xxxx" so put anything in and select File > Return Data to Microsoft Excel.

    In the query you can now right click and select parameters then specify which cells in you spreadsheet they are linked to. You can also select refresh automatically when the cell changes. In this way as you change cells in your workbook it will automatically drag data from the "database" workbook that meet the criteria.


    Re: Multiple Criteria Lookup With Dates Across Workbooks


    Can you think of another way to do this then

    Yes. A few. It would be easier to put the data in the same workbook if you are going to use simple formulas. See attached example using Indirect or Index (as suggested above).

    You may come across the problem that you have more than one entry that satisfies your criteria. In this case the sumproduct approach will not work as it will add up the two or more rows in which the criteria are matching and give you the wrong result. See example when you choose S460. If you want to return two or more results the formula approach will become too complex and you need to switch to another method probably using queries or some VBA.

    You could use a parameter query to look up results in this workbook or another. It will take some learning but I have posted some examples in the past here. Alternatively you could use VBA to automate the filter functionality but again it will take some time to get up to speed with the VBA involved.

    Good luck