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?

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?

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?

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?

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?