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
    Sh.Delete
    Application.DisplayAlerts = True
    MsgBox "You cannot insert sheets in this workbook."
End Sub

Re: Select Distinct To Populate Combo Box

Hi,

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):

rstsql.fields("test").name

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:

Dim cnn As ADODB.Connection
    Dim rstSQL As ADODB.Recordset
    Dim lngIndex As Long
    Dim lngRow As Long
    
    'Connection
    Set cnn = New ADODB.Connection
    cnn.Provider "Microsoft.jet.oledb.4.0"
    cnn.ConnectionString = "c:\tmp\test.mdb"
    cnn.Open
    
    'Recordset
    Set rstSQL = New ADODB.Recordset
    rstSQL.Open "SELECT * FROM tblTest", cnn, adOpenDynamic, adLockReadOnly
    lngRow = 2 'Start writing on row 1
    Do While rstSQL.EOF = False
        For lngIndex = 0 To 2 '2 if you have 3 fields(start counting from 0)
            ActiveSheet.Cells(lngRow, lngIndex + 1).Value = rstSQL.Fields(lngIndex)
        Next
        lngRow = lngRow + 1
        
        rstSQL.MoveNext
    Loop
    
    'Close connection
    rstSQL.Close
    Set rstSQL = Nothing
    cnn.Close
    Set cnn = Nothing

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

...
 Do While rstSQL.EOF = False
        For lngIndex = 0 To 2 '2 if you have 3 fields(start counting from 0)
            ActiveSheet.Cells(lngRow, lngIndex + 1).Value = rstSQL.Fields(lngIndex)
        Next
        lngRow = lngRow + 1
        
        rstSQL.MoveNext
    Loop


...

Use:

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
        rstSQL.MoveNext
    Loop

Re: Select Distinct To Populate Combo Box

I don't understand your question.

Do you mean:
- get data from an access table into excel
- or get data from excel into access
...

Can you be more specific?

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 
        rstComb.movenext
Loop

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:

Quote

SELECT [DEPOT],[STOCK],[COMPLETED],[TIME_FLAG]
FROM tblBreaks WHERE (DEPOT='" & txtDepotSearch.text & "'
[ALLOCATED_TO] = '" & sBreakName$ & sSelect

Re: Select Distinct To Populate Combo Box

Yes,

you can do just the same for the other combobox. Just add another loop and do the same.

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.

'Here you loop the combobox:
'Start to search the combobox from item 0 until last item
For intcounter = 0 To txtStocksearch.listcount-1 
        'Check if item is equal to current record-item from the recordset
        'If the item is equal we exit the loop because we found the item
        If frmAgent.txtStockSearch.list(intcounter) = rsComb!STOCK Then exit For 
    Next 
     
 'Correction < should be =
'If our counter is equal to the total items in our box we have looped the box without finding the item => so we add the item
    If intcounter [COLOR="Red"]=[/COLOR] txtStocksearch.listcount Then frmAgent.txtStockSearch.AddItem rsComb

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:

...


dim intCounter  as integer 
Do While Not rsComb.EOF 
        frmAgent.txtDepotSearch.AddItem rsComb!DEPOT 
        
        'Here you loop the combobox
        for intcounter = 0 to txtStocksearch.listcount-1
             if frmAgent.txtStockSearch.list(intcounter) = rsComb!STOCK then exit for
        next


        if intcounter < txtStocksearch.listcount then frmAgent.txtStockSearch.AddItem rsComb!STOCK 
        


        rsComb.MoveNext 
 Loop 


...

Hope this gives you an idea.

Re: Querying An Oracle Database In A Function

Hi,

example of an ado-connection to oracle:

dim cnn As ADODB.Connection
dim rst As ADODB.Recordset
dim strSQL as string


Set cnn = New ADODB.Connection
cnn.Provider = "msdaora"
cnn.Open "Data source=database.test.com;User ID=test;Password=pwd;"


strSQL = "Select PostCode From CustDetails Where CustomerName = 'Gollem'"    


Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenDynamic, adLockReadOnly
if rst.eof = false then msgbox rst.fields!postcode


rst.close
set rst = nothing 


cnn.close
set cnn = nothing

Hope this helps.

Re: Select Distinct To Populate Combo Box

Hi,

you can try to loop through the combobox to check if the stock is already added. If it's in the combobox => don't add otherwise add.

Re: Round to Specfied Multiple

Quote

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.

Gollem

Re: Round to Specfied Multiple

Nice solution 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.

Ex:

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,

Gollem

Re: Date Format And Regional Settings

Perhaps thiw works:

Format(Date, "DD/MM/YYYY") & ", " & Time

or this

"[B][COLOR="Red"]'[/COLOR][/B]" & Format(Date, "DD/MM/YYYY") & ", " & Time

Re: Combine Several Files Into One

Hi,

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

Dim objFso      As Object
    Dim objFolder   As Object
    Dim objFile     As Object
    Dim objFiles    As Object
    Dim strFolder   As String
    Dim strWorkbook As String
    
    strWorkbook = "ozz.xls" 'Main workbook
    
    'Create objects
    Set objFso = CreateObject("Scripting.FileSystemObject")
    strFolder = "c:\tmp\test" 'Folder directory
    Set objFolder = objFso.getfolder(strFolder)
    Set objFiles = objFolder.Files
    
    'Loop through files
    For Each objFile In objFiles
        Workbooks.Open strFolder & "\" & objFile.Name
        'Copy sheet
        ActiveWorkbook.Sheets(1).Copy Before:=Workbooks(strWorkbook).Sheets(1) 'Copy sheet
        Windows(objFile.Name).Activate
        ActiveWorkbook.Close
    Next objFile
    
        
    'Clear memory
    Set objFile = Nothing
    Set objFiles = Nothing
    Set objFolder = Nothing
    Set objFso = Nothing

Hope this gives you a start.

Gollem

Re: File Remains Open In Vba Background

The problem occurs with or without Vba-code... so you don't even have code to have the problem.

I don't have the google desktop.

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.

Gollem