Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Multiple Excel worksheets: unknown identifier & ExcelSheet
#1
Hi, all,

I would like to copy several columns of figures from another program into a worksheet in Excel, then I would like to copy a specific column from this data into another worksheet. However, running the macro yields an 'unknown identifier.' error message and it highlights the first occurrence of 'ExcelSheet' in the file. I then tried running the sample 'Easier Excel functions' and I also get that message ......

My code is below, and this is with Excel 2010 and Windows 7. When it runs, it stops at "ExcelSheet es.Init("Buffer" 1)". Thanks for your time & any insight you can give.


act results
'Ca
'Cc
int excel=act(win("Excel"))
ExcelSheet es.Init("Buffer" 1)
'CH
'Cv
ExcelSheet bufferDonor.Init
bufferDonor.ws.Range("C1:C200").Select
'Cc
ExcelSheet es2.Init("Data" 1)
ExcelSheet dataDonor.Init
dataDonor.ws.Range("I2:I201").Select
'Cv
#2
Installing newer Quick Macros version should help.
#3
I am currently running 2.3.2.8, which I think is the most current version ...
#4
Click "Check extensions" button in Options -> General. What it says?
#5
"No problems have been detected."
#6
What is its color? ExcelSheet or ExcelSheet?

Open function Classes2 (System\Declarations\Classes2). Are all the names purple or black? Also look in function Classes.
#7
ExcelSheet is black in my macro.

A screenshot of Classes2 is attached.

   
#8
Run this macro. What it says?

Code:
Copy      Help
typelib Excel {00020813-0000-0000-C000-000000000046} 1.2 0 1
class ExcelSheet Excel.Worksheet'ws
#9
I am not getting any error messages, but now ExcelSheet has become purple.
#10
Now your macro should work.
Create function init2 and add these two lines. It is a workaround. Later I'll try to find the reason why it did not work.
#11
Sorry to reanimate this thread. I tried running the the code

Code:
Copy      Help
typelib Excel {00020813-0000-0000-C000-000000000046} 1.2 0 1
class ExcelSheet Excel.Worksheet'ws

I get "Error in Macro: cannot load type library."

Please help.
#12
Is Excel installed? What version? What is its program path?
#13
LOL!! I'm SO sorry!

I have kingsoft spreadsheet (Excel alternative). I totally forgot because I also own Excel but it's not on this computer.

:lol:
#14
Dear Gintaras
I have the same problem. I followed the steps that you specified. The macro says for me that:
"cannot load type library"

I have upgrade my OS to Windows 10.
I have Excel 2010.
for your kind help
Regards
#15
Look in menu -> Tools -> COM Libraries. Search for Excel. Can you find "Microsoft Excel 11.0 Object Library" or similar (perhaps the version number is bigger)?
#16
yes. you are right.
I found "Microsoft Excel 14.0 Object Library" version 1.7

kindly advice what is the next step.

Regards
#17
Create new macro. In the COM Libraries dialog select that Excel library and click "Insert declaration from list". It inserts a typelib line in the macro. Run the macro. Does it show error? If no, try to run your Excel macro now. If works, move the typelib line to function named init2. If function init2 does not exist - create. Restart QM.

the typelib must be named Excel. If other name, change to Excel.

typelib Excel {..............} ....
#18
after I run the line of macro
Code:
Copy      Help
typelib Excel {00020813-0000-0000-C000-000000000046} 1.7

it highlight Excel word and give me this message:

Error in Macro1: this name already exists.
#19
Try to replace file System.qml in QM folder with the attached System.qml file. I disabled the Excel typelib declaration in it.


Attached Files
.zip   System.zip (Size: 626.37 KB / Downloads: 340)
#20
after I replaced the file, I tested the macro.
it still highlighted the "ExcelSheet es.Init"
and give this message

Warning in AddNewEmployee: private System function __err_Silent in user code. It may be deleted in the future?
Error in AddNewEmployee: unknown identifier.
#21
Macro Macro2865
Code:
Copy      Help
typelib Excel {00020813-0000-0000-C000-000000000046} 1.7

ExcelSheet es.Init

If error, it should be in the first line. Is Excel colored or black after you run this macro?
#22
this is the error message
Error (RT) in AddNewEmployee: 0x8002801D, Library not registered.

