Posts by gollem

Thanks WillR,

my mistake.

gollem

Hi,

try to replace the decimal character. if "." => ",", if "," => "." before you take the value.

(Use replace () function)

It could be that Vb sees the decimal as thousands seperator.

Gollem

Hi,

welcome to ozgrid.

You could use ADO to resolve the problem.

Check the links below(examples are for an access database, but you can use it for SQL also):

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

http://www.excelkb.com/default.aspx...V&pNodes=8M3M1M

Hope this is sufficient to help you starting.

Gollem

Ok I see, that's indeed a lot more difficult.
I 'm not familiar with the method you use.

Another thing you could try is this:

"'" & Information & "'"

Try this also for the Person-variable.

I don't know if this helps, but it's the only other notation I know for strings.

Gollem

If you want to check if the combination "SIN" is in a string(beginning, ending, middle, ...)

Use

intPosition = InStr(1, strstring, "SIN")

If it's in the beginning of the string => result 1
If it's not in the string => result 0

Gollem

Hi,

try this

if left(strString,3) = "SIN" then
     'Your code
end if

Gollem

Ok,

if Information and Person are the variables:

MsgBox "The Custom Sheet contains the " & Information & " information for " & Person & ".", vbOKOnly, "Custom Sheet Selected"

Gollem

Hi,

look at this

MsgBox "The Custom Sheet contains the " & strst1 & "Information " & str2 & " information for " & str2 & " Person " & strstring3 & ".", vbOKOnly, "Custom Sheet Selected"

It is not exact the same as your example, but you now should be able to use the strings and text correctly.

Hope this helps,

Gollem

Hello,

a small example of ADO-code you can find in this thread:

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

Gollem

Hi,
check the updated attachements.

Macro only works when the two workbooks are open.

Gollem

Hi,

welcome to ozgrid.

I'm not sure if I understand correctly but you want to select a text-file from a listbox and than you want to see the data of that opened file in another listbox?

=> you should use a rich-text box for that and not a listbox

(right click on the toolbox) Additional controls - microsoft richtextbox control...
set the properties of scrollbars on both

To show the data of a text-file, just use this code:

RichTextBox.Filename = "c:\test.txt"

Hope this helps.

Gollem

Hi,

could you post an example or upload an attachement, would be helpful.

I can already say one thing: when you want pause in a certain code and than execute another and than back where you've stopped... I don't think it's possible(perhaps it is), I think you can only make some stops at certain points but not on every line.

Gollem

Another check you can do is this:

If trim(ActiveSheet.Cells(11, 1).Value) = "USD" Then 
    MsgBox "ok" 
Else 
    MsgBox "not ok" 
End If

If this gives "ok" there are space(s).

Check also: Usd or USD (capitals?)

Gollem

Hi,

try to add this to your msgbox as a check:

msgbox "'" & ActiveSheet.Cells(11, 1).Value & "'"

Check if there are any spaces before or after the values.

Gollem

Hi,

I'm not sure this is what you are looking for but see the attachement.
When you press the button the macro creates 3 files.

Dim intColumn           As Integer
    Dim strPath             As String
    
    strPath = ActiveWorkbook.Path & "\"
    
    'Start column 3
    intColumn = 3
    Do While ActiveSheet.Cells(4, intColumn).Value <> ""
        'Add workbook
        Workbooks.Add
        'Save workbook
        ActiveWorkbook.SaveAs strPath & "index " & intColumn - 2
        
        'Copy standard column
        Windows("Racer.xls").Activate
        Columns(2).Select   'Column B
        Selection.Copy
        Windows("index " & intColumn - 2 & ".xls").Activate
        Application.Columns("A:A").Select
        Application.ActiveSheet.Paste
        
        'Copy column race
        Windows("Racer.xls").Activate
        Columns(intColumn).Select 'Column C-D-E...
        Selection.Copy
        Windows("index " & intColumn - 2 & ".xls").Activate
        Application.Columns("B:B").Select
        ActiveSheet.Paste
        ActiveWorkbook.Close savechanges:=True
        
        intColumn = intColumn + 1
    Loop

I hope this helps you starting.

Gollem

Hi,

please post an example(attachement or just code), it will be easier to answer, help.

Gollem

Hello,

the code I wrote is written in Vb 6.0, you also can use this in VBa. I don't think you understood the solution I gave. The user doesn't have to create a template everytime. You just create 1 excel where the data is written to when the user executes the "experiment".

Why are you using Excel to write logs to? As you noticed it's slow, the fastest way that I know is the method I 've described(if using excel). If you are filling cell by cell ... slow(this is a standard method).

To answer on you question(see also attachement):

Dim intPosition         As Integer
    Dim strStringA          As String
    Dim strStringB          As String
    
    'Find startposition ","
    intPosition = InStr(1, ActiveSheet.Range("A1").Value, ",")
    
    strStringA = Left(ActiveSheet.Range("A1").Value, intPosition - 1)
    strStringB = Mid(ActiveSheet.Range("A1").Value, intPosition + 1)
    
    ActiveSheet.Range("A1").Value = strStringA
    ActiveSheet.Range("B1").Value = strStringB

You should be able to adapt the code(built in a loop, use "." instead of ",", ...), this is the basic.

Hope this helps to solve the problem.

Gollem

Hi,

if you want to use excel as logger => it is slower and if I understand correctly you are going to have a lot of records. You are also limited(can't go on forever).

The best thing you can do is using a text-file or an database(SQL, access, ...) and then using SQL-statement to add records.

If you really want to use excel I know another method but I don't know if it's fast enough. You make an excel template, name the range of a sheet for example two columns "tblData" and save the file. Now, you can use the excel-file like a database.

Use ADO to add records:

Dim rstData         As ADODB.Recordset
Dim cmm             As ADODB.Connection
    
Set rstData = New ADODB.Recordset
Set cmm = New ADODB.Connection


'Make connection    
cmm.Provider = "Microsoft.jet.oledb.4.0"
cmm.ConnectionString = "Data source="c:\test.xls";Extended properties=Excel 8.0"
cmm.Open
    
rstData.open "select * from tblData", cmm, adOpenDynamic, adLockOptimistic


'Add record
rstdata.addnew
rstdata.fields(0).value = ""       'ColumnA
rstdata.fields(1).value = ""       'ColumnB
rstData.update


'close connection
rstData.close
set rstData=nothing


cmm.close
set cmm = nothing

I didn't test this but you should be able to adapt the code.

Gollem

Hello,

to open a workbook(if you know the path) you can use

workbooks.open "c:\tmp\one.xls"

to select a heet in that workbook:

activeworkbook.sheets("sheet1").select

to retreive data from a cell:

activeworkbook.activesheet.range("A1").value

Gollem

Hi,

first of all welcome to the forum.

If I understand correctly: you want to create a file from every record where the date of column O is equal to the date of today?

You want to use text-files, you can do that but if you don't know much of VBa I would prefer an Excel template. So you create a new workbook with the same data as the text-file using columns. Save this for example as sampleTemplate.xls.(Will be easier to work with)

Now comes the hard part: you have to make a loop that checks every record and if a record is found => you have to copy(take) the values to the template. After filling in the template you can safe the file.

(So first step make an exceltemplate, that's my opinion)

Gollem