Posts by gollem

    Re: Edit Text Strings

    With VBA:

    dim strString  as string 
    dim intPos      as integer 
    strString = "INSERT INTO default_en_listingsdb VALUES('1','1','Example Listing','2003-07-01','This is an example listing!','Blah!','2002-07-01','2006-08-10 16:18:18','29','no','no','no');"
    'Search pos (
    intpos = InStr(1, strstring, "(")
    strString = Mid(strString, intPos + 1)

    Re: Disable User From Adding Sheets

    Another approach is program a macro that deletes every sheet when adding one:

    Copy this in your ThisWorkbook Module

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
        MsgBox "You cannot insert sheets in this workbook."
    End Sub

    Re: Select Distinct To Populate Combo Box


    yes this is possible, you better search the net for this.

    I've used this a long time ago and I want to make a remark: If it's a lot af data that has to be written the application can slow down.

    Re: Select Distinct To Populate Combo Box

    To get the name of a field(col-header):


    This should help you to get the col-names.

    To see how you can add sheets, workbooks, ... you can take a look at the macro recorder. Record a macro and see what code is used.

    Hope this helps.

    Re: Select Distinct To Populate Combo Box

    Hi this can be a global procedure:

    This part is perhaps easier for you, instead of using:


    Do While rstSQL.EOF = False
            ActiveSheet.Cells(lngRow, 1).Value = rstSQL.Fields!Name                
            ActiveSheet.Cells(lngRow, 2).Value = rstSQL.Fields!Des                
            ActiveSheet.Cells(lngRow, 3).Value = rstSQL.Fields!Test
            lngRow = lngRow + 1

    Re: Select Distinct To Populate Combo Box

    Now you can use distinct and you don't have to use the loop I've provided.

    rsComb.Open "SELECT [COLOR="Red"]DISTINCT[/COLOR] [DEPOT],[ .......
    Do While Not rsComb.EOF 
            frmAgent.txtStockSearch.AddItem rsComb!STOCK 

    Re: Select Distinct To Populate Combo Box

    Yes this is possible. Just use the same code as you use to populate the comboboxes. Only you populate 1 combobox an d you have have to se the filter in your sql-statement, something like this:


    FROM tblBreaks WHERE (DEPOT='" & txtDepotSearch.text & "'
    [ALLOCATED_TO] = '" & sBreakName$ & sSelect

    Re: Select Distinct To Populate Combo Box

    Ok, I'll try to explain a little more. The example I provide is only for 1 combobox, but you can use this for the other as well.

    The global thing that I do is, before I add a new element in the combobox I check if the item I want to add already exists in the combobox. If the item is founded in the combobox I don't add the item. If the Item isn't found in the combobox I add the item.

    I hope this helps you to understand what the code actually does: checking before adding the item, if the item is in the combobox.

    Re: Select Distinct To Populate Combo Box

    You have to loop through the combobox:

    Hope this gives you an idea.

    Re: Querying An Oracle Database In A Function


    example of an ado-connection to oracle:

    Hope this helps.

    Re: Round to Specfied Multiple


    I havn't "seen" you for a while

    Yeah, I know, have been quite busy lately. The forum has grown a lot I see.
    Always nice to see that the help is still greatly supported here. :)

    See you around carl.


    Re: Script Error: Expected Number

    I agree with Carl, the error says that you are using a different datatype.
    I'm also confused: you select a number(calculation) from a table dual? So you take for example: result of your calculation = 5 => select 5 from dual??
    So your field-name is 5 then strange...

    I would assume that you want to select an numberfield from the table who's value is equal to the calculation you make.


    select * from dual
    where NumberField= round(ABS(2.15),1-floor(LOG(10,2.15))) ;

    This sql takes all data from the table where the NumberField(Your database field name) is equal to your calculation

    Hope this helps,


    Re: Combine Several Files Into One


    this macro opens every file in a directory and copies a certain sheet to the main workbook:

    Hope this gives you a start.


    Re: File Remains Open In Vba Background

    Hi Roy,

    thanks for the reply.
    I've noticed that at home I don't have the problem...
    So I'm not sure what the problem is at work, the first thing I thought was installation, but I've noticed that the problem is on every PC at work... so it's a global problem. Thanks for the reply anyway, now I know that it's probably a installation problem.