Posts by carlmack

carlmack · Aug 26th 2012

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.

Code

Application.ODBCTimeout = 100

To convert SQL can use a bit of gen.

Code

Public Sub ConvertSQLStrToVBA()
Dim LastRow As Long
Dim RowCounter As Long
Dim StartStr As String
Dim EndStr As String
Dim SubStartStrRows As Long
Dim RowStr As String
Dim AutoArrayCounter As Long
Dim counter As Long
Dim AutoArray(1000) As String


For counter = LBound(AutoArray) To UBound(AutoArray)
        AutoArray(counter) = ""
Next counter


StartStr = Chr(34) & "  "
EndStr = "  " & Chr(34) & " & _"


Sheet2.Range("A1:Z65000").ClearContents
LastRow = Sheet1.Range("A65000").End(xlUp).Row




AutoArray(1) = "Dim SQLStr as String"
AutoArray(2) = "SQLStr = " & Chr(34) & " " & EndStr
SubStartStrRows = 2


For RowCounter = 1 To LastRow
    RowStr = Sheet1.Range("A" & RowCounter).Value
    If RowCounter <> LastRow Then
        AutoArray(RowCounter + SubStartStrRows) = StartStr & RowStr & EndStr
    Else
        AutoArray(RowCounter + SubStartStrRows) = StartStr & RowStr & " " & Chr(34)
    End If
Next RowCounter




RowCounter = 0
For AutoArrayCounter = LBound(AutoArray) To UBound(AutoArray)
        RowCounter = RowCounter + 1
        Sheet2.Range("A" & RowCounter).Value = AutoArray(AutoArrayCounter)
Next AutoArrayCounter
End Sub

Display More

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

carlmack · Mar 28th 2012

Re: Excel vba adodb pivot - refresh pivot table

Code

Dim kdid As Long
kdid = 100004
.CommandText = "select * from rechpos where kdid = " & kdid

This should work for an integer (in the where clause).

carlmack · Oct 31st 2011

Re: Run macro to hide blank rows after recalculation from an OLAP cube using pivot fi

You probably need the VBA to wait until the cube formulas have finished refreshing. Try

Code

Application.CalculateUntilAsyncQueriesDone

HTH
Carl

carlmack · Jan 27th 2011

Re: Advanced sorting filtering in excel 2007

Can you add the three to the row area then count the accounts like attached ?

carlmack · Jan 27th 2011

Re: Not adding Duplicates to combo box list

Moved to Excel forum - (incorrectly posted in Access)

carlmack · Oct 3rd 2010

Re: OLAP Pivot Drillthrough Completion Trigger

Try something like

Code

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

carlmack · Sep 30th 2010

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

Code

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.

carlmack · Aug 30th 2010

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.

HTH
Carl

carlmack · Jul 27th 2010

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

carlmack · Jul 25th 2010

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

carlmack · Jul 25th 2010

Re: GetPivotData VBA in Excel 2003

Try something like

Code

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

carlmack · Jul 25th 2010

Re: How to access local folder data over network

You will need to share the folder you want them to have access to (right click sharing). It can then be accessed using your IP address or computer name. eg.

Code

InitialFoldr$ = "\\192.168.1.1\MyShareName"

carlmack · Jul 25th 2010

Re: Display multiple images based on data in multiple cells

Hi Leon,

By using

Code

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

Code

Private Sub Worksheet_Change(ByVal Target As Range)




If Target = Range("B4") Then
    Select Case Target.Value


'************
'* Goalkeepers  *
'************
            Case "van der Sar, E": Image1.Picture = LoadPicture("C:\Users\Leon\Documents\Telegraph FFL\Goalkeepers\van der Sar.jpg")
            Case "Cech, P": Image1.Picture = LoadPicture("C:\Users\Leon\Documents\Telegraph FFL\Goalkeepers\Cech.jpg")
            Case "Reina, J": Image1.Picture = LoadPicture("C:\Users\Leon\Documents\Telegraph FFL\Goalkeepers\Reina.jpg")
            Case "Gomes, H": Image1.Picture = LoadPicture("C:\Users\Leon\Documents\Telegraph FFL\Goalkeepers\Gomez.jpg")


    End Select
End If
If Target = Range("B5") Then
        Select Case Target.Value