typelib Excel {00020813-0000-0000-C000-000000000046} 1.7
ExcelSheet es.Init

with dotted red line under es.Init

I inserted this line
typelib Excel {00020813-0000-0000-C000-000000000046} 1.7
in my macro just above ExcelSheet es.Init.
color of Excel word is grayed out.
#23
When you select the Excel library in the COM Libraries dialog,
1. What is in the yellow field?
2. What file path is below, in the combo box? For example on my PC it is C:\Program Files (x86)\Microsoft Office\OFFICE11\EXCEL.EXE.
#24
1. in the yellow field I have this:

Excel

--- Coclasses ---
QueryTable
Application
Chart
Worksheet
Global
Workbook
OLEObject

--- Interfaces ---
Adjustments
CalloutFormat
ColorFormat
LineFormat
ShapeNode
ShapeNodes
PictureFormat
ShadowFormat
TextEffectFormat
ThreeDFormat
FillFormat
DiagramNodes
DiagramNodeChildren
DiagramNode
IRTDUpdateEvent
IRtdServer
TextFrame2
IFont
IWindow
IWindows
IAppEvents
_Application
IWorksheetFunction
IRange
IChartEvents
_Chart
Sheets
IVPageBreak
IHPageBreak
IHPageBreaks
IVPageBreaks
IRecentFile
IRecentFiles
IDocEvents
_Worksheet
IStyle
IStyles
IBorders
_Global
IAddIn
IAddIns
IToolbar
IToolbars
IToolbarButton
IToolbarButtons
IAreas
IWorkbookEvents
_Workbook
Workbooks
IMenuBars
IMenuBar
IMenus
IMenu
IMenuItems
IMenuItem
ICharts
IDrawingObjects
IPivotCache
IPivotCaches
IPivotFormula
IPivotFormulas
IPivotTable
IPivotTables
IPivotField
IPivotFields
ICalculatedFields
IPivotItem
IPivotItems
ICalculatedItems
ICharacters
IDialogs
IDialog
ISoundNote
IButton
IButtons
ICheckBox
ICheckBoxes
IOptionButton
IOptionButtons
IEditBox
IEditBoxes
IScrollBar
IScrollBars
IListBox
IListBoxes
IGroupBox
IGroupBoxes
IDropDown
IDropDowns
ISpinner
ISpinners
IDialogFrame
ILabel
ILabels
IPanes
IPane
IScenarios
IScenario
IGroupObject
IGroupObjects
ILine
ILines
IRectangle
IRectangles
IOval
IOvals
IArc
IArcs
IOLEObjectEvents
_IOLEObject
IOLEObjects
ITextBox
ITextBoxes
IPicture
IPictures
IDrawing
IDrawings
IRoutingSlip
IOutline
IModule
IModules
IDialogSheet
IDialogSheets
IWorksheets
IPageSetup
INames
IName
IChartObject
IChartObjects
IMailer
ICustomViews
ICustomView
IFormatConditions
IFormatCondition
IComments
IComment
IRefreshEvents
_IQueryTable
IQueryTables
IParameter
IParameters
IODBCError
IODBCErrors
IValidation
IHyperlinks
IHyperlink
IAutoFilter
IFilters
IFilter
IAutoCorrect
IBorder
IInterior
IChartFillFormat
IChartColorFormat
IAxis
IChartTitle
IAxisTitle
IChartGroup
IChartGroups
IAxes
IPoints
IPoint
ISeries
ISeriesCollection
IDataLabel
IDataLabels
ILegendEntry
ILegendEntries
ILegendKey
ITrendlines
ITrendline
ICorners
ISeriesLines
IHiLoLines
IGridlines
IDropLines
ILeaderLines
IUpBars
IDownBars
IFloor
IWalls
ITickLabels
IPlotArea
IChartArea
ILegend
IErrorBars
IDataTable
IPhonetic
IShape
IShapes
IShapeRange
IGroupShapes
ITextFrame
IConnectorFormat
IFreeformBuilder
IControlFormat
IOLEFormat
ILinkFormat
IPublishObjects
PublishObject
IOLEDBError
IOLEDBErrors
IPhonetics
DefaultWebOptions
WebOptions
IPivotLayout
TreeviewControl
CubeField
CubeFields
IDisplayUnitLabel
ICellFormat
IUsedObjects
ICustomProperties
ICustomProperty
ICalculatedMembers
ICalculatedMember
IWatches
IWatch
IPivotCell
IGraphic
IAutoRecover
IErrorCheckingOptions
IErrors
IError
ISmartTagAction
ISmartTagActions
ISmartTag
ISmartTags
ISmartTagRecognizer
ISmartTagRecognizers
ISmartTagOptions
ISpellingOptions
ISpeech
IProtection
IPivotItemList
ITab
IAllowEditRanges
IAllowEditRange
IUserAccessList
IUserAccess
IRTD
IDiagram
IListObjects
IListObject
IListColumns
IListColumn
IListRows
IListRow
IXmlNamespace
IXmlNamespaces
IXmlDataBinding
IXmlSchema
IXmlSchemas
IXmlMap
IXmlMaps
IListDataFormat
IXPath
IPivotLineCells
IPivotLine
IPivotLines
IPivotAxis
IPivotFilter
IPivotFilters
IWorkbookConnection
IConnections
IWorksheetView
IChartView
IModuleView
IDialogSheetView
ISheetViews
IOLEDBConnection
IODBCConnection
IAction
IActions
IFormatColor
IConditionValue
IColorScale
IColorScaleCriteria
IColorScaleCriterion
IDatabar
IIconSetCondition
IIconCriteria
IIconCriterion
IIcon
IIconSet
IIconSets
ITop10
IAboveAverage
IUniqueValues
IRanges
IHeaderFooter
IPage
IPages
IServerViewableItems
ITableStyleElement
ITableStyleElements
ITableStyle
ITableStyles
ISortField
ISortFields
ISort
IResearch
IColorStop
IColorStops
ILinearGradient
IRectangularGradient
IMultiThreadedCalculation
IChartFormat
IFileExportConverter
IFileExportConverters
IAddIns2
ISparklineGroups
ISparklineGroup
ISparkPoints
ISparkline
ISparkAxes
ISparkHorizontalAxis
ISparkVerticalAxis
ISparkColor
IDataBarBorder
INegativeBarFormat
IValueChange
IPivotTableChangeList
IDisplayFormat
ISlicerCaches
ISlicerCache
ISlicerCacheLevels
ISlicerCacheLevel
ISlicers
ISlicer
ISlicerItem
ISlicerItems
ISlicerPivotTables
IProtectedViewWindows
IProtectedViewWindow
Font
Window
Windows
AppEvents
WorksheetFunction
Range
ChartEvents
VPageBreak
HPageBreak
HPageBreaks
VPageBreaks
RecentFile
RecentFiles
DocEvents
Style
Styles
Borders
AddIn
AddIns
Toolbar
Toolbars
ToolbarButton
ToolbarButtons
Areas
WorkbookEvents
MenuBars
MenuBar
Menus
Menu
MenuItems
MenuItem
Charts
DrawingObjects
PivotCache
PivotCaches
PivotFormula
PivotFormulas
PivotTable
PivotTables
PivotField
PivotFields
CalculatedFields
PivotItem
PivotItems
CalculatedItems
Characters
Dialogs
Dialog
SoundNote
Button
Buttons
CheckBox
CheckBoxes
OptionButton
OptionButtons
EditBox
EditBoxes
ScrollBar
ScrollBars
ListBox
ListBoxes
GroupBox
GroupBoxes
DropDown
DropDowns
Spinner
Spinners
DialogFrame
Label
Labels
Panes
Pane
Scenarios
Scenario
GroupObject
GroupObjects
Line
Lines
Rectangle
Rectangles
Oval
Ovals
Arc
Arcs
OLEObjectEvents
_OLEObject
OLEObjects
TextBox
TextBoxes
Picture
Pictures
Drawing
Drawings
RoutingSlip
Outline
Module
Modules
DialogSheet
DialogSheets
Worksheets
PageSetup
Names
Name
ChartObject
ChartObjects
Mailer
CustomViews
CustomView
FormatConditions
FormatCondition
Comments
Comment
RefreshEvents
_QueryTable
QueryTables
Parameter
Parameters
ODBCError
ODBCErrors
Validation
Hyperlinks
Hyperlink
AutoFilter
Filters
Filter
AutoCorrect
Border
Interior
ChartFillFormat
ChartColorFormat
Axis
ChartTitle
AxisTitle
ChartGroup
ChartGroups
Axes
Points
Point
Series
SeriesCollection
DataLabel
DataLabels
LegendEntry
LegendEntries
LegendKey
Trendlines
Trendline
Corners
SeriesLines
HiLoLines
Gridlines
DropLines
LeaderLines
UpBars
DownBars
Floor
Walls
TickLabels
PlotArea
ChartArea
Legend
ErrorBars
DataTable
Phonetic
Shape
Shapes
ShapeRange
GroupShapes
TextFrame
ConnectorFormat
FreeformBuilder
ControlFormat
OLEFormat
LinkFormat
PublishObjects
OLEDBError
OLEDBErrors
Phonetics
PivotLayout
DisplayUnitLabel
CellFormat
UsedObjects
CustomProperties
CustomProperty
CalculatedMembers
CalculatedMember
Watches
Watch
PivotCell
Graphic
AutoRecover
ErrorCheckingOptions
Errors
Error
SmartTagAction
SmartTagActions
SmartTag
SmartTags
SmartTagRecognizer
SmartTagRecognizers
SmartTagOptions
SpellingOptions
Speech
Protection
PivotItemList
Tab
AllowEditRanges
AllowEditRange
UserAccessList
UserAccess
RTD
Diagram
ListObjects
ListObject
ListColumns
ListColumn
ListRows
ListRow
XmlNamespace
XmlNamespaces
XmlDataBinding
XmlSchema
XmlSchemas
XmlMap
XmlMaps
ListDataFormat
XPath
PivotLineCells
PivotLine
PivotLines
PivotAxis
PivotFilter
PivotFilters
WorkbookConnection
Connections
WorksheetView
ChartView
ModuleView
DialogSheetView
SheetViews
OLEDBConnection
ODBCConnection
Action
Actions
FormatColor
ConditionValue
ColorScale
ColorScaleCriteria
ColorScaleCriterion
Databar
IconSetCondition
IconCriteria
IconCriterion
Icon
IconSet
IconSets
Top10
AboveAverage
UniqueValues
Ranges
HeaderFooter
Page
Pages
ServerViewableItems
TableStyleElement
TableStyleElements
TableStyle
TableStyles
SortField
SortFields
Sort
Research
ColorStop
ColorStops
LinearGradient
RectangularGradient
MultiThreadedCalculation
ChartFormat
FileExportConverter
FileExportConverters
AddIns2
SparklineGroups
SparklineGroup
SparkPoints
Sparkline
SparkAxes
SparkHorizontalAxis
SparkVerticalAxis
SparkColor
DataBarBorder
NegativeBarFormat
ValueChange
PivotTableChangeList
DisplayFormat
SlicerCaches
SlicerCache
SlicerCacheLevels
SlicerCacheLevel
Slicers
Slicer
SlicerItem
SlicerItems
SlicerPivotTables
ProtectedViewWindows
ProtectedViewWindow
IDummy
ICanvasShapes

