Re-directing where to look for Office library reference paths

[rummaging]

I purchased Visual Studio 6 Pro Edition.
I installed it on a machine that has Office 2003.
I now need to reference the Excel 2000 library which is on another machine.
I can compile using a reference through my network to the Excel 2000 library, but if the machine I compiled on is NOT connected to the network, that compiled DLL fails to run properly. However, using that same DLL on machines other than on the one I compiled seems to work (that is the Excel library is found on the local machine -- at least the couple I've tried). I don't want the compiled DLL even attempting to look across the network for fear of delays and other network issues problems.

Is there a way to reference a library across a network but to have alternate paths specified in the source code which are to be used first by the compiled DLL before attempting to look over the network path?

If that is not possible, I worry that if I try to install VS6 also on my other machine, that it might invalidate the license for my present machine. Does anyone know whether I can install on two machines without causing problems with licenses?

Thanks in advance.

[Siddharth Rout]

I now need to reference the Excel 2000 library which is on another machine.

Why do you need to do that? Any particular reason for that?

If you just want to make sure that your code runs on any excel platform then you can use latebinding.

[rummaging]

I don't want to have to rewrite the code to provide all of Excel's constants throughout. Also, I would rather not slow down my program since it can already take a while to operate on large amounts of worksheet data.

[si_the_geek]

You don't need to code the constants, Microsoft provide a .bas file with them in, so you can just add it to your project. For a download link, see my Excel tutorial (link in my signature).

In terms of speed, while there is a difference it shouldn't be particularly noticeable - and it is likely that you can more than offset it by optimising your code (perhaps by using an array to pass the data, as shown in my tutorial).

I worry that if I try to install VS6 also on my other machine, that it might invalidate the license for my present machine. Does anyone know whether I can install on two machines without causing problems with licenses?

Yes you can (I think limited to 2 computers), as long as you are only using it on one at a time.

[rummaging]

Glad to find out about the MS .bas file with constants. Thanks.

But if all I have to do is compile on a machine with Excel 2000 then I would probably opt for that rather than incur any slow-down. I ran into problems when I tried using arrays to move data around on the worksheet (if that's what you're referring to). Since my program processes lots of data, it could possibly run for minutes.

Thanks SI for the info about installing on 2 computers.

Does anyone now about redirecting where to find a library reference.

[si_the_geek]

I ran into problems when I tried using arrays to move data around on the worksheet (if that's what you're referring to).

We can help you fix them if you want - and depending on the code you are currently using, it may be 5 to 10 times faster.

Does anyone now about redirecting where to find a library reference.

I'm fairly certain you can't, except via Late Binding.

[rummaging]

I haven't found it anywhere, but off hand, I can't see why there couldn't be a directive that changes the path priorities to look for a reference.

Unfortunately, I didn't document exactly what the problem was with using arrays. Part of it might be that I move around up to 32K of cell content. Another problem I might have run into was that I want to copy text numbers into other cells without changing them to actual numbers. I think they might have been getting changed to numbers and so I had to check for that and fix them back to text -- lost any time advantage. Not sure though if that was the main problem. I'm aware of exceeding an array greater than 911 (or something like that). I had added a solution for that.

[si_the_geek]

I haven't found it anywhere, but off hand, I can't see why there couldn't be a directive that changes the path priorities to look for a reference.

The VB runtime deals with it all, and I have never seen any way to interact with that side of things other than the References screen or Late Binding - and I've got experience of thousands of threads, presumably hundreds on this kind of topic.

Unfortunately, I didn't document exactly what the problem was with using arrays. Part of it might be that I move around up to 32K of cell content. Another problem I might have run into was that I want to copy text numbers into other cells without changing them to actual numbers. I think they might have been getting changed to numbers and so I had to check for that and fix them back to text -- lost any time advantage. Not sure though if that was the main problem. I'm aware of exceeding an array greater than 911 (or something like that). I had added a solution for that.

The text-numbers issue can be solved by adding ' in front of the number.

There are ways to solve the others too, but we need to see your code, and know what the problem is.

[rummaging]

okay, no redirecting of a reference path I guess.

So, I'll compile on my other machine that has the earliest version of Excel that I want to support (2000).

I see by looking at my commented out code that I was needing to accomodate the conversion of a text number into an actual number when copying cells to destination using arrays.

And, yes, things get slowed down when you have to check if each cell's content is <911 and check if it's necessary to insert the ' in front to force a cell to become text, and then having to make the edit to the cell after the array is copied to the destination. And I think there was at least one other problem that clinched it for me to simply copy three cells individually at a time (looping thousands of times depending on how much data is selected).

Below is an example of what I had to do:

If Len(Place.Offset(i, Uniq).Value) < 911 And Len(Place.Offset(i, Item).Value) < 911 And Len(Place.Offset(i, Prefix1).Value) < 911 Then
     aTemp = wSheet.Range(Place.Offset(i, Uniq).Address, Place.Offset(i, Prefix1).Address).Value: wSheet.Range(Place.Offset(M, OpRsltA).Address, Place.Offset(M, OpRslt).Address).Value = aTemp
     If Place.Offset(M, OpRslt).Value <> Place.Offset(i, Prefix1).Value Then Place.Offset(M, OpRslt).Value = "'" & Place.Offset(i, Prefix1).Value
End If

[si_the_geek]

One obvious speed issue with that is that you are reading the cells, rather than the initial data - you should be doing all of that work before you pass the data to Excel if possible, or at least reading from the array instead of Excel.

Another issue is your use of Offset rather than a specific cell reference.

Another is your use of three conditions in a single If statement joined by And, it would be faster to use 3 separate ones (each with an End If).

[rummaging]

Thanks Si.

That code had been comment out so I wasn't using it. Since that time, I have been more careful about using multiple IF THENS for conditional tests for the reason you state.

I do need to access a hidden worksheet to get the data to be copied since I presort using Excel's sort method to get the exact same results as Excel's sorting works on a worksheet.

I don't think I have much of a choice about using some kind of offset.
I guess the following would be faster in other places where I acess a worksheet...

BaseRow = Place.Row
BaseCol = Place.Column

'much more code in the loop, offset is small percent of everything else going on

DO
   wSheet.Range(range(i + BaseRow, Uniq + BaseCol).Address, range(i + BaseRow, Prefix1 + BaseCol).Address).Value
   i = i + 1
LOOP

Wait a minute, is that really faster if the range addresses still need to be established?

[dilettante]

The performance difference between late and early binding for Automation is simply not going to be significant. Marshalling data cross-process is going to dwarf the late binding overhead anyway, and you have that in both cases.

With Excel and Word there is a special case though, DISPID binding:

The two most commonly automated Office applications, Microsoft Word and Microsoft Excel, are distinct in that their implementations of IDispatch (late binding) predate the v-table methods that are used by early bound clients. These implementations of IDispatch have been highly optimized and run slightly faster than early binding if using a cached DISPID. For this reason, DISPID binding to these two Office applications is recommended for out-of-process clients. As new functionality and requirements are added to the product, Office applications will extend the interfaces that are used for Automation in order to add new properties, methods, and events to their object models. In rare cases, these changes may cause unpredictable behavior or application errors when early binding is used because the v-table may change between versions. For this reason, Microsoft recommends the use of DISPID binding to Office if the client needs assurance of compatibility across versions.

INFO: Use DISPID Binding to Automate Office Applications Whenever Possible

While the title says "Office" this only aplies to Excel and Word. And sadly there is no practical way to use DISPID binding in VB6.

It isn't a question of "redirecting a path" as you ask for. Later versions of Excel and Word have different type libraries that contain extensions of the interface described in earlier version typelibs. Early binding against a later typelib results in a later interface "compiled into" your program. You can't change this at runtime.

However if you really insist on early binding you can follow the recommendation:

Microsoft Office applications provide a good example of such COM servers. Office applications will typically expand their interfaces to add new functionality or correct previous shortcomings between versions. If you need to automate an Office application, it is recommended that you early bind to the earliest version of the product that you expect could be installed on your client's system. For example, if you need to be able to automate Excel 95, Excel 97, Excel 2000, and Excel 2002, you should use the type library for Excel 95 (XL5en32.olb) to maintain compatibility with all three versions.

"Three versions" isn't a typo. I think 95 and 97 were identical, or was it 2000 and 2002?

However immediately after this Microsoft says:

Office applications also demonstrate that object models with large dual interfaces can suffer limitations in marshalling on some platforms. For your code to work best across all platforms, use IDispatch.

I.e., use late binding... possibly for better performance because of cross process marshalling costs.

Using early binding and late binding in Automation

The only way to compile against an older version of Excel is to have Excel installed on the system. Excel9.olb is not available separately as far as I know. It is explicitly not redistributable.


Some of these guys posting here have a lot of experience automating Excel. I think their recommendation of late binding makes sense on a lot of levels.

[rummaging]

Thanks for your time Si.

It isn't a question of "redirecting a path" as you ask for. Later versions of Excel and Word have different type libraries that contain extensions of the interface described in earlier version typelibs. Early binding against a later typelib results in a later interface "compiled into" your program. You can't change this at runtime.

I'm not sure if you are misunderstanding me or I'm misunderstanding you about that. I am compiling across my network using the older Excel library version. This works with newer versions of Excel. But I don't want the DLL to try to look for the library first across a non-existent network path and then look locally. Seems that an alternate (higher priority) path to the library should be able to be specified so that it looks locally first. But I accept that it isn't possible.

I might try switching over to late binding to see the actual affect on speed. Now that you've pointed out the .bas file of constants, that will make it easier.

Thanks agian.

[rummaging]

Si, that MS .bas file of constants at your link is for Excel 97. Do you know of one for Excel 2000 somewhere? I haven't spotted one yet.

If no newer .bas file exists, then is there a list somewhere of added constants between 97 and 2000? I don't use many Excel constants but, if any are missing, I'd rather not have to determine what the values should be if I don't have to.

[rummaging]

Si and Dilettante,

Thanks for the info, Dilettante. I only just noticed it was you who replied here and not Si. I see that my previous comment about "misunderstanding" should have been directed to you instead of Si. But I'm no longer looking to pursue redirecting reference paths if you all say it is not possible. And I'll revisit using late-binding.

[si_the_geek]

In terms of the .bas file, I haven't seen any other versions - but you can search the MS site for similar articles. If you find one, let me know and I'll add the link to the tutorial.

Unless you are doing things that were added in 2000 or later (which is surprisingly rare), the 97 module will be fine for you. If you use Option Explicit (which you always should), VB will tell you which constant(s) are missing when you do a full compile - and finding any extras doesn't take long (the VB Editor in Excel can give you the code for the constant definition).

"Three versions" isn't a typo. I think 95 and 97 were identical, or was it 2000 and 2002?

The first three were definitely different (especially 95 to 97), so I suspect 2000 and 2002 are the ones which are the 'same' version.

DO
   wSheet.Range(range(i + BaseRow, Uniq + BaseCol).Address, range(i + BaseRow, Prefix1 + BaseCol).Address).Value
   i = i + 1
LOOP

In terms of speed and actual workload, that's rather long winded... if you add the "xl_Col" function from my tutorial, you could use this faster version:

DO
   wSheet.Range(xl_Col(Uniq + BaseCol) & (i + BaseRow) & ":" & xl_Col(Prefix1 + BaseCol) & (i + BaseRow)).Value
   i = i + 1
LOOP

[rummaging]

Thanks,

I'll be on the lookout for places where it would be significantly beneficial to eliminate using Excel for passing back range addresses and will instead create them within VB.

[rummaging]

I generated a list of constants using Excel's 2003 Help, and displayed Enumeration/Microsoft Excel Constants. I right-clicked the displayed Help and viewed the source in my text editor. Saved that to a .Html file and loaded that into Excel where I filtered everything out except the constant name and its value.

I then used a utility of mine to compare the constants of Excel 97 in the .bas file you pointed out. The results are below.

I can't find a way to see Excel 2000 constants via Excel's Help. As long as constants didn't change from '97 to 2000 (only three changed between '97-2003) then all is okay.

What that comparison revealed is that 3 constants changed value (marked "HAS CHANGED/CHANGED TO"), and many of '97s constants are "MISSING", and many new ones have been "ADDED".

So someone could use that .bas file to get all of '97s constants and add the "Added" below and also use the 3 "ChangedTo". (see my own reply to this for new info).

MISSING
xl3DBar	-4099
xl3DEffects1	13
xl3DEffects2	14
xl3DSurface	-4103
xlAbove	0
xlAccounting1	4
xlAccounting2	5
xlAccounting3	6
xlAccounting4	17
xlAdd	2
xlAll	-4104
xlAllExceptBorders	6
xlAutomatic	-4105
xlBar	2
xlBelow	1
xlBidi	-5000
xlBidiCalendar	3
xlBoth	1
xlBottom	-4107
xlCascade	7
xlCenter	-4108
xlCenterAcrossSelection	7
xlChart4	2
xlChartSeries	17
xlChartShort	6
xlChartTitles	18
xlChecker	9
xlCircle	8
xlClassic1	1
xlClassic2	2
xlClassic3	3
xlClosed	3
xlColor1	7
xlColor2	8
xlColor3	9
xlColumn	3
xlCombination	-4111
xlComplete	4
xlConstants	2
xlContents	2
xlContext	-5002
xlCorner	2
xlCrissCross	16
xlCross	4
xlCustom	-4114
xlDebugCodePane	13
xlDefaultAutoFormat	-1
xlDesktop	9
xlDiamond	2
xlDirect	1
xlDistributed	-4117
xlDivide	5
xlDoubleAccounting	5
xlDoubleClosed	5
xlDoubleOpen	4
xlDoubleQuote	1
xlDrawingObject	14
xlEntireChart	20
xlExcelMenus	1
xlExtended	3
xlFill	5
xlFirst	0
xlFixedValue	1
xlFloating	5
xlFormats	-4122
xlFormula	5
xlGeneral	1
xlGray16	17
xlGray25	-4124
xlGray50	-4125
xlGray75	-4126
xlGray8	18
xlGregorian	2
xlGrid	15
xlGridline	22
xlHigh	-4127
xlHindiNumerals	3
xlIcons	1
xlImmediatePane	12
xlInside	2
xlInteger	2
xlJustify	-4130
xlLast	1
xlLastCell	11
xlLatin	-5001
xlLeft	-4131
xlLeftToRight	2
xlLightDown	13
xlLightHorizontal	11
xlLightUp	14
xlLightVertical	12
xlList1	10
xlList2	11
xlList3	12
xlLocalFormat1	15
xlLocalFormat2	16
xlLogicalCursor	1
xlLong	3
xlLotusHelp	2
xlLow	-4134
xlLTR	-5003
xlMacrosheetCell	7
xlManual	-4135
xlMaximum	2
xlMinimum	4
xlMinusValues	3
xlMixed	2
xlModule	-4141
xlMultiply	4
xlNarrow	1
xlNextToAxis	4
xlNoDocuments	3
xlNone	-4142
xlNotes	-4144
xlOff	-4146
xlOn	1
xlOpaque	3
xlOpen	2
xlOutside	3
xlPartial	3
xlPercent	2
xlPlus	9
xlPlusValues	2
xlReference	4
xlRight	-4152
xlRTL	-5004
xlScale	3
xlSemiautomatic	2
xlSemiGray75	10
xlShort	1
xlShowLabel	4
xlShowLabelAndPercent	5
xlShowPercent	3
xlShowValue	2
xlSimple	-4154
xlSingle	2
xlSingleAccounting	4
xlSingleQuote	2
xlSolid	1
xlSquare	1
xlStar	5
xlStError	4
xlStrict	2
xlSubtract	3
xlSystem	1
xlTextBox	16
xlTiled	1
xlTitleBar	8
xlToolbar	1
xlToolbarButton	2
xlTop	-4160
xlTopToBottom	1
xlTransparent	2
xlTriangle	3
xlVeryHidden	2
xlVisible	12
xlVisualCursor	2
xlWatchPane	11
xlWide	3
xlWorkbookTab	6
xlWorksheet4	1
xlWorksheetCell	3
xlWorksheetShort	5

HAS CHANGED
xlDialogChartSourceData	541
xlDialogPhonetic	538
xlPasteAllExceptBorders	6

CHANGED TO
xlDialogChartSourceData	540
xlDialogPhonetic	656
xlPasteAllExceptBorders	7

ADDED
_xlDialogChartSourceData	541	(same as older value without _)
_xlDialogPhonetic	538	(same as older value without _)
xlADORecordset	7
xlAllTables	2
xlAlways	1
xlAnyKey	2
xlArabicBothStrict	3
xlArabicNone	0
xlArabicStrictAlefHamza	1
xlArabicStrictFinalYaa	2
xlAsRequired	0
xlAtBottom	2
xlAtTop	1
xlButtonOnly	2
xlCalculatedMember	0
xlCalculatedSet	1
xlCalculating	1
xlCmdCube	1
xlCmdDefault	4
xlCmdList	5
xlCmdSql	2
xlCmdTable	3
xlDAORecordset	2
xlDataLabelSeparatorDefault	1
xlDialogChartOptionsDataLabelMultiple	724
xlDialogCreateList	796
xlDialogDataLabelMultiple	723
xlDialogErrorChecking	732
xlDialogEvaluateFormula	709
xlDialogExternalDataProperties	530
xlDialogImportTextFile	666
xlDialogMyPermission	834
xlDialogNewWebQuery	667
xlDialogOptionsME	647
xlDialogPermission	832
xlDialogPivotClientServerSet	689
xlDialogPropertyFields	754
xlDialogPublishAsWebPage	653
xlDialogSearch	731
xlDialogWebOptionsBrowsers	773
xlDialogWebOptionsEncoding	686
xlDialogWebOptionsFiles	684
xlDialogWebOptionsFonts	687
xlDialogWebOptionsGeneral	683
xlDialogWebOptionsPictures	685
xlDisplayNone	1
xlDisplayUnitLabel	30
xlDMYFormat	4
xlDone	0
xlDYMFormat	7
xlEMDFormat	10
xlEmptyCellReferences	7
xlEntirePage	1
xlEscKey	1
xlEvaluateToError	1
xlExtractData	2
xlFirstRow	256
xlFormatFromLeftOrAbove	0
xlFormatFromRightOrBelow	1
xlGeneralFormat	1
xlHebrewFullScript	0
xlHebrewMixedAuthorizedScript	3
xlHebrewMixedScript	2
xlHebrewPartialScript	1
xlHierarchy	1
xlHtml	44
xlHtmlCalc	1
xlHtmlChart	3
xlHtmlList	2
xlHtmlStatic	0
xlHundredMillions	-8
xlHundreds	-2
xlHundredThousands	-5
xlInconsistentFormula	4
xlIndicatorAndButton	0
xlLinkInfoStatus	3
xlLinkStatusCopiedValues	10
xlLinkStatusIndeterminate	5
xlLinkStatusInvalidName	7
xlLinkStatusMissingFile	1
xlLinkStatusMissingSheet	2
xlLinkStatusNotStarted	6
xlLinkStatusOK	0
xlLinkStatusOld	3
xlLinkStatusSourceNotCalculated	4
xlLinkStatusSourceNotOpen	8
xlLinkStatusSourceOpen	9
xlListConflictDialog	0
xlListConflictDiscardAllConflicts	2
xlListConflictError	3
xlListConflictRetryAllConflicts	1
xlListDataTypeCheckbox	9
xlListDataTypeChoice	6
xlListDataTypeChoiceMulti	7
xlListDataTypeCounter	11
xlListDataTypeCurrency	4
xlListDataTypeDateTime	5
xlListDataTypeHyperLink	10
xlListDataTypeListLookup	8
xlListDataTypeMultiLineRichText	12
xlListDataTypeMultiLineText	2
xlListDataTypeNone	0
xlListDataTypeNumber	3
xlListDataTypeText	1
xlListDataValidation	8
xlMDYFormat	3
xlMeasure	2
xlMillionMillions	-10
xlMillions	-6
xlMissingItemsDefault	-1
xlMissingItemsMax	32500
xlMissingItemsNone	0
xlMYDFormat	6
xlNever	2
xlNoKey	0
xlNormalLoad	0
xlNumberAsText	3
xlODBCQuery	1
xlOLEDBQuery	5
xlOmittedCells	5
xlOutline	1
xlPageBreakNone	-4142
xlParamTypeWChar	-8
xlPasteColumnWidths	8
xlPasteFormulasAndNumberFormats	11
xlPasteValidation	6
xlPasteValuesAndNumberFormats	12
xlPending	2
xlPivotCellBlankCell	9
xlPivotCellCustomSubtotal	7
xlPivotCellDataField	4
xlPivotCellDataPivotField	8
xlPivotCellGrandTotal	3
xlPivotCellPageFieldItem	6
xlPivotCellPivotField	5
xlPivotCellPivotItem	1
xlPivotCellSubtotal	2
xlPivotCellValue	0
xlPivotChartDropZone	32
xlPivotChartFieldButton	31
xlPivotTableReport	1
xlPivotTableVersion10	1
xlPivotTableVersion2000	0
xlPivotTableVersionCurrent	-1
xlPrintErrorsBlank	1
xlPrintErrorsDash	2
xlPrintErrorsDisplayed	0
xlPrintErrorsNA	3
xlPTClassic	20
xlPTNone	21
xlQueryTable	0
xlRangeAutoFormatClassicPivotTable	31
xlRangeAutoFormatPTNone	42
xlRangeAutoFormatReport1	21
xlRangeAutoFormatReport10	30
xlRangeAutoFormatReport2	22
xlRangeAutoFormatReport3	23
xlRangeAutoFormatReport4	24
xlRangeAutoFormatReport5	25
xlRangeAutoFormatReport6	26
xlRangeAutoFormatReport7	27
xlRangeAutoFormatReport8	28
xlRangeAutoFormatReport9	29
xlRangeAutoFormatTable1	32
xlRangeAutoFormatTable10	41
xlRangeAutoFormatTable2	33
xlRangeAutoFormatTable3	34
xlRangeAutoFormatTable4	35
xlRangeAutoFormatTable5	36
xlRangeAutoFormatTable6	37
xlRangeAutoFormatTable7	38
xlRangeAutoFormatTable8	39
xlRangeAutoFormatTable9	40
xlRangeValueDefault	10
xlRangeValueMSPersistXML	12
xlRangeValueXMLSpreadsheet	11
xlRepairFile	1
xlReport1	0
xlReport10	9
xlReport2	1
xlReport3	2
xlReport4	3
xlReport5	4
xlReport6	5
xlReport7	6
xlReport8	7
xlReport9	8
xlScenario	4
xlSet	3
xlSkipColumn	9
xlSmartTagControlActiveX	13
xlSmartTagControlButton	6
xlSmartTagControlCheckbox	9
xlSmartTagControlCombo	12
xlSmartTagControlHelp	3
xlSmartTagControlHelpURL	4
xlSmartTagControlImage	8
xlSmartTagControlLabel	7
xlSmartTagControlLink	2
xlSmartTagControlListbox	11
xlSmartTagControlRadioGroup	14
xlSmartTagControlSeparator	5
xlSmartTagControlSmartTag	1
xlSmartTagControlTextbox	10
xlSortNormal	0
xlSortTextAsNumbers	1
xlSourceAutoFilter	3
xlSourceChart	5
xlSourcePivotTable	6
xlSourcePrintArea	2
xlSourceQuery	7
xlSourceRange	4
xlSourceSheet	1
xlSourceWorkbook	0
xlSpeakByColumns	1
xlSpeakByRows	0
xlSpecifiedTables	3
xlSrcExternal	0
xlSrcRange	1
xlSrcXml	2
xlTable1	10
xlTable10	19
xlTable2	11
xlTable3	12
xlTable4	13
xlTable5	14
xlTable6	15
xlTable7	16
xlTable8	17
xlTable9	18
xlTabular	0
xlTenMillions	-7
xlTenThousands	-4
xlTextDate	2
xlTextFormat	2
xlTextImport	6
xlTextVisualLTR	1
xlTextVisualRTL	2
xlThousandMillions	-9
xlThousands	-3
xlTotalsCalculationAverage	2
xlTotalsCalculationCount	3
xlTotalsCalculationCountNums	4
xlTotalsCalculationMax	6
xlTotalsCalculationMin	5
xlTotalsCalculationNone	0
xlTotalsCalculationStdDev	7
xlTotalsCalculationSum	1
xlTotalsCalculationVar	8
xlUnicodeText	42
xlUnknown	1000
xlUnlockedFormulaCells	6
xlUpdateLinksAlways	3
xlUpdateLinksNever	2
xlUpdateLinksUserSetting	1
xlWebArchive	45
xlWebFormattingAll	1
xlWebFormattingNone	3
xlWebFormattingRTF	2
xlWebQuery	4
xlWithinSheet	1
xlWithinWorkbook	2
xlXMLData	47
xlXmlExportSuccess	0
xlXmlExportValidationFailed	1
xlXmlImportElementsTruncated	1
xlXmlImportSuccess	0
xlXmlImportValidationFailed	2
xlXmlLoadImportToList	2
xlXmlLoadMapXml	3
xlXmlLoadOpenXml	1
xlXmlLoadPromptUser	0
xlXMLSpreadsheet	46
xlYDMFormat	8
xlYMDFormat	5

[si_the_geek]

That could be very useful, thanks for sharing.

I hope you don't mind, I've edited your post to have three code blocks (removed/changed/added) to make it a little clearer - and I plan (time permitting!) to write an updated module over the weekend, which I'll post in the CodeBank (with a link to your post in case people want the details).

I found this MS page which seems to be the same as the help file you've got (or at least close to it), but based on my searches there isn't an Excel 2000 version.



I used the 97 module against Excel 2000 for years, and can't remember having any issues with it (I didn't save an updated version of it, so I assume there weren't any), so it might be best to use that - I think the changes are more likely to be at the 2003 version.

[rummaging]

Si,
Actually, the page you found is all of the constants that were missing in Excel's Help file.

They are all of the constants I had listed as "REMOVED" (they were missing), plus these 4 more:
xlFullScript 1
xlMixedAuthorizedScript 4
xlMixedScript 3
xlPartialScript 2

So none were removed (which makes sense).

[rummaging]

I can't make the following changes to the original msg cuz it would exceed the character limit permitted. So here are the changes.

I used Excel's 2003 to generate a list of constants by going to Excle's Help>TableOfContents>Enumeration/Microsoft Excel Constants. I right-clicked the displayed Help and viewed the source in my text editor. Saved that to a .Html file and loaded that into Excel where I filtered everything out except the constant name and its value.

I then used a utility of mine to compare the constants of Excel 97 in the .bas file Si pointed out. The results are below.

I can't find a way to see Excel 2000 constants via Excel's Help. As long as constants didn't change from '97 to 2000 (only three changed between '97-2003) then all is okay.

What that comparison revealed is that 3 constants changed value (marked "HAS CHANGED/CHANGED TO"), and many of '97s constants are not listed in Excel 2003 Help and are in the original msg as "MISSING", and many new ones have been "ADDED".

So someone could use that .bas file to get all of '97s constants and add the "Added" below and also use the 3 "ChangedTo" plus the one other changed constant indicated at the bottom of this msg.

ALSO..
Si provided the following link: This Link, which has all of the constants MISSING from the Excel 2003 Help plus the following four new ones:
xlFullScript 1
xlMixedAuthorizedScript 4
xlMixedScript 3
xlPartialScript 2

And the following has also changed from a value of 6 to 7:
xlAllExceptBorders 7