Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate workbook dynamically??
Please forgive me if this has been done before..
Im looking to populate a workbook dynamically from information input into another workbook in the same worksheet. Im looking to do capacity planning for racks in our data centres. I want to get excel to create graphical representations of each rack using information I will put into a workbook. Is this at all possible? Each graphical rep will be 42 cells high to represent the U hieght of the rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start point (of say 26 for this KVM). I want to include other information as well but if this is not possible and a no starter then I will look elsewhere... Can you help? Have I made any sense? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate workbook dynamically??
This works for me
I entered data in to a table like this to feed the macro Name start height kvm 3 4 ups 8 10 IBM 20 45 Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/30/2006 by xxxxxxxx ' ' Dim srow 'data start point Dim slength 'data height Dim count 'data entry row Dim sname 'value to appear in graphic Dim current 'worksheet name current = ActiveSheet.name count = 2 While count < 100 Cells(count, 2).Select 'column 2 contains starting number srow = ActiveCell.Value Cells(count, 3).Select 'column 3 contains height slength = ActiveCell.Value Cells(count, 1).Select 'column 1 contains name to put in graphic sname = Selection.Formula Sheets("Sheet1").Select If (slength 0 And srow 0) Then Range(Cells(srow, 1), Cells(srow + slength, 1)).Select Selection.Formula = sname 'change this to a color change, or border to make more graphical End If count = count + 1 Sheets([current]).Select Wend End Sub "Dave Cleghorn" wrote: Please forgive me if this has been done before.. Im looking to populate a workbook dynamically from information input into another workbook in the same worksheet. Im looking to do capacity planning for racks in our data centres. I want to get excel to create graphical representations of each rack using information I will put into a workbook. Is this at all possible? Each graphical rep will be 42 cells high to represent the U hieght of the rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start point (of say 26 for this KVM). I want to include other information as well but if this is not possible and a no starter then I will look elsewhere... Can you help? Have I made any sense? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate workbook dynamically??
Thanks so much for this. Ive read through it and it makes some sense to me
although I am not very good with VB. I do however, get the following error on "more graphical" line: Compile Error: Sub or Function not defined. Am I doing something wrong? "fugazi48" wrote: This works for me I entered data in to a table like this to feed the macro Name start height kvm 3 4 ups 8 10 IBM 20 45 Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/30/2006 by xxxxxxxx ' ' Dim srow 'data start point Dim slength 'data height Dim count 'data entry row Dim sname 'value to appear in graphic Dim current 'worksheet name current = ActiveSheet.name count = 2 While count < 100 Cells(count, 2).Select 'column 2 contains starting number srow = ActiveCell.Value Cells(count, 3).Select 'column 3 contains height slength = ActiveCell.Value Cells(count, 1).Select 'column 1 contains name to put in graphic sname = Selection.Formula Sheets("Sheet1").Select If (slength 0 And srow 0) Then Range(Cells(srow, 1), Cells(srow + slength, 1)).Select Selection.Formula = sname 'change this to a color change, or border to make more graphical End If count = count + 1 Sheets([current]).Select Wend End Sub "Dave Cleghorn" wrote: Please forgive me if this has been done before.. Im looking to populate a workbook dynamically from information input into another workbook in the same worksheet. Im looking to do capacity planning for racks in our data centres. I want to get excel to create graphical representations of each rack using information I will put into a workbook. Is this at all possible? Each graphical rep will be 42 cells high to represent the U hieght of the rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start point (of say 26 for this KVM). I want to include other information as well but if this is not possible and a no starter then I will look elsewhere... Can you help? Have I made any sense? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate workbook dynamically??
The "more graphical" line actually belongs at the end of
the comment on the previous line - the line has been wrapped at some point in its journey though Usenet. So you can just delete it. (Or move it back to where it belongs.) Dave Cleghorn wrote: Thanks so much for this. Ive read through it and it makes some sense to me although I am not very good with VB. I do however, get the following error on "more graphical" line: Compile Error: Sub or Function not defined. Am I doing something wrong? "fugazi48" wrote: This works for me I entered data in to a table like this to feed the macro Name start height kvm 3 4 ups 8 10 IBM 20 45 Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/30/2006 by xxxxxxxx ' ' Dim srow 'data start point Dim slength 'data height Dim count 'data entry row Dim sname 'value to appear in graphic Dim current 'worksheet name current = ActiveSheet.name count = 2 While count < 100 Cells(count, 2).Select 'column 2 contains starting number srow = ActiveCell.Value Cells(count, 3).Select 'column 3 contains height slength = ActiveCell.Value Cells(count, 1).Select 'column 1 contains name to put in graphic sname = Selection.Formula Sheets("Sheet1").Select If (slength 0 And srow 0) Then Range(Cells(srow, 1), Cells(srow + slength, 1)).Select Selection.Formula = sname 'change this to a color change, or border to make more graphical End If count = count + 1 Sheets([current]).Select Wend End Sub "Dave Cleghorn" wrote: Please forgive me if this has been done before.. Im looking to populate a workbook dynamically from information input into another workbook in the same worksheet. Im looking to do capacity planning for racks in our data centres. I want to get excel to create graphical representations of each rack using information I will put into a workbook. Is this at all possible? Each graphical rep will be 42 cells high to represent the U hieght of the rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start point (of say 26 for this KVM). I want to include other information as well but if this is not possible and a no starter then I will look elsewhere... Can you help? Have I made any sense? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate workbook dynamically??
OK, thanks for that. I am still having trouble running the macro. Im
gettiing a runtime error '13' type mismatch on: Range(Cells(srow, 1), Cells(srow + slength, 1)).Select Should I be altering this macro to fit my data set? What should I be doing to get this working correctly on the data you provided me with fugazi48? "Andrew Taylor" wrote: The "more graphical" line actually belongs at the end of the comment on the previous line - the line has been wrapped at some point in its journey though Usenet. So you can just delete it. (Or move it back to where it belongs.) Dave Cleghorn wrote: Thanks so much for this. Ive read through it and it makes some sense to me although I am not very good with VB. I do however, get the following error on "more graphical" line: Compile Error: Sub or Function not defined. Am I doing something wrong? "fugazi48" wrote: This works for me I entered data in to a table like this to feed the macro Name start height kvm 3 4 ups 8 10 IBM 20 45 Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/30/2006 by xxxxxxxx ' ' Dim srow 'data start point Dim slength 'data height Dim count 'data entry row Dim sname 'value to appear in graphic Dim current 'worksheet name current = ActiveSheet.name count = 2 While count < 100 Cells(count, 2).Select 'column 2 contains starting number srow = ActiveCell.Value Cells(count, 3).Select 'column 3 contains height slength = ActiveCell.Value Cells(count, 1).Select 'column 1 contains name to put in graphic sname = Selection.Formula Sheets("Sheet1").Select If (slength 0 And srow 0) Then Range(Cells(srow, 1), Cells(srow + slength, 1)).Select Selection.Formula = sname 'change this to a color change, or border to make more graphical End If count = count + 1 Sheets([current]).Select Wend End Sub "Dave Cleghorn" wrote: Please forgive me if this has been done before.. Im looking to populate a workbook dynamically from information input into another workbook in the same worksheet. Im looking to do capacity planning for racks in our data centres. I want to get excel to create graphical representations of each rack using information I will put into a workbook. Is this at all possible? Each graphical rep will be 42 cells high to represent the U hieght of the rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start point (of say 26 for this KVM). I want to include other information as well but if this is not possible and a no starter then I will look elsewhere... Can you help? Have I made any sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto populate unused rows from workbook to workbook | Excel Worksheet Functions | |||
Dynamically populate a Date pulldown menu? | Excel Discussion (Misc queries) | |||
Dynamically Reference Offline Workbook/worksheet | Excel Discussion (Misc queries) | |||
run vba code dynamically of another add-in workbook | Excel Programming | |||
populate a different workbook | Excel Programming |