VBA:Populating Cells/List From External Data

[mark1.thompson45]

Hi,
can anyone help, I have a background in Perl, JScript etc but am
new to Excel/VBA. I need some hints or prefferably code that will
allow me to perform the following on an Excel worksheet.

I am trying to populate several "cells" from data which is external to
the Excel application, the data is in the format:

Player#Club#Cost

i.e. Ronaldo#Manchester United#8.0

I want the "Player" part of the data to be read into list form when a
cell is selected, if one of the players from the list is selected then
that player will populate that cell (D111) - ALSO, the "Club" will
populate another cell AND the "Cost" another cell, i.e.

Cells D111 will be the PALYERS
Cells E1:E11 will be the CLUBS
Cells F1:F11 will be the COSTS

If I could push my luck a little further then I would also like a bit
of data checking to be performed at the same time - I can only allow 2
players from the same club to be selected, anymore is not allowed - so
if cells E1:E11 contain MORE THAN 2 of the same string value then I
need a warning, maybe if the cells containing the cinflicting data
could FLASH in a red colour.

thanks for any help, Mark...

 

[Joel]

mystring = "Ronaldo#Manchester United#8.0"
first_Item = Left(mystring, InStr(mystring, "#") - 1)
second_item = Mid(mystring, InStr(mystring, "#") + 1)
third_Item = Mid(second_item, InStr(second_item, "#") + 1)
second_item = Left(second_item, InStr(second_item, "#") - 1)

 

[mark1.thompson45]

Ok, so that's how to split the string - but I still need help on:-

a). when a cell is selected, reading the data into a list from the
external source.
b). populating the 3 different cells, player, club, cost.
c). checking for existance of more than 2 clubs.

cheers.

 

[Joel]

Is the external source a file?

 

[Gary''s Student]

I created a file on the Desktop called:

data.mark1

I imported the data manually with the Recorder turned on and got:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/12/2008 by James Ravenswood
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Owner\Desktop\data.mark1",
Destination:=Range _
("D1"))
.Name = "data.mark1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "#"
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub