Pull Data From an Add-in to a Workbook

H

help_wanted

I have created an add-in containing a macro so that I can distribut
this macro. Some of the data that I need resides in the .xla file. I
it possible to pull data from the .xla. Here is my code as the macr
was saved. Thanks for the support as always.


The bug is below the astriks.

ColWidAlgnTextTest Macro
' Macro recorded 3/31/2004 by Will Day
'

'
Columns("A:A").ColumnWidth = 16
Columns("B:B").ColumnWidth = 30
Columns("C:C").ColumnWidth = 18
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("E2").Select
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1"
Link:=False, _
DisplayAsIcon:=False, Left:=395.25, Top:=12, Width:=96
Height:=27.75 _
).Select


*******************************************
This is where the bug is. It is obviously looking for sheet two of th
.xls i started to create the .xla. How can I point the macro to th
data in the xla.

********************************************
---> Sheets("Sheet2").Select

Range("B1").Select
ActiveCell.FormulaR1C1 = "William Day"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Michael Becker"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Jason Craig"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Saeed Mirza"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Chris Webber"
Range("B6").Select
ActiveCell.FormulaR1C1 = "Matthew Gray"
Range("B7").Select
ActiveCell.FormulaR1C1 = "James Pence"
Range("B8").Select
ActiveCell.FormulaR1C1 = "Chuck Patenaude"
Range("B9").Select
ActiveCell.FormulaR1C1 = "Jimmy Zheng"
Range("B10").Select
ActiveCell.FormulaR1C1 = "Armando Ruiz"
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Range("B2").Select
Sheets("Sheet1").Select
Range("F6").Select
ActiveCell.FormulaR1C1
"=COUNTIF(R[-5]C[-5]:R[294]C[-5],R[-3]C[-5])"
Range("F7").Select
ActiveCell.FormulaR1C1
"=COUNTIF(R[-6]C[-5]:R[293]C[-5],R[-5]C[-1])"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C,R[-2]C)"
Range("G6").Select
Columns("G:G").ColumnWidth = 3.43
Range("H6").Select
ActiveCell.FormulaR1C1 = "HD & CHG Tickets"
Range("H7").Select
ActiveCell.FormulaR1C1 = "TSK Tickets"
Range("H9").Select
ActiveCell.FormulaR1C1 = "Total Tickets Resolved"
Columns("I:I").Select
Columns("H:H").EntireColumn.AutoFit
ActiveSheet.Shapes("ComboBox1").Select
Selection.ShapeRange.ScaleWidth 1.01, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.57, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 1.02, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.05, msoFalse
msoScaleFromTopLeft
End Su
 
J

JF

1-use ThisWorkbook.Sheets("Sheet2")
2-avoid selecting cells or sheets :
with ThisWorkbook.Sheets("Sheet2")
.Range("B1").value = "William Day"
...
...
end with
-----Original Message-----
I have created an add-in containing a macro so that I can distribute
this macro. Some of the data that I need resides in the .xla file. Is
it possible to pull data from the .xla. Here is my code as the macro
was saved. Thanks for the support as always.


The bug is below the astriks.

ColWidAlgnTextTest Macro
' Macro recorded 3/31/2004 by Will Day
'

'
Columns("A:A").ColumnWidth = 16
Columns("B:B").ColumnWidth = 30
Columns("C:C").ColumnWidth = 18
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("E2").Select
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1",
Link:=False, _
DisplayAsIcon:=False, Left:=395.25, Top:=12, Width:=96,
Height:=27.75 _
).Select


*******************************************
This is where the bug is. It is obviously looking for sheet two of the
.xls i started to create the .xla. How can I point the macro to the
data in the xla.

********************************************
---> Sheets("Sheet2").Select

Range("B1").Select
ActiveCell.FormulaR1C1 = "William Day"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Michael Becker"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Jason Craig"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Saeed Mirza"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Chris Webber"
Range("B6").Select
ActiveCell.FormulaR1C1 = "Matthew Gray"
Range("B7").Select
ActiveCell.FormulaR1C1 = "James Pence"
Range("B8").Select
ActiveCell.FormulaR1C1 = "Chuck Patenaude"
Range("B9").Select
ActiveCell.FormulaR1C1 = "Jimmy Zheng"
Range("B10").Select
ActiveCell.FormulaR1C1 = "Armando Ruiz"
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Range("B2").Select
Sheets("Sheet1").Select
Range("F6").Select
ActiveCell.FormulaR1C1 =
"=COUNTIF(R[-5]C[-5]:R[294]C[-5],R[-3]C[-5])"
Range("F7").Select
ActiveCell.FormulaR1C1 =
"=COUNTIF(R[-6]C[-5]:R[293]C[-5],R[-5]C[-1])"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C,R[-2]C)"
Range("G6").Select
Columns("G:G").ColumnWidth = 3.43
Range("H6").Select
ActiveCell.FormulaR1C1 = "HD & CHG Tickets"
Range("H7").Select
ActiveCell.FormulaR1C1 = "TSK Tickets"
Range("H9").Select
ActiveCell.FormulaR1C1 = "Total Tickets Resolved"
Columns("I:I").Select
Columns("H:H").EntireColumn.AutoFit
ActiveSheet.Shapes("ComboBox1").Select
Selection.ShapeRange.ScaleWidth 1.01, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.57, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 1.02, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.05, msoFalse,
msoScaleFromTopLeft
End Sub
Click to expand...
 
P

Pierre Janin

You must use the value property, like this :
Range("B1").Value = "William Day" and never use the Select method.

"help_wanted <" a écrit :
I have created an add-in containing a macro so that I can distribute
this macro. Some of the data that I need resides in the .xla file. Is
it possible to pull data from the .xla. Here is my code as the macro
was saved. Thanks for the support as always.

The bug is below the astriks.

ColWidAlgnTextTest Macro
' Macro recorded 3/31/2004 by Will Day
'

'
Columns("A:A").ColumnWidth = 16
Columns("B:B").ColumnWidth = 30
Columns("C:C").ColumnWidth = 18
Columns("B:B").Select
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlBottom
WrapText = False
Orientation = 0
AddIndent = False
ShrinkToFit = False
MergeCells = False
End With
Range("E2").Select
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1",
Link:=False, _
DisplayAsIcon:=False, Left:=395.25, Top:=12, Width:=96,
Height:=27.75 _
).Select

*******************************************
This is where the bug is. It is obviously looking for sheet two of the
xls i started to create the .xla. How can I point the macro to the
data in the xla.

********************************************
---> Sheets("Sheet2").Select

Range("B1").Select
ActiveCell.FormulaR1C1 = "William Day"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Michael Becker"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Jason Craig"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Saeed Mirza"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Chris Webber"
Range("B6").Select
ActiveCell.FormulaR1C1 = "Matthew Gray"
Range("B7").Select
ActiveCell.FormulaR1C1 = "James Pence"
Range("B8").Select
ActiveCell.FormulaR1C1 = "Chuck Patenaude"
Range("B9").Select
ActiveCell.FormulaR1C1 = "Jimmy Zheng"
Range("B10").Select
ActiveCell.FormulaR1C1 = "Armando Ruiz"
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Range("B2").Select
Sheets("Sheet1").Select
Range("F6").Select
ActiveCell.FormulaR1C1 =
"=COUNTIF(R[-5]C[-5]:R[294]C[-5],R[-3]C[-5])"
Range("F7").Select
ActiveCell.FormulaR1C1 =
"=COUNTIF(R[-6]C[-5]:R[293]C[-5],R[-5]C[-1])"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C,R[-2]C)"
Range("G6").Select
Columns("G:G").ColumnWidth = 3.43
Range("H6").Select
ActiveCell.FormulaR1C1 = "HD & CHG Tickets"
Range("H7").Select
ActiveCell.FormulaR1C1 = "TSK Tickets"
Range("H9").Select
ActiveCell.FormulaR1C1 = "Total Tickets Resolved"
Columns("I:I").Select
Columns("H:H").EntireColumn.AutoFit
ActiveSheet.Shapes("ComboBox1").Select
Selection.ShapeRange.ScaleWidth 1.01, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.57, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 1.02, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.05, msoFalse,
msoScaleFromTopLeft
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Different Results from the Same Macro 2
Create Detail Sheets from Pivot Table 1
Resize commentbox in VB 1
Macro Crashes 1
Copying Data Down the Column But Only Based on What's in Column A 1
macro to copy rows that contain a cell with a certain number 2
Copy and Paste Macro 5
Run Time Error 1004 5

Top