Posts by gollem

gollem · Aug 12th 2004

Hi,

why let the user give a name of a file? You can get the problem that when there are duplicates on the system, the program doesn't know which file to use. Another aspects is that it could take a while, so for a little program performance lowers.

Searching a file is no problem I guess, but I should use the search in a directory or let the user select the specific file.

What do you mean by giving in a range? For example C5 => gives the data of cell C5?

The code for selecting a file:

Code

Dim strFile         As String


strFile = Application.GetOpenFilename

Code for opening a file:

Code

workbooks.open strFile

Code to get data from workbook:

Code

activesheet.range("C5").value  'gives value of cell "C5"

Hope this gives some ideas.

Gollem

gollem · Aug 12th 2004

Hello,

all the things you mentioned can be done. But you have to be more specific and perhaps you should ask step by step the things you want to do.

You want to search the system for a file? Why not let the user select the file? Otherwise you could get problems with duplicate files. What data would you like to find in that file? Where should the data filled in?

Do you want that the program starts when pressing a button?

....

Gollem

gollem · Aug 12th 2004

Hi,

use

Code

sheets.select

Gollem

gollem · Aug 11th 2004

Hello,

you perhaps could use the replace function:

example: replace(string,vblf, "")

Gollem

gollem · Aug 11th 2004

Hi,

If you want to search with SQL a text-field, you must use ' '.

For your example:

Code

"SELECT FullName FROM tunnukset WHERE US_Username ='" &  nimi & "';"

Gollem

gollem · Aug 11th 2004

Hi,

check this thread: http://www.ozgrid.com/forum/showthread.php?t=22388

This is another method but maybe a solution for your problem.

Gollem

gollem · Aug 11th 2004

Hi, I'm glad it helped.

About your question I 'm sure if I understand:

Do you want that for example:

sheet1 => data1
sheet2 => data2
sheet3=> data2
sheet4=> nothing
sheet5=> nothing

the program loads the data until sheet4 and when it notices that there is no data, the program stops looping, loading data from the other sheets?

If this is the case try to add the if-structure below:

Code

intCounter = 1
    intIndex = 0
    Do While intCounter < ActiveWorkbook.Sheets.Count
        'Select next sheet
        Sheets(intCounter).Select
        
        'Check if data => when no data => exit loop
        If ActiveSheet.Range("A5").Value = "" Then
            Exit Do
        End If
        
        'Load value in table (from every sheet)
        strTable(intIndex) = ActiveSheet.Range("A5").Value
        intIndex = intIndex + 1
        intCounter = intCounter + 1
    Loop

Display More

After exiting the loop, the program will start(with the next loop) writing the data.

Gollem

gollem · Aug 11th 2004

Hello,

I've made one mistake already. Sorry for that: I've declared dbTable as integer => it should be a double.

If you are certain that in every A5 cell is a number, you could try this first.

The changing of sheetnames doesn't matter except for your last sheet(in mine example the "test" sheet).

I've adtapted the example for the string version, I will also give a short explanation of the program:

Code

Private Sub CommandButton1_Click()
    Dim intCounter              As Integer
    Dim intIndex                As Integer
    Dim strTable(100)           As String
    
    intCounter = 1
    intIndex = 0
    Do While intCounter < ActiveWorkbook.Sheets.Count
        'Select next sheet
        Sheets(intCounter).Select
        'Load value in table (from every sheet)
        strTable(intIndex) = ActiveSheet.Range("A5").Value
        intIndex = intIndex + 1
        intCounter = intCounter + 1
    Loop
    
    Sheets("Test").Select
    'Start filling data on row 7
    intCounter = 7
    intIndex = 0
    Do While strTable(intIndex) <> ""  
'In the example with the dbTabel it has to be 0 because we work 
'with numbers(integer,double)
        ActiveSheet.Cells(intCounter, 1).Value = strTable(intIndex)
        '1 3 5 7 ....
        intIndex = intIndex + 2
        intCounter = intCounter + 1
    Loop
End Sub

Display More

Code

Dim intCounter              As Integer
    Dim intIndex                As Integer
    Dim strTable(100)           As String

intCounter is a counter that we use to loop every worksheet, so instead of using a name I use a number(index) to select a sheet. Sheets(1).select, sheets(2).select, ....

intindex is used for loading the table(first entrance=0)

strTable(100) is an array of strings in this case who can have max 101 items(0-100) => in this array we load every A5 value of every sheet, example:
sheet(1) strtable(0)=5
sheet(2) strtable(1)=2
sheet(3) strtable(2)=7
....

Code

intCounter = 1
    intIndex = 0

First sheet and first entrance of the table

Code

