Posts by Craig Ottley

Re: Submitting a quiz score to email

Have you tired looking into CDO? Try this linkhttp://www.rondebruin.nl/win/s1/cdo.htm

Re: VBA/SQL: The fastest way to send data from Excel sheet into Access database

If you've got access to SSIS that will be the fastest way of getting data into access...

Re: Fix "Run-Time error 13: Type MisMatch" in sql query with complex "Where" statemen

Have you tried doing a debug.print str value and executing that directly on the server? It's normally a syntax error that throws error 13

Re: Automatically Run Macro After Pivot Table Refresh

Are you actually trying to run this piece of code with the color tag?

If so try changing it to

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
     Application.Run "'1pivotbasedon2.xlsm'!test2"
 Sub

Or you can call the procedure

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
     Call ModuleName.test2
 Sub

Re: Huge SQL Statement, VBA, Timeouts

You can change the Timeout for the query but it requires you to change the Registry... Its something like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\QueryTimeout depending on how you're connecting to the data source.

If you've got such a big sql statement you should consider creating a stored procedure in the database. Big statements are never good calling from excel, you're better off breaking it down into chunks in SQL Server

Re: Match SQL ODBC Results on distinct ID; insert/update/archive on status

If you're using SQL 2008R2 why not load the data into a staging table and then perform a merge statement in t-sql?

Re: Match SQL ODBC Results on distinct ID; insert/update/archive on status

Post your code and we'll see if we can help?

This guy just doesn't listen to you when you ask to be removed from their mailing list!!

[email protected]

Good Morning,

Thank you for your recent enquiry on Money Supermarket, we hope the service we provided was as you hoped and your insurance needs were met.

We are now offering all Nunn McCreesh clients and referrals a free no obligation Pension review focusing on old and frozen pensions.

A combination of poor advice and a lack of understanding in this complex area means that many people are paying way over the odds in charges and suffer from poor fund performance.

Also, There may be money owed to you that you don't even know about relating to the governments S2P. Did you opt out of SERPS?

Please reply with a contact number and the best time for me to call and we can have a quick 5 minute chat.

Best Regards

--
Phillip A Nunn | Senior Partner | Nunn McCreesh

Website - http://www.nunnmccreesh.com

T: 0161 234 6506 | M: 07538 280547 | F: 0161 210 2983

Nunn McCreesh is an Appointed Representative of Sage Financial Services Limited which is authorised and regulated by the Financial Services Authority

Re: Hide Cell in selection if not in listbox

Post a sample workbook...

Re: Remove duplicate rows based on oldest date

Can you not just update the existing record?

Re: Hide Cell in selection if not in listbox

Hi,

You could use the Scripting Dictionary to check if the value exists before you hide the row.

Try the following example.

Option Explicit
Private Sub UserForm_Initialize()


Dim wks As Worksheet
Dim lngLastRow As Long, lngRow As Long, lngListItems As Long
Dim scpDic As Object


    Set scpDic = CreateObject("Scripting.Dictionary")
    
    Set wks = Sheet1
    lngLastRow = wks.Cells(Rows.Count, 2).End(xlUp).Row
    
    ' Add the List Items to the Scripting Dictionary
    For lngListItems = 0 To Me.ListBox1.ListCount - 1
        If Not (scpDic.Exists(Me.ListBox1.List(lngListItems))) Then
            scpDic.Add Me.ListBox1.List(lngListItems), Null
        End If
    Next lngListItems
    
    For lngRow = 1 To lngLastRow Step 1
        If Not scpDic.Exists(wks.Cells(lngRow, 2).Value) Then
            wks.Cells(lngRow, 1).EntireRow.Hidden = True
        End If
    Next lngRow
   
End Sub

HTH

C

Re: Autorecover Location

You can normally set this in VBA.... Try something along the lines of the following, but make sure it's a valid path as excel will test the path.

Option Explicit


Sub SetPath()
    
    With Application
        .AutoRecover.Path = "C:\Users\Craig\Downloads"
    End With


End Sub

HTH

C

Re: insert sql result into variable in vba Excel

You can use an InputBox for the password to store this as a variable to be used in the connection string. Also you're better off using the Oracle Provider instead of the Microsoft one.

HTH

C

Re: Macro for HTML to Excel file

Hi Sae,

Have you tried using a web query and altering the macro slightly to do what you want?

Thanks

C

Re: Making a variable available amongst userform routines

No Worries.

Happy coding!

Re: Remote Desktop - vba pauses when "locked" or connection closed

Hmmmm, if you're stuck with the task I'd start looking at different options.

Try seeing if you can RDP, start running the add-in and just disconnecting the connection (IE Not logging off) reconnecting to see what happens.
You might have some kind of Group Policy that interferes with the way RDP is working, is the user a local admin? Start checking things like this...

There are come other tricks you can do by creating registry entries where you add a local user to the machine but this exposes the password for that user. You can then have some kind of VNC client watch the machine?

Without being there I'm not that useful to you sorry.

Do let me know how you get on though.

C

Re: Convert Numbers to Words/Text - Explanation Needed

Hi DohNuts,

Welcome to OzGrid! The length variable is optionsal. See Object Explorer

Quote

Optional; Variant (Long). Number of characters to replace. If omitted, all of string is used.

As for the double zero, that's probably something to do with data types on conversion to preserve formatting.

HTH

C

Re: Macro to copy and paste data between date&time code

Hi Matt,

Hopefully the attached should help point you in the right direction. This is an interpretation but should be a good starting point!

[ATTACH=CONFIG]41432[/ATTACH]

C

Re: Remote Desktop - vba pauses when "locked" or connection closed

Hi Simon,

Welcome to Ozgrid. For tasks like this I've created a c#/vb.net applications to run the tasks. This way you can create a Scheduled Task which in theory should run everything you require.

Hope this helps?

C

Re: Making a variable available amongst userform routines

Hi Xhunmas,

You can create an add-in a few ways. One way you'll find on here by doing a search or there is another way by creating an Add-In using VSTO (Visual Studio Tools for Office). There's a good book on it here

HTH

C