Posts by Rich_z

Re: Data Entry Via Userform

Hi,

You can loop round the controls on your form using a construct such as:

For Each Control In Characteristic_Entry.Controls

Where in this case Characteristic_Entry is the name of the form and Control is declared as a Control.

You can then identify which controls you are interested in by for example giving them each a root name followed by an extention to differentiate them (it's a shame you can't have control arrays).

So in your code you would do something like this:

For Each Control In Characteristic_Entry.Controls
                  '*
                  '* See if this name exists in the values from the spread sheet.
                  '*
                  If Left(Control.Name, 4) <> "Titl" And Mid(Control.Name, 5, 1) = "0" Then

So here, I am looking at the control name and only processing it if does not start with the string 'Titl' and has a zero for the 5th character.

You might name your controls something like Chksafeacts_05 and chkreactions_06.

Looking for the first three characters of the control being 'chk' (translate them all to upper case in the code if you want) and the 3rd from last character being '_', if you take the value at the end of the name, this gives you the offset into your spreadsheet to place the values.

You could also build a look up table in the code to do the same thing but this is easier and when a new check box is added to the form no code changes need to take place for the existing code to handle it.

Your code would then look something like this:

For Each Control In Characteristic_Entry.Controls
                  '*
                  If Ucase(Left(Control.Name, 3)) = "CHK" And Left(Mid(Control.Name,Len(Control.Name)-3,1) = "_" Then
                     NextRow.Offset(0, Val(Right(Control.Name,2))).Value = "P"
                  End If
              Next Control

Regards

Rich

Re: Created Batchfile And Then Execute

Quote from mt_aripz

Hi Rich,

If i were to insert a module with this code and point this macro to a command button on my worksheet..will i be using the same codes?

And once i click this button, will it automatically run the batchfile?

Appreciate ur help..

Display More

You need to insert the relevant executable commands so that it picks up the data from your worksheet and outputs the executable commands to the batch file and then include the shell command to executue it,

If you point a command button to the macro and click it then yes it will run this code.

Quote

to print all the data on that spreadsheet to a batchfile and execute it.

You can't execute data.

Regards

Rich

Re: Importing Specific Csv Data Into Different Tabs

Public CSVFile As String
Public Sub ImportCSV()


Dim wb As Workbook


Dim CSVPath As String
Dim CSVFile As String


 
    Application.ScreenUpdating = False
    
    Call Sortsort    
    Application.ScreenUpdating = True


End Sub


Public Sub sortsort()


Dim Current_Sheet As Worksheet
Dim Source_String As String
Dim CSVPath As String
Dim CSVFile As String
'*
Dim Curr as String
'*
'* Dimension ALL your other variables too.
'*
On error goto ds_Err
CSVPath = "C:\Documents and Settings\matthewtruslove\My Documents\Documents\"
CSVFile = Dir(CSVPath & "*.csv")


input_file = FreeFile()


Open CSVFile For Input Access Read As #input_file


While Not EOF(input_file)


    Line Input #input_file, Source_String
    field_count = 1
    While Source_String <> ""
        field_pos = InStr(Source_String, ",")
        Select Case field_count
        Case 1
            '*
            '* Change Infield whatever to names that represent what the data
            '* is.
            '*
            infield1 = Mid(Left(Source_String, field_pos - 1), 2)
        Case 2
            infield2 = Mid(Left(Source_String, field_pos - 1), 2)
        Case 3
            infield3 = Mid(Left(Source_String, field_pos - 1), 2)
        Case 4
            infield4 = Mid(Left(Source_String, field_pos - 1), 2)
        Case 5
            infield5 = Mid(Left(Source_String, field_pos - 1), 2)
        Case 6
            infield6 = Mid(Left(Source_String, field_pos - 1), 2)
        Case 7
            infield7 = Mid(Left(Source_String, field_pos - 1), 2)
        Case 8
            Curr = Mid(Left(Source_String, field_pos - 1), 2)
        End Select
        Source_String = Mid(Source_String, field_pos + 1)
        field_count = field_count + 1
    Wend
     '*
     '* At this point the input string has been parsed into seperate fields
     '*
     '* Locate the work sheet for the currency
     '*
    Set Current_Sheet = Worksheets(Curr)
    '*
    '* Find the last used row in Current_Sheet
    '* Add 1 to it.
    '* Place the data from your infield1...Infield7 and curr in the 
    '* relevant cells.
    '*
    Set Current_Sheet = Nothing


Wend
Close #input_file
 '*
ds_exit:
On Error GoTo 0
Exit Sub
ds_Error: 
Select Case ERR.NUM 
Case whatever error number worksheet Not found Is 
    Add a New worksheet called (Currency) - ie the value In Currency 
    (Copied from your MASTER sheet) 
    RESUME 
Case Else 
    On Error Goto 0 
    RESUME 
End Select 




End Sub

Re: Created Batchfile And Then Execute

Quote from mt_aripz

Please help if this is possible.
I need to create a batchfile from data retrieved from an excel spreadsheet.

1. I have 30 pre-select users on a list with their phone numbers and want to create checkboxes on each user to be selected.

2. Selected users will then be included in a batchfile to execute but only the corresponding phone numbers will be used.

3. Probably need to use a form for it to be user-friendly...i guess.

e.g. batchfile.bat
echo OFF
::Location of the executable file:example.exe
d:

::CLI of utility
example 32165487 -tsve -g<ip> <msg>
example 45679821 -tsve -g<ip> <msg>
example 16543248 -tsve -g<ip> <msg>

pause

Display More

Hi mt_aripz,

Open a file called BATCHFILE.BAT using the OPEN statement:

Dim Outfile as Integer
Dim Out_Cmd as String
Outfile = FreeFile()
Open "batchfile.bat" for output access write as #Outfile
Print #Outfile,"@ECHO OFF"
Print #Outfile,"d:"

Loop round your selections and construct the command line you need in Out_Cmd, outputting this to the batch file using the Print # statement.

Close the file:

Close #Outfile

The execute the batch file using the shell statement. You may need to run the batch file as an argument to cmd.exe.

Regards

Rich

Re: Importing Specific Csv Data Into Different Tabs

Quote from error#9

I'm liking the efficienct logic here but i don't know how many different currency there are, this is why i dont thing the CASE method will work.

The number of currencies doesn't matter. The case statement is looking at the position in your input line for the field value it is currently working on. (oh, and you must clear these just before the INPUT LINE statement.

The code automatically copes with the different number of currencies by creating a worksheet for them, with the currency code as the name of the worksheet.

If the worksheet doesn't exist then it is added to the work book.

Another thing to do is set a flag and if this is the first record, delete any work sheets that already exist. You have to do this after you have added a new worksheet otherwise Excel complains. Name this worksheet something unique and delete all the worksheets that are not called 'hhgtya' for example then rename 'hhgtya' to the correct currency. Once that's been done, clear the flag so that you don't do the delete again this run.

Regards

Rich

Re: Consolidating Column Entries

Quote from adncmm1980

Dear all

I am probably being extremly thick as I cannot get the following code to work properly. I would be grateful to anyone who can tell me how I change the following code (Which works if the data is in column a) to be able to get it to work if the data is in column D

Code

Option Explicit
Sub RSLdifferenceCheck()


Dim objWskRSLData As Worksheet
Dim curTotalBalance As Currency, intRSLClient As Integer
  
Set objWskRSLData = Worksheets("RSL Data")
objWskRSLData.Columns("C:C").NumberFormat = "0.00"


intRSLClient = 0
intRSLClient = 2


Do Until Cells(intRSLClient, 1).Value = ""
    curTotalBalance = curTotalBalance + Cells(intRSLClient, 2).Value
    If Cells(intRSLClient, 1).Value <> Cells(intRSLClient + 1, 1).Value Then
        Cells(intRSLClient, 3).Value = curTotalBalance
        Cells(intRSLClient, 4).Value = Cells(intRSLClient, 1).Value
        curTotalBalance = 0
    Else
        Cells(intRSLClient, 3).Value = ""
    End If
    intRSLClient = intRSLClient + 1
Loop


End Sub

Display More

Kindest Regards

Display More

Place the cursor on one of the CELLS keywords and press the F1 key.

That explains the addressing notation you are looking at. Then find all the ",1"'s and change them to ",4"'s as a starting point.

Regards

Rich

Re: Open 1000 Files And Analyze Data

Hi Quickoats.

Firstly, this type of question has been answer many times before. Always try and use the Search first before posting.

Have a look at this post which will tell you how to loop round a directory containing your files and then import them.

Regards

Rich

Re: Importing Specific Csv Data Into Different Tabs

Quote from error#9

New sheets if the Currency has been found already?
on the next available row if already exists?

Isn't 'found already' and 'if already exists' the same thing ??

I'm not going to code it for you but you do something like this (and plug this into VBA, pressing F1 on the blue keywords.... I did say capitalised, but the code formatter undid all my hard work!)

'*
'* Don't forget to declare your variables
'*
On error goto ds_err
input_File=FREEFILE()
OPEN "My Input File" FOR INPUT ACCESS READ AS # input_file
WHILE NOT EOF(input_file) 
      LINE INPUT #input_file, source_string
      field_count = 1
      WHILE Source_String <> ""
            field_pos = INSTR(source_string,"]")
            SELECT CASE field_Count
                   CASE 1
                        infield1 = MID(LEFT(source_string, field_pos-1),2)
                   CASE 2
                        infield2 = MID(LEFT(source_string, field_pos-1),2)
                   ...
                   ...
                   CASE 8
                        Currency = MID(LEFT(source_string, field_pos-1),2)                        
            END SELECT
            source_string = MID(source_string, field_pos + 1)
            field_count = field_count + 1
      WEND
      '*
      '* At this point the input string has been parsed into seperate fields
      '*
      '* Locate the work sheet for the currency
      '*
      SET current_sheet=WORKSHEETS(currency)
      Find the last occupied line in current_sheet
      Add your data there
      SET current_sheet = NOTHING
...
...
WEND
CLOSE #input_file
'*
ds_exit:
On error goto 0
Exit sub
'*
ds_Error:
SELECT CASE ERR.NUM
       CASE whatever error number worksheet not found is
            Add a new worksheet called (currency) - ie the value in currency
              (Copied from your MASTER sheet)
            RESUME
       CASE ELSE
            ON ERROR GOTO 0
            RESUME
END SELECT

Hope this gives you a few ideas.

regards

Rich

Re: Characters In Cell To Be Re-arranged

Quote from johnccc

Hi,

I'd prefer to use a user defined function. The following is messy but works (I think)

Code

Function sortMe(DataString As String)
Dim FirstNumber(5)
Dim SecondNumber(5)
Dim Counter As Integer
Dim Counter2 As Integer
Dim TextString


For i = 1 To 5
    Let FirstNumber(i) = " "
    Let SecondNumber(i) = " "
Next i


For i = 1 To Len(DataString)
    If Mid(DataString, i, 1) <> "x" Then
        Let FirstNumber(i) = Mid(DataString, i, 1)
        Let Counter = i
    Else
        GoTo LineSkip1
    End If
Next i
LineSkip1:
Counter2 = 0
For i = Counter + 2 To Len(DataString)
    Counter2 = Counter2 + 1
    If IsNumeric(Mid(DataString, i, 1)) = True Then
        Let SecondNumber(Counter2) = Mid(DataString, i, 1)
    Else
        Let TextString = Right(DataString, Len(DataString) - i + 1)
        GoTo LineSkip2
    End If
Next i
LineSkip2:
sortMe = ""
For i = 1 To 5
    Let sortMe = sortMe & FirstNumber(i)
Next i
sortMe = sortMe & "x"
For i = 1 To 5
    Let sortMe = sortMe & SecondNumber(i)
Next i
Let sortMe = sortMe & TextString
End Function

Display More

It's also in the attached.

John

Display More

Hi John,

The problem with that is maintainability. If some one else has to modify the code, or you come back to the code in 6 months time, it can be quite difficult to understand what you have done where.

Using structures, you get almost self documenting code, it's quite obvious what you are doing (Moving some fields to fields of a structure and then back to a string).

This of course would all be wrapped up in a function module for modularisation and information hiding as any other routines using this do not really need to know how this works

Re: Charectors In Cell To Be Re-arranged

Quote from stevekirk

hi

i have attached a sample sheet as it is and how it needs to be.
the information is is a single cell. could you please advise me what macro would do this .

thank you
steve

Hi Steve,

There isn't an existing macro to do it. You could take a deep breath and dive into VBA. The help files are very extensive.

Regards

Rich

Re: Share Workbook While Openning It

Quote from tochybaja

Hi there,

I have a workbook which is used by over 100 people. It is turned on to share mode, but to ensure it would stay like that I need the workbook to check whether it is shared while being opened and then turn to share mode in case this would not be the fact.

Can anyone help?

Thanx

Display More

As long as only one person accesses this at a time you will be all right.

Have a search in the forum for threads that contain the terms file sharing, multi user etc because if you intend to allow simultaneous access to the work book, you're heading for trouble.

Regards

Rich

Re: Charectors In Cell To Be Re-arranged

Quote from stevekirk

hi,

i have data that looks like the following

1x1234Bbc
12x1234Fbc
456x1234Sbc
1234x1234abc
1234x1abc
3456x12abc
1234x123abc
1234x1234abc

i need the data to look like the following

1234x1223sabc
1sssx1ssssabc
12ssx12xxxabc
in other words the x is always the 5th charector
the start of the str to be the 11th charector

spaces must be left from the first number to the x if 4 numbers have not been used. this also applies after the x

s denotes spaces
thank you

steve

Display More

Create a user defined type, such as:

Type Change_Stack                                      '* Change stack
     Changing            As Boolean                    '* Previous changing state
     Update              As Boolean                    '* Previous ScreenUpdating State
End Type
'*
Type Data_Entry                                        '* Data entry details for all sheets
     Previous_Address    As String                     '* Previous Cell visited
     Previous_ForeColour As Integer                    '* Previous font colour
     Previous_FillColour As Integer                    '* Previous fill colour
End Type

You then use this like so:

Dim Change_Stack(10)           As Change_Stack                   '* For nested calls
....
...
Sub Change_Sheet()
    Change_Stack(StackPtr).Changing = g_Changing
    Change_Stack(StackPtr).Update = Application.ScreenUpdating
    StackPtr = StackPtr + 1

And the other way round:

Sub Lock_Sheet()
    StackPtr = StackPtr - 1
    g_Changing = Change_Stack(StackPtr).Changing
    Application.ScreenUpdating = Change_Stack(StackPtr).Update
    If g_Changing = False Then

In your case, break your string into it's component parts.

You don't say what it is, but lets assume it's a material code that determines the warehouse, bin number and what ever.

You would then declare a type:

Type Mat_Num
       Material as string * 4
       S_String as String * 1
      Other_Stuff as String *10
End Type

Then move the parts of the string to the relevant bits of a declared variable, and then move that back to a single string.

Regards

Rich

Re: Sharing One Workbook With Multiusers

Quote from mbluethunder700

Dear Rich

I think that a thousand of Excel program users especially who has a employees and they work on the same file try to make this option .

If i couldn't let the excel file with sharing option so i think there is anther option to let the file used by multiusers

may be share the file between the users but let one user only can edit in his sheet and after he finish the other user can open it ( Like that )

BR

Display More

Hi mbluethunder700

I think you have a slight misunderstanding of file sharing and allowing a file to be used by several users at once.

When you share a file, it's placed in a common directory on the server or perhaps a shared directory on a networked pc.

This allows multiple users to access the file, but only one at a time. The entire file is locked for the duration of the access by a user preventing other users from changing the file.

In a multi-user system access to a file is granted to many users at once, but if a user is changing a record in that file, other users are prevented from changing that one record until the record is released. So the granularity of the locking in a multi-user system is much finer. It is at record level rather than file level.

Excel does not have a record style file structure as such so the only way you can prevent conflicting changes from happening is to use file level locking.

What you are after is a database. And that is what Excel isn't.

You could do they same thing in Access and if you really wanted use Excel as a front end to it. I would be tempted to do it directly in Access.

If you've more than a few users using this then dump access and use an industrial strength database such as Oracle or Microsoft SQL.

Unfortunately the thousands of companies trying to do this need to get someone in their IT department who knows what he's doing or has the guts to tell his boss he is talking daft words, and you'll find that multi-user access to a spreadsheet cannot happen. (And file sharing isn't multi-user access).

Why try and bend Excel into something it most definately isn't ?

Regards

Rich

Re: Finding Next 1 In A List Of 0 And 1

Quote from A9192Shark

Rich,

Thanks for the suggestion. I have solved the problem by telling my boss that it really is not required. This is a reasonable statement at this stage but we will want teh info later.

I will look into the idea of using a string to store the data. I think that I could implement something using this but it will be a peice of code rather than a function.

Your code runs very quickly because you fill the data from a loop, I have to fill it from a worksheet where the numbers are calculated.

Thanks for your help- I will look at using teh string when I solve this one later in teh year!

Cheers,

Alan.

Display More

Hi Alan,

What I'm trying to say is don't always get stuck on looking at data in the way it is presented to you at first.

It may be quicker to write a program to extract the 0's and 1's from the spreadsheet, put them into a string and then call the subroutines I've written rather than trying to do it directly from the spreadsheet.

Whatever you do, don't get it fixed in your head that you must use the data as is. If a bit of re-arranging (not creative mind you!) helps, and it's going to be faster in the long run, go for it!

Regards

Rich

Re: Sharing One Workbook With Multiusers

Listen to those two mbluethunder700. I hope you're not trying to make the workbook available to those different users at the same time ??

Excel is not a multi user system and was never designed to be one. You're working on something that will never work properly.

Re: Error Opening File In Macro

You say that this stops working when you have uploaded the spreadsheet to a server.

The first and most likely question to next ask is when you run the spreadsheet from the server, where is it run, (ie on your machine or the server).

It looks like it may be running on the server in which case the fully qualified file name you are giving it will be the C: drive on the server.

Try changing your file name to it's network version by placing the machine name in front of the path, substituting the drive name. For example \\localhost\my_dir\my_file.txt. Doing that will uniquely identify the file from anywhere on the network.

Another good thing to check with servers and not finding files is access rights.

Regards

Rich

Re: Finding Next 1 In A List Of 0 And 1

Hi A9192Shark

Does this do what you want ?

Option Explicit


Function Build_Big_String() As String
         Dim New_String As String
         Dim R As Integer
         Dim Count As Long
         '*
         For Count = 1 To 48000
             R = Rnd(1)
             If R = 0 Then
                New_String = New_String & "0"
             Else
                New_String = New_String & "1"
             End If
         Next Count
         Build_Big_String = New_String
End Function


Sub Do_String()
    Dim The_String As String
    ReDim Zero_Counts(0) As Integer
    Dim R As Long
    '*
    The_String = Build_Big_String()
    Call Find_Zeros(The_String, Zero_Counts())
    For R = 0 To UBound(Zero_Counts) - 1
        Range("A" & (R + 1)).Value = Zero_Counts(R)
        If R < 650 Then
           Range("B" & (R + 1)).Value = "'" & Mid(The_String, 1 + (50 * R), 50)
        End If
    Next R
End Sub
'*
Sub Find_Zeros(Source As String, Zcount() As Integer)
    '*
    Dim State As String
    Dim Next_One As Long
    Dim Next_Zero As Long
    Dim Source_Len As Long
    Dim Here As Long
    '*
    '* Set the initial state
    '*
    State = Left(Source, 1)
    Here = 1
    Source_Len = Len(Source)
    While Here < Source_Len
          Select Case State
                 Case "0"
                      If Next_Zero <= Here Then
                         Next_Zero = InStr(Here, Source, "1")
                      End If
                      Zcount(UBound(Zcount)) = Next_Zero - Here
                      ReDim Preserve Zcount(UBound(Zcount) + 1)
                 Case "1"
                      '*
                      '* Ignore ones
                      '*
                      Here = InStr(Here, Source, "0") - 1
          End Select
          Here = Here + 1
          State = Mid(Source, Here, 1)
    Wend
End Sub

This takes about 2 seconds for one string, including the output.

Regards

Rich

Re: Finding Next 1 In A List Of 0 And 1

Quote from A9192Shark

Hello All,

I have a list of 48 000 zeros and ones (0 and 1s) (and 12 separate lists).

If there is a 1 then I will return a zero.
If there is a zero then I need to find the next 1 and return the 'distance' to that 1.

For example
1
1
1
1
0
0
0
0
1

The first zero will have to return 4, the second 3 etc.

I have tried using Match(1,[range],0) but this takes for ever to run.
I could write a udf to do this but figure that it would have to be based on a loop. Can anyone think of a clever way of doing this?

In the mean time I will implement the udf.

Thanks,

Alan.

Display More

Ouch.

I would turn the data around so that you have 12 rows rather than 48000, read it directly from the data file (if you can) and try using the string operations such as Instr which can take a start point in the string to start searching from.

Just output the results into Excel.

If it's not a state secret, what are you doing ??

regards

Rich

Re: Workbook Freezes on Opening

PTG258,

I'll agree with Jim. It's very hard to offer suggestions if the problem may be to do with coding when you haven't had sight of the code.

You're not being asked to post the entire project. Paste the relevant procedures. The majority of code being written today does not contain any state secrets, some one, somewhere else has already written it.

If the code accesses proprietary stuff, it's generally through a procedure call so that won't be giving anything away either.

Regards

Rich

Re: Specify the Appropriate Worksheet Code

Quote from getty7

I seriously think I must be in need of a mental examination. This just makes no sense.

You'll find that this happens to all of us. It's called 'Can't see the wood for the trees'. You get so immersed in your code you can't see a blatent error. You're collegue comes over and says:

'Hey... you've forgotten the all important full stops'

And you kick yourself.

What I do when I get to that point is get up and go for a walk. Do something totally different for 15 minutes. And then come back to the problem.

Regards

Rich