G
Guest
Hi all,
I know it is holiday time, not many ppl working right now, but if you are
around, please help me
I wish you all have a wonderful Xmas!!!
My problems are:
(1) After I recorded a macro "TextConvert", I save the macro name
"TextConvert" as Microsoft Excel add-in then I went book1 to create a icon
by do like this: "View -> Toolbars -> Customize -> Commands - > Macro -> drag
the icon to Standard toolbar, name it "Convert text to Excel" -> assign
Macro, I chose "TextConvert" macro. It show up in the standard toolbar, I
click "Convert text to Excel" then work. But when I close out the whole
excel application then open new excel application. I go to Tools -> Add-Ins,
check the add-in "Text Convert". However, when I click "Convert text to
Excel" it doesn't work, said like "Book1.xls could not be found. Check for
spelling..." But I open Visual Basic editor, I saw the code still there in
"VBAProject (TextConvert.xla) under module1.
Can any1 tell me how do I fix this problem?
My macro is like this
Sub ConvertText()
'
' ConvertText Macro
' Macro recorded 12/23/2005 by A.Q
'
Dim fName As Variant
fName = Application.GetOpenFileName()
If fName = False Then
MsgBox "No File was selected, the Macro will now end"
Else
Application.Workbooks.OpenText FileName:=fName, Origin _
:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), _
Array(3, 2), Array(43, 2), Array(45, 2), Array(55, 3), Array(64, 3),
Array(73, 2), Array(84 _
, 2), Array(91, 2), Array(98, 2)), TrailingMinusNumbers:=True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "State"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Client"
Selection.Font.Bold = True
End If
End Sub
(2) I have this code from my manager as a class file,( I modified the
caption name) he said use this, but I run into error with
"ThereIsCommandBar". How do I use this and where should I paste this code
too? (Module, or ThisWorkBook?)
Option Explicit
'This event procedure doesn't run in the Add-In version
'In the workbook version, the menu is
'created and destroyed each time the Book
'is Activated or Deactivated
Private Sub Workbook_Activate()
MakeMenu "Workbook"
End Sub
Private Sub Workbook_AddinInstall()
MakeMenu "Add_In"
End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Convert Data").Delete
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("Convert Data").Delete
End Sub
'Parameter use is used in lieu of Tag to identify
'this menu control; when the add-in is uninstalled
'this value is needed to find the correct control
'to remove from the menus
'(Tag is not remembered by Excel for permanently installed menus controls)
Private Sub MakeMenu(InstallType As String)
Dim cbar As CommandBar
Dim cBarP As CommandBar
Dim cBut As CommandBarButton
Dim cPop As CommandBarPopup
Dim cComb As CommandBarComboBox
Dim cCon As CommandBarControl
Dim cPopGetData As CommandBarPopup
If ThereIsCommandBar("Convert Data") Then Exit Sub
Set cbar = Application.CommandBars.Add("Convert Data", , , False)
cbar.Visible = True
' Set cbar = Application.CommandBars("Worksheet Menu Bar")
If InstallType = "Add_In" Then
' For Each cCon In cbar.Controls
' If cCon.Parameter = "Add_In" Then cCon.Delete
' Next cCon
'Make add-in menu Permanent
'Make it a Popup menu so that it can have a Submenu
Set cPop = cbar.Controls.Add( _
Type:=msoControlPopup, _
Before:=1, _
Temporary:=False)
With cPop
.Caption = "Convert Data"
.Parameter = "Add_In"
End With
ElseIf InstallType = "Workbook" Then
' For Each cCon In cbar.Controls
' If cCon.Parameter = "Workbook" Then cCon.Delete
' Next cCon
'Make Workbook menu Temporary
Set cPop = cbar.Controls.Add( _
Type:=msoControlPopup, _
Before:=1, _
Temporary:=True)
With cPop
'Put t in caption to distinguish
'from Add-In in case both are installed
.Caption = "Convert Data(T)"
.Parameter = "Workbook"
End With
End If
'Get a Reference to the Popup CommandBar
'and add buttons
Set cBarP = cPop.CommandBar
With cBarP
Set cBut = .Controls.Add
With cBut
.Caption = "Excel Navigater..."
.Style = msoButtonCaption
.OnAction = "Show_ufNavigate"
End With
I know it is holiday time, not many ppl working right now, but if you are
around, please help me
I wish you all have a wonderful Xmas!!!
My problems are:
(1) After I recorded a macro "TextConvert", I save the macro name
"TextConvert" as Microsoft Excel add-in then I went book1 to create a icon
by do like this: "View -> Toolbars -> Customize -> Commands - > Macro -> drag
the icon to Standard toolbar, name it "Convert text to Excel" -> assign
Macro, I chose "TextConvert" macro. It show up in the standard toolbar, I
click "Convert text to Excel" then work. But when I close out the whole
excel application then open new excel application. I go to Tools -> Add-Ins,
check the add-in "Text Convert". However, when I click "Convert text to
Excel" it doesn't work, said like "Book1.xls could not be found. Check for
spelling..." But I open Visual Basic editor, I saw the code still there in
"VBAProject (TextConvert.xla) under module1.
Can any1 tell me how do I fix this problem?
My macro is like this
Sub ConvertText()
'
' ConvertText Macro
' Macro recorded 12/23/2005 by A.Q
'
Dim fName As Variant
fName = Application.GetOpenFileName()
If fName = False Then
MsgBox "No File was selected, the Macro will now end"
Else
Application.Workbooks.OpenText FileName:=fName, Origin _
:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), _
Array(3, 2), Array(43, 2), Array(45, 2), Array(55, 3), Array(64, 3),
Array(73, 2), Array(84 _
, 2), Array(91, 2), Array(98, 2)), TrailingMinusNumbers:=True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "State"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Client"
Selection.Font.Bold = True
End If
End Sub
(2) I have this code from my manager as a class file,( I modified the
caption name) he said use this, but I run into error with
"ThereIsCommandBar". How do I use this and where should I paste this code
too? (Module, or ThisWorkBook?)
Option Explicit
'This event procedure doesn't run in the Add-In version
'In the workbook version, the menu is
'created and destroyed each time the Book
'is Activated or Deactivated
Private Sub Workbook_Activate()
MakeMenu "Workbook"
End Sub
Private Sub Workbook_AddinInstall()
MakeMenu "Add_In"
End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Convert Data").Delete
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("Convert Data").Delete
End Sub
'Parameter use is used in lieu of Tag to identify
'this menu control; when the add-in is uninstalled
'this value is needed to find the correct control
'to remove from the menus
'(Tag is not remembered by Excel for permanently installed menus controls)
Private Sub MakeMenu(InstallType As String)
Dim cbar As CommandBar
Dim cBarP As CommandBar
Dim cBut As CommandBarButton
Dim cPop As CommandBarPopup
Dim cComb As CommandBarComboBox
Dim cCon As CommandBarControl
Dim cPopGetData As CommandBarPopup
If ThereIsCommandBar("Convert Data") Then Exit Sub
Set cbar = Application.CommandBars.Add("Convert Data", , , False)
cbar.Visible = True
' Set cbar = Application.CommandBars("Worksheet Menu Bar")
If InstallType = "Add_In" Then
' For Each cCon In cbar.Controls
' If cCon.Parameter = "Add_In" Then cCon.Delete
' Next cCon
'Make add-in menu Permanent
'Make it a Popup menu so that it can have a Submenu
Set cPop = cbar.Controls.Add( _
Type:=msoControlPopup, _
Before:=1, _
Temporary:=False)
With cPop
.Caption = "Convert Data"
.Parameter = "Add_In"
End With
ElseIf InstallType = "Workbook" Then
' For Each cCon In cbar.Controls
' If cCon.Parameter = "Workbook" Then cCon.Delete
' Next cCon
'Make Workbook menu Temporary
Set cPop = cbar.Controls.Add( _
Type:=msoControlPopup, _
Before:=1, _
Temporary:=True)
With cPop
'Put t in caption to distinguish
'from Add-In in case both are installed
.Caption = "Convert Data(T)"
.Parameter = "Workbook"
End With
End If
'Get a Reference to the Popup CommandBar
'and add buttons
Set cBarP = cPop.CommandBar
With cBarP
Set cBut = .Controls.Add
With cBut
.Caption = "Excel Navigater..."
.Style = msoButtonCaption
.OnAction = "Show_ufNavigate"
End With