Posts by Kyle123

Kyle123 · Sep 26th 2017

Re: SQL multiple counts from same column

Are there a set amount of externalnames so do you just want 2B and 2A or could there be any number i.e are there 2C 3D etc?

Kyle123 · Feb 8th 2016

Re: Adding a collection to a class object

It depends how far you want to go into this, the simplest method is simply to expose the collection on the parent class. The calling code then deals with the inserting.

Code

[color=blue]Private[/color] pColWeeksTasks              [color=blue]As[/color] [color=blue]Collection[/color]
[color=blue]Public[/color] [color=blue]Property[/color] [color=blue]Get[/color] [color=darkcyan]ColWeeksTasks[/color]() [color=blue]As[/color] [color=blue]Collection[/color]: [color=blue]Set[/color] [color=darkcyan]ColWeeksTasks[/color] = pColWeeksTasks: [color=blue]End[/color] [color=blue]Property[/color]

If you want to have the Parent class deal with adding children then you're probably looking towards a custom collection class, have a look here: http://dailydoseofexcel.com/ar…/custom-collection-class/

Kyle123 · Jan 29th 2014

Re: Help with: Prefill cells with command line switches from a browser

Crossposted:
http://www.mrexcel.com/forum/excel-q...s-browser.html

http://www.excelforum.com/excel-programming-vba-macros/985127-help-with-prefill-cells-with-command-line-switches-from-a-browser.html#post3563939

http://www.vbaexpress.com/forum/show...027#post304027

Kyle123 · Aug 13th 2013

Re: exit event on msforms.listbox

You can't do it; the Exity and entery events, I believe are really raised by the parent container which is why you don't see them, there are some laborious work arounds, but not really worth it in my opinion; have a look here: http://www.mrexcel.com/forum/e…cus-userform-control.html

Kyle123 · Mar 26th 2013

Re: Excel Macro that utilizes Toodledo.com's API

Glad it helped

Kyle123 · Mar 21st 2013

Re: Excel Macro that utilizes Toodledo.com's API

MD5 hashing is easiest using the .Net objects, this creates the same output as the PHP MD5

Code

Function MD5(sInput As String) As String
Dim oTxt As Object
Dim b() As Byte
Dim l As Long




    Set oTxt = CreateObject("System.Text.UTF8Encoding")
    
    With CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
      b = .ComputeHash_2((oTxt.GetBytes_4(sInput)))
    End With




    For l = LBound(b) To UBound(b)
        MD5 = LCase(MD5 & Right$("00" & Hex$(b(l)), 2))
    Next l




End Function

Display More

Kyle123 · Mar 21st 2013

Re: Excel Macro that utilizes Toodledo.com's API

This is perfectly possible, if a little bit of a pain in the arse - sending and receiving data to a website is very straightforward and since the website returns xml, it's very easy to parse. Have a look at the MSXML2 classes, they will handle both sending and parsing. The bit that's a pain in the arse is the format that you need to send your data in, it expects JSON formatted strings, that are url encoded. There is nothing in Excel that is going to do this for you, but the JSON is pretty straightforward - so you should be able to write functions to build it.

I obviously can't check the below, but adding would look something like (obv with no error handling):

Code

Private Const sAPIKey As String = "MyAPIKey"




Sub AddTask()
    Dim oXml As Object: Set oXml = CreateObject("MSXML2.DomDocument")
    Dim oReq As Object: Set oReq = CreateObject("MSXML2.XMLHttp")
    Dim oTasks As Object, oTask As Object
    
    With oReq
        .Open "POST", "http://api.toodledo.com/2/tasks/add.php?key=" & sAPIKey, False
        .Send "tasks=[{""title""%3A""My Task""}%2C{""title""%3A""Another""%2C""star""%3A""1""%2C""ref""%3A""98765""}]" _
                & "&fields=folder,star&f=xml"
        oXml.LoadXML .responseText()
    End With
    
    For Each oTasks In oXml.ChildNodes
        For Each oTask In oTasks.ChildNodes
            Debug.Print oTask.SelectSingleNode("id").Text
        Next oTask
    Next oTasks
End Sub

Display More

As far as I can see, the biggest pin will be generating the api key - Excel doesn't have any means of creating an MD5 hah, so you have to get a bit creative

Kyle123 · Feb 28th 2013

Re: Ues SQL on data in Active Workbook

Yes it's possible as shown in the post above, but you don't really want to do it since it causes memory leaks.

Kyle123 · Feb 25th 2013

Re: Create VBA code and place it inside Sheet module

Would be much easier to use regular forms buttons:

Code

Sub test()Dim btn As Button
For Each btn In Sheets(1).Buttons
    btn.OnAction = "Calculate"
Next btn
End Sub

Kyle123 · Feb 14th 2013

Re: Completely Redesign the Microsoft Excel/Access interface forAutomated Reporting

That doesn't look like Excel or Access to me, I would say that it's a windows application that possibly provides add-ins to Excel and Access. The biggest giveaway that this is the case is this from the faq:

Quote

Do I need to have Excel installed?
No, you do not need to have Excel installed to review the results. However, you will need Excel if you wish to export graphs or tables.

So your questions largely becomes moot, since office isn't used to produce any of the interface graphs, charts and the suchlike are not bound by the restrictions of Excel or Access.

So in summary, to produce an application like that, then yes, you would need to learn a new programming language, to have it look like that, it would need to be within the Microsoft .Net framework so C++, C#, VB.NET, F# etc. The advantages of these languages are that they are infinitely faster than VBA and give you much more control and free you from the limits of office. To produce something similar to the application you linked to, would require a huge amount of work, probably with multiple programmers working on different aspects of it - it's not a simple thing to accomplish. Things like pivot tables are extremely complex and for larger datasets, you would probably be better looking at other underlying technologies OLAP etc, working within the Excel/Access environment you take a lot of things for granted, MS has put a huge amount of work into making tools like pivot tables and you'd need to design those sort of tools again - which would be no small task in itself let alone just as an aspect of an application. (though a quick google of windows forms pivot table turn up a lot of controls that you could add to your application - they ain't cheap though)

The reason that commercial data analysis tools are so expensive are down to the amount of work that has gone into creating the product. So, this probably isn't the ideal starter application for learning a new language.

Kyle123 · Jan 30th 2013

Re: SQL statement in .sql file through VBA

You are almost there, you've got past there and gone too far

Code

Dim Cnn As New ADODB.Connection Dim Rst As New ADODB.RecordSet 
Dim ConnectionString, SqlTextFile, SqlStatement As String 
 
 'Prevent screenupdating when this macro runs
Application.ScreenUpdating = False 
 
 'Clear the destination sheet
ThisWorkbook.Sheets("Test").Range("A2:B3000").ClearContents 
 
 'Connection to SQl Server
ConnectionString = "Provider=SQLOLEDB.1;" & _ 
"Integrated Security=SSPI;" & _ 
"Initial Catalog=DATA;" & _ 
"Data Source=Server01" 
 
Cnn.Open ConnectionString 
Cnn.CommandTimeout = 900 
 
SqlTextFile = "C:\Test.sql" 
Debug.Print SqlTextFile 
Dim hFile As Long 
hFile = FreeFile 
Open SqlTextFile For Input As #hFile 
SqlStatement = Input$(LOF(hFile), hFile) 
Close #hFile 
Debug.Print SqlStatement 




rst.open SqlStatement, cnn

Display More

Kyle123 · Dec 10th 2012

Re: Finding data between two dates using ADODB

Cross posted here: http://www.excelforum.com/exce…wo-dates-using-adodb.html

Kyle123 · Nov 28th 2012

Re: IE Automation "Download File" dialog box

xposted: http://www.excelforum.com/exce…load-file-dialog-box.html and http://www.mrexcel.com/forum/e…load-file-dialog-box.html

Kyle123 · Nov 26th 2012