Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA:Populating Cells/List From External Data
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 (D1:D11) - ALSO, the "Club" will populate another cell AND the "Cost" another cell, i.e. Cells D1:D11 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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA:Populating Cells/List From External Data
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) " wrote: 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 (D1:D11) - ALSO, the "Club" will populate another cell AND the "Cost" another cell, i.e. Cells D1:D11 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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA:Populating Cells/List From External Data
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA:Populating Cells/List From External Data
Is the external source a file?
" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA:Populating Cells/List From External Data
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 -- Gary''s Student - gsnu2007g " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation list populating multiple cells | Excel Discussion (Misc queries) | |||
Excel Macro for taking data external data and populating it on a sheet and deleting unwanted data | Excel Programming | |||
Populating dropdown list 2 with data depending upon what was selected in list 1 | Excel Programming | |||
Populating data from an external database | Excel Programming | |||
Populating data from an external database | Excel Programming |