'************
'*   Defenders   *
'************
            Case "Terry, J": Image2.Picture = LoadPicture("C:\Users\Leon\Documents\Telegraph FFL\Defenders\Terry.jpg")
            Case "Evra, P": Image2.Picture = LoadPicture("C:\Users\Leon\Documents\Telegraph FFL\Defenders\Evra.jpg")
            Case "Cole, A": Image2.Picture = LoadPicture("C:\Users\Leon\Documents\Telegraph FFL\Defenders\Cole.jpg")


        End Select
End If
End Sub

Display More

carlmack · Jul 24th 2010

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.

carlmack · May 30th 2010

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 ?

carlmack · May 30th 2010

Code

Sub ADOExcelAccess()
'
' Carl Mackinder : Access to Excel using ADO connection
' Dump SQL results from recordset to spreadsheet
'
' ***** FOR THIS CODE TO WORK  *****
' In VBE (Visual Basic Editor) you need to go
'  > Tools > References and check Microsoft Active X Data Objects 2.x library
'
    Dim Cn As ADODB.Connection
    Dim DataSource As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Dim FieldCounter As Long
    
    Set Cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
     
    ' *** Enter your Access database path and name here ***
    DataSource = "C:\Datastores\Movie2000.mdb" '<--- CHANGE HERE
    
    ' With 2007 database format use
    ' DataSource = "C:\Datastores\Movie2000.accdb"
    
    ' *** Enter your SQL statement here EXAMPLE ***
    SQLStr = "SELECT TitleID, Title, Director" & _
    " FROM AllMovies" & _
    " WHERE Title = '" & Worksheets("sheet1").Range("a1").Value & "'" & _
    " ORDER BY TitleID" '<--- CHANGE HERE and 3 lines above
     
     ' Open connection using DataSource
    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & DataSource & ";"
    
    ' With 2007 database format use
    'Cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & _
        "Data Source=" & DataSource & ";"
     
     ' Open recordset and pass in results from SQLStr statement
    rs.Open SQLStr, Cn, adOpenStatic
    
    
    ' write field names to range A1, B1 ....
    For FieldCounter = 0 To rs.Fields.Count - 1
        Worksheets("sheet1").Cells(1, FieldCounter + 2).Value = rs.Fields(FieldCounter).Name
    Next FieldCounter
    
     ' Dump contents of recordset to spreadsheet
     ' *** Enter the sheet name and range in which you want the data to appear
    With Worksheets("sheet1").Range("b2:z100") ' <--- CHANGE HERE
        .ClearContents ' **** Note clears the FULL range before new data is loaded
        .CopyFromRecordset rs
    End With
     
     ' Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    
End Sub

Display More

carlmack · Feb 6th 2010

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.

carlmack · Nov 25th 2009

Re: Run SQL Query with ODBC to Oracle DB

Hi Andrew,

You could try something like

Code

Sub RefreshQT() 
    Dim qt As QueryTable 
    Dim ws As  Worksheet 
    For Each ws In  ThisWorkbook.Worksheets 
       
        For Each qt In ws.QueryTables 
            qt.BackgroundQuery = False 
            qt.Refresh 
        Next qt 
     
    Next ws 
End Sub

Display More

Cheers
Carl

carlmack · Sep 12th 2008

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.

Cheers
Carl

carlmack · Sep 10th 2008

Re: Multiple Criteria Lookup With Dates Across Workbooks

Quote

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

Carl

Posts by carlmack

Huge SQL Statement, VBA, Timeouts

Excel vba adodb pivot - refresh pivot table

Run macro to hide blank rows after recalculation from an OLAP cube using pivot fields

Advanced sorting filtering in excel 2007

Not adding Duplicates to combo box list

OLAP Pivot Drillthrough Completion Trigger

Datatype mismatch when trying to execute Recordset.Open procedure

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

Seeking a method to compare a range against an Access database

MS Query Excel ->VBA -> MSQ -> Excel

GetPivotData VBA in Excel 2003

How to access local folder data over network

Display multiple images based on data in multiple cells

Automate Excel report that is based off SSAS cube

suggestion to work with a million of recrds

Query from Access to XL

Export To Access

Run SQL Query with ODBC to Oracle DB

Multiple Criteria Lookup With Dates Across Workbooks

Multiple Criteria Lookup With Dates Across Workbooks