2. this is the file path
C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
#25
Maybe this can help to find the reason:

Macro Macro2865
Code:
Copy      Help
typelib Excel {00020813-0000-0000-C000-000000000046} 1.7
deb
ExcelSheet es.Init

Run this macro. It runs in steps. Press F6 F6 F6 ... until it fails. What is the line where it fails? Or the last executed line before it fails? And in which function.
#26
I runed the macro and here is what I got.
Kindly refer to the images below.

[Image: quickMacros00.jpg]
[Image: quickMacros01.jpg]
#27
It is a cleanup code, executed after fails. Should fail somewhere before, probably in ExcelSheet.Init or in some other function called by ExcelSheet.Init. What is the last executed line in ExcelSheet.Init?
#28
here you can see the last line executed in ExceSheet.Init

[Image: Quci_Macros03.jpg]

then it will jump to another function called "__VtReal"
[Image: Quci_Macros04.jpg]
#29
Not this. Need the last executed line before the error is printed. Or before jumps somewhere to a cleanup code. If you press F6 F6 again, __VtReal returns...
#30
I got it now.
here is the sequence of execution
[Image: QuickMacros01.jpg]
[Image: QuickMacros02.jpg]
[Image: QuickMacros03.jpg]
[Image: QuickMacros04.jpg]


Forum Jump:


Users browsing this thread: 1 Guest(s)