Do While intCounter < ActiveWorkbook.Sheets.Count
        'Select next sheet
        Sheets(intCounter).Select
        'Load value in table (from every sheet)
        strTable(intIndex) = ActiveSheet.Range("A5").Value
        intIndex = intIndex + 1
        intCounter = intCounter + 1
    Loop

Looping the sheets and loading the data of every sheet(except last sheet)=> automatically

Code

Sheets("Test").Select
    'Start filling data on row 7
    intCounter = 7
    intIndex = 0
    Do While strTable(intIndex) <> ""
        ActiveSheet.Cells(intCounter, 1).Value = strTable(intIndex)
        '1 3 5 7 ....
        intIndex = intIndex + 2
        intCounter = intCounter + 1
    Loop

Selecting the "test" sheet => has to be your last sheet where you want to fill in the data. The counter is here used to present the row here you want to put the data. The intindex is the entrance of the table.

So if you initialise the index to 0 (you start with the value of sheet1), +2 after filling in means that you get strtable(2) => means values of sheet3(sheet2 is skipped). If you want to start with the value of sheet2 and then sheet4, .... you should initialize the index to 1.

I hopes this clears things up for you.

Gollem

gollem · Aug 10th 2004

Hi John,

that's indeed an anoying thing. If the mid function works you can use it the same as a left function.

Mid(string,1,length)

Gollem

gollem · Aug 10th 2004

Hi,

check the code and the attachement. The attachement has example solution, you should be able to adapt the code for your needs.

Code

Private Sub CommandButton1_Click()
    Dim intCounter              As Integer
    Dim intIndex                As Integer
    Dim dbTabel(100)            As Integer
    
    intCounter = 1
    intIndex = 0
    Do While intCounter < ActiveWorkbook.Sheets.Count
        'Select next sheet
        Sheets(intCounter).Select
        'Load value in table (from every sheet)
        dbTabel(intIndex) = ActiveSheet.Range("A5").Value
        intIndex = intIndex + 1
        intCounter = intCounter + 1
    Loop
    
    Sheets("Test").Select
    'Start filling data on row 7
    intCounter = 7
    intIndex = 0
    Do While dbTabel(intIndex) <> 0
        ActiveSheet.Cells(intCounter, 1).Value = dbTabel(intIndex)
        '1 3 5 7 ....
        intIndex = intIndex + 2
        intCounter = intCounter + 1
    Loop
End Sub

Display More

The example uses an array of numbers, if the values are text, you have to adapt the table in a string.

Gollem

gollem · Aug 10th 2004

Ok, didn't read your question correctly.

This should do the job. I've adapted the code, fixed a little mistake and simplified a little bit.

Code

Private Sub cmdDelete_Click()
    Dim intCounter          As Integer
    Dim strField            As String
    Dim intFieldRow         As Integer
    Dim blnDeleted          As Boolean
    
    'Sort data
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
    'First field
    strField = ActiveSheet.Cells(1, 1).Value
    intFieldRow = 1
    'Data start on row 1 => begin filtering row 2
    intCounter = 2
    blnDeleted = False
    Do While ActiveSheet.Cells(intCounter, 1).Value <> ""
        Select Case strField = ActiveSheet.Cells(intCounter, 1).Value
            Case True
                'Check if begindata is deleted
                Select Case blnDeleted
                    Case False
                        'Not deleted yet => delete
                        Rows(intFieldRow & ":" & intFieldRow).Delete Shift:=xlUp
                        blnDeleted = True
                        'Delete row => set back counter
                        intCounter = intCounter - 1
                 End Select
                        
                'Delete data
                Rows(intCounter & ":" & intCounter).Delete Shift:=xlUp
                'Delete row => set back counter
                intCounter = intCounter - 1
            Case Else
                'New item
                strField = ActiveSheet.Cells(intCounter, 1).Value
                intFieldRow = intCounter
                blnDeleted = False
        End Select
        intCounter = intCounter + 1
    Loop
End Sub

Display More

Hope this works for you.

Gollem

gollem · Aug 10th 2004

Hi,

you should sort the data on that column and then use a loop to filter the doubles.(see attachement or code below)

Code

Private Sub cmdDelete_Click()
    Dim intCounter          As Integer
    Dim strField            As String
    
    'Sort data
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
    'Data start on row 1 => begin filtering row 2
    intCounter = 2
    strField = ActiveSheet.Cells(intCounter, 1).Value
    Do While ActiveSheet.Cells(intCounter, 1).Value <> ""
        Select Case strField = ActiveSheet.Cells(intCounter, 1).Value
            Case True
                'Delete data
                Rows(intCounter & ":" & intCounter).Select
                Selection.Delete Shift:=xlUp
                'Delete row => set back counter
                intCounter = intCounter - 1
            Case Else
                'New item
                strField = ActiveSheet.Cells(intCounter, 1).Value
        End Select
        intCounter = intCounter + 1
    Loop
