split a code

[Prav]

hi there, i'm new to vb programming. i'm using vb macro in excel to split this code 15110-ACN-01 which is in a cell in my wooksheet. therefore when i press a command button then code have to be seperated as follows
15 = Area
151 = subarea
15110 = system
ACN = air con
01 = sequential number.

how you guys know to do that, please.
thank you

[si_the_geek]

You can use the functions Left, Mid, and Right.

Is your code always the same length& format? If so, you could use code like this:

Area = Left(yourstring,2)
subarea = Left(yourstring,3)
system = Left(yourstring,5)
aircon = Mid(yourstring,7,3)
sequential = Right(yourstring,2)

If the format or length changes, you should use Instr to find the - markers.

[Prav]

i'm new to vb and excel macro, how can i put these code between
Private Sub CommandButton1_Click()
-- program--
End Sub

and codes is always be from Col A and starts from row 2. thank you for your help

[si_the_geek]

Ok, this is how you would read just cell A2:

Private Sub CommandButton1_Click()
 
Dim yourstring as String
Dim Area, subarea, system, aircon, sequential
  yourstring = WorkSheets("[B]sheetname[/B]").Range("A2")
  Area = Left(yourstring,2)
  subarea = Left(yourstring,3)
  system = Left(yourstring,5)
  aircon = Mid(yourstring,7,3)
  sequential = Right(yourstring,2)
 
  '... do something with the variables here
 
End Sub

And this will read all cells from A2 downwards (up to the first empty cell), and will be ok if they all contain text formatted as in your example:

Private Sub CommandButton1_Click()
 
Dim iRowNum as Integer
Dim yourstring as String
Dim Area, subarea, system, aircon, sequential
  iRowNum = 2
  Do
    yourstring = WorkSheets("[B]sheetname[/B]").Cells (iRowNum,1)
    If yourstring <> "" Then
      Area = Left(yourstring,2)
      subarea = Left(yourstring,3)
      system = Left(yourstring,5)
      aircon = Mid(yourstring,7,3)
      sequential = Right(yourstring,2)
 
      '... do something with the variables here
 
    End If
    iRowNum = iRowNum + 1
  Loop While yourstring <> ""
 
End Sub

[Prav]

thx for that. i need to load the variable to the database using a loader. the data in the loader should be like as follows

..Area|15
..SubArea|151
..System|15110
..Equiment|Air Con
..SeqNumber|01

..Area|16
..SubArea|161
..System|16110
..Equiment|Air filter
..SeqNumber|02

and so on

[Prav]

this is the code i was given and how to merge them to your code.


Private Sub CommandButton1_Click()
'
' Export Macro
' Export Piping Codes
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Dim FileName, Code, Service, DTime As String
Dim irow, icol As Integer
Dim ifh As Long

DTime = CStr(Now())
DTime = Replace(DTime, "/", "-", 1, -1, vbTextCompare)
DTime = Replace(DTime, ":", "-", 1, -1, vbTextCompare)
DTime = Replace(DTime, " ", "-", 1, -1, vbTextCompare)

ifh = FreeFile
FileName = ActiveWorkbook.Path & "\Export Piping Codes " & DTime & ".txt"
Open FileName For Output As ifh

irow = 3
With ActiveSheet
On Error Resume Next
While .Cells(irow, 2) <> ""
icol = 2
While .Cells(2, icol) <> ""
Code = .Cells(irow, 2)
Service = .Cells(irow, 3)

Print #ifh, ""
Print #ifh, "----------------------"
Print #ifh, "Export Equiments Codes"
Print #ifh, "----------------------"
Print #ifh, ""
Print #ifh, ".TagType"
Print #ifh, "..NAME|" & Code
Print #ifh, "..DESCRIPTION|" & Service
Print #ifh, "..CLASSIFICATION|TagType"
Print #ifh, "..LCSTATE|PipeRDLClass"
Print #ifh, "..ATTRCLASSNAME|"
Print #ifh, ""
Print #ifh, "-----RELATIONSHIP-----"
Print #ifh, ""
Print #ifh, ".TagTypeHasTagType"
Print #ifh, "..LNAME|Piping"
Print #ifh, "..RNAME|" & Code
Print #ifh, ""
Print #ifh, "-------- END-----------"
icol = icol + 1
Wend
irow = irow + 1
Wend
End With
Close ifh
End Sub

[Prav]

well i managed to merge both and getting the required result. however i would like to know how to implement 'Instr' you mentioned earlier if i have the format or length changes. thank you

[si_the_geek]

Good stuff

Instr returns the position of one string within another, so if you provide it with the following parameters:

x = InStr(1, "test-test","-")

..you are asking it to find the position of - within test-test, which is 5, and this value is stored in the variable x. (the "1," is optional, this is where to start looking - the first character in this case)

As an example, to find the "ACN" in your example we could do this:

Dim firstdashpos as Integer
Dim seconddashpos as Integer
  firstdashpos = Instr(yourstring,"-")
    'for the second dash, start looking at the character after the first one
  seconddashpos = Instr(firstdashpos+1, yourstring,"-")  
  aircon = Mid(yourstring, firstdashpos, seconddashpos - firstdashpos - 1)

However, if it is available (I think it depends on your version of Office) then Split is a nicer alternative, this converts a string into an array, putting into each item a piece of text between the separator you specify. eg:

Dim myArray as Variant  '(split needs the Variant data type)
  myArray = Split(yourstring,"-")

If yourstring is the example again, myArray now contains the following:
myArray(0) = "15110"
myArray(1) = "ACN"
myArray(2) = "01"

So the code to put that into variables can now be:

Area = Left(myArray(0),2)
subarea = Left(myArray(0),3)
system = myArray(0)
aircon = myArray(1)
sequential = myArray(2)