Re-directing where to look for Office library reference paths
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.
Re: Re-directing where to look for Office library reference paths
Quote:
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.
Re: Re-directing where to look for Office library reference paths
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.
Re: Re-directing where to look for Office library reference paths
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).
Quote:
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.
Re: Re-directing where to look for Office library reference paths
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.
Re: Re-directing where to look for Office library reference paths
Quote:
Originally Posted by
rummaging
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.
Quote:
Does anyone now about redirecting where to find a library reference.
I'm fairly certain you can't, except via Late Binding.
Re: Re-directing where to look for Office library reference paths
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.
Re: Re-directing where to look for Office library reference paths
Quote:
Originally Posted by
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.
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.
Quote:
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.
Re: Re-directing where to look for Office library reference paths
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:
Code:
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
Re: Re-directing where to look for Office library reference paths
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).
Re: Re-directing where to look for Office library reference paths
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...
Code:
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?
Re: Re-directing where to look for Office library reference paths
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:
Quote:
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:
Quote:
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:
Quote:
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.
Re: Re-directing where to look for Office library reference paths
Thanks for your time Si.
Code:
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.
Re: Re-directing where to look for Office library reference paths
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.
Re: Re-directing where to look for Office library reference paths
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.
Re: Re-directing where to look for Office library reference paths
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).
Quote:
Originally Posted by dilettante
"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.
Quote:
Originally Posted by rummaging
Code:
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:
Code:
DO
wSheet.Range(xl_Col(Uniq + BaseCol) & (i + BaseRow) & ":" & xl_Col(Prefix1 + BaseCol) & (i + BaseRow)).Value
i = i + 1
LOOP
Re: Re-directing where to look for Office library reference paths
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.
Re: Re-directing where to look for Office library reference paths
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).
Code:
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
Code:
HAS CHANGED
xlDialogChartSourceData 541
xlDialogPhonetic 538
xlPasteAllExceptBorders 6
CHANGED TO
xlDialogChartSourceData 540
xlDialogPhonetic 656
xlPasteAllExceptBorders 7
Code:
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
Re: Re-directing where to look for Office library reference paths
That could be very useful, thanks for sharing. :thumb:
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.
Re: Re-directing where to look for Office library reference paths
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).
Re: Re-directing where to look for Office library reference paths
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