End Sub

Display More

Hope this helps.

Gollem

gollem · Aug 10th 2004

Hi Suju,

see the attachement.

Code

Option Explicit


Private Sub cmdClickForStrategy_Click()
    Select Case True
        Case optSAVolatile.Value And optPPUL.Value And optSARPLim And optSADet And optLow.Value
            MsgBox "yOUR STRATEGY IS: Long Straddle or Long Synthetic Straddle"
    End Select
End Sub

Gollem

gollem · Aug 9th 2004

Hi,

use this syntax:

Code

DoCmd.OpenReport "rptReport", acNormal

rptReport for your report.

Gollem

gollem · Aug 9th 2004

Hi,

I'm not sure but I think you want to do the same as in this thread, take a look at it.

http://www.ozgrid.com/forum/showthread.php?t=22259

Gollem

gollem · Aug 9th 2004

Hi,

this is indead possible. Just use some counters do to this, something like this for example:

Code

Dim intCounter1         As Integer
    Dim intCounter2         As Integer
    Dim strTable(1000, 20)  As String
    
    'Initialize table
    
    
    'assuming max 1000 rows and 20 columns
    intcounter = 1
    Do While ActiveSheet.Cells(intCounter1, 1).Value <> ""
        intCounter2 = 1
        Do While intcounter < 21
            strTable(intCounter1 - 1, intCounter2 - 2) = ActiveSheet.Cells(intCounter1, intCounter2).Value
            intCounter2 = intCounter2 + 1
        Loop
        intcounter = intcounter + 1
    Loop

Display More

Gollem

gollem · Aug 6th 2004

Hi,

you can use ADO to do this. First select the Microsoft activeX Data objects library(2.1) for example(Tools -references).

Code:

Code

Dim rstTable        As ADODB.Recordset
    Dim cnn             As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    Set rstTable = New ADODB.Recordset
    
    'Open connection
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"   'Access 2000
    
    cnn.ConnectieString = "c:\tmp\database.mdb"
    cnn.Open
    
    'Open table
    rstTable.Open "select * from tblTable", cnn, adOpenDynamic
    
    'Search key
    If rstTable.BOF = False Then
        rstTable.MoveFirst
    End If
    
    rstTable.Find "field1=" & ActiveSheet.Cells(1, 1).Value 'if keys is numeric
    If rstTable.EOF = True Then
        'Not founded => add record
        rstTable.AddNew
        rstTable.Fields!field1 = ActiveSheet.Cells(1, 1).Value
    End If
    
    rstTable.Fields!field2 = ActiveSheet.Cells(1, 2).Value
    rstTable.Fields!field3 = ActiveSheet.Cells(1, 3).Value
    rstTable.Update
    
    
    'Close
    rstTable.Close
    Set rstTable = Nothing
    cnn.Close
    Set cnn = Nothing

Display More

You should be able to adapt this for your needs (add a loop to use all data)

Gollem

gollem · Aug 5th 2004

Hi,

check the uploaded file. It compares colA and colB of the same workbook and writes differences in colC.

(Just add code to switch between the workbooks if you want to use it for different workbooks=> it's the same way of working)

Hope you can use this.

Gollem

gollem · Aug 5th 2004

Hi,

try to use a loop function, something like this:

'Do until row 2 of book B is "" (assuming when you start macro you are in workbook B)

Do While ActiveSheet.Cells(2, 1).Value = ""
Windows("BookA").Activate
'print the stuff
Windows("BookB").Activate
'Clear row

'Copy row

'Delete row
Loop

(Just add a check if a cell from row2 is empty=> no more records)

Gollem

gollem · Aug 5th 2004

Hi,

You should work with a boolean. You set the boolean to false when the form starts, when you push the cancel-button you set the boolean to true. In the exit-module you do:

if boolean = false then
'do your stuff
end if

Gollem

Posts by gollem

Can VBA take user input (form?), search and locate file, retreive data and output?

functions to assign strings as variables and search with regular expressions?

Select all the worksheets

Feeding a spreadsheet from a UserForm

SQL.request and My Query -question

SQL.request and My Query -question

Copy / PasteSpecial from various worksheets to one worksheet

Copy / PasteSpecial from various worksheets to one worksheet

LEFT(String,Length) not working

Copy / PasteSpecial from various worksheets to one worksheet

Deleting multiple rows

Deleting multiple rows

VBA IF Statement

printing various Access report

Use macro to give Hyper link in another workbook

ranges into arrays using cells(x,y) not range("")

Excel to Access using SQL

compare range and copy

Looking for a blank row & repeating tasks

How to prevent event from occuring when button is clicked ??