 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / November 2006VBA for Excel function
Hello again all, I am stuggling with the syntax of VBA in Access versus VBA in Excel, so any assistance you can provide is greatly appreciated. My goal is to clear contents rows 2 to end in OPEN tab, goto tab OPEN1, rows 2 to end - CUT. Then goto OPEN tab, rows to to end - Paste values. Everything works well except the last line of code to paste the values. Here is what I have Set objOpen = objXLBook. _ Worksheets("Open") LastRow3 = objOpen.UsedRange.Rows.Count objOpen.Range("2:" & LastRow3).ClearContents Set objOpen1 = objXLBook. _ Worksheets("Open1") LastRow3 = objOpen1.UsedRange.Rows.Count objOpen1.Range("A2:Q" & LastRow3).Cut 'Line below creates error objOpen.Range("A2:Q" & LastRow3).PasteSpecial (xlPasteValues) The named arguments of excel aren't natively exposed. Give this a go. objOpen.Range("A2:Q" & LastRow3).PasteSpecial -4163 I always late bind and add this declaration in a separate module which allows you to use those named args Option Compare Database Public Const xl24HourClock = 33 Public Const xl3DArea = -4098 Public Const xl3DAreaStacked = 78 Public Const xl3DAreaStacked100 = 79 Public Const xl3DBar = -4099 Public Const xl3DBarClustered = 60 Public Const xl3DBarStacked = 61 Public Const xl3DBarStacked100 = 62 Public Const xl3DColumn = -4100 Public Const xl3DColumnClustered = 54 Public Const xl3DColumnStacked = 55 Public Const xl3DColumnStacked100 = 56 Public Const xl3DEffects1 = 13 Public Const xl3DEffects2 = 14 Public Const xl3DLine = -4101 Public Const xl3DPie = -4102 Public Const xl3DPieExploded = 70 Public Const xl3DSurface = -4103 Public Const xl4DigitYears = 43 Public Const xlA1 = 1 Public Const xlAbove = 0 Public Const xlAbsolute = 1 Public Const xlAbsRowRelColumn = 2 Public Const xlAccounting1 = 4 Public Const xlAccounting2 = 5 Public Const xlAccounting3 = 6 Public Const xlAccounting4 = 17 Public Const xlAdd = 2 Public Const xlAddIn = 18 Public Const xlAll = -4104 Public Const xlAllAtOnce = 2 Public Const xlAllChanges = 2 Public Const xlAllExceptBorders = 6 Public Const xlAllFaces = 7 Public Const xlAlternateArraySeparator = 16 Public Const xlAnd = 1 Public Const xlAnyGallery = 23 Public Const xlArea = 1 Public Const xlAreaStacked = 76 Public Const xlAreaStacked100 = 77 Public Const xlArrangeStyleCascade = 7 Public Const xlArrangeStyleHorizontal = -4128 Public Const xlArrangeStyleTiled = 1 Public Const xlArrangeStyleVertical = -4166 Public Const xlArrowHeadLengthLong = 3 Public Const xlArrowHeadLengthMedium = -4138 Public Const xlArrowHeadLengthShort = 1 Public Const xlArrowHeadStyleClosed = 3 Public Const xlArrowHeadStyleDoubleClosed = 5 Public Const xlArrowHeadStyleDoubleOpen = 4 Public Const xlArrowHeadStyleNone = -4142 Public Const xlArrowHeadStyleOpen = 2 Public Const xlArrowHeadWidthMedium = -4138 Public Const xlArrowHeadWidthNarrow = 1 Public Const xlArrowHeadWidthWide = 3 Public Const xlAscending = 1 Public Const xlAutoActivate = 3 Public Const xlAutoClose = 2 Public Const xlAutoDeactivate = 4 Public Const xlAutoFill = 4 Public Const xlAutomatic = -4105 Public Const xlAutomaticScale = -4105 Public Const xlAutomaticUpdate = 4 Public Const xlAutoOpen = 1 Public Const xlAverage = -4106 Public Const xlAxis = 21 Public Const xlAxisCrossesAutomatic = -4105 Public Const xlAxisCrossesCustom = -4114 Public Const xlAxisCrossesMaximum = 2 Public Const xlAxisCrossesMinimum = 4 Public Const xlAxisTitle = 17 Public Const xlBackgroundAutomatic = -4105 Public Const xlBackgroundOpaque = 3 Public Const xlBackgroundTransparent = 2 Public Const xlBar = 2 Public Const xlBarClustered = 57 Public Const xlBarOfPie = 71 Public Const xlBarStacked = 58 Public Const xlBarStacked100 = 59 Public Const xlBelow = 1 Public Const xlBetween = 1 Public Const xlBidi = -5000 Public Const xlBidiCalendar = 3 Public Const xlBIFF = 2 Public Const xlBitmap = 2 Public Const xlBlanks = 4 Public Const xlBMP = 1 Public Const xlBoth = 1 Public Const xlBottom = -4107 Public Const xlBottom10Items = 4 Public Const xlBottom10Percent = 6 Public Const xlBox = 0 Public Const xlBubble = 15 Public Const xlBubble3DEffect = 87 Public Const xlBuiltIn = 21 Public Const xlButton = 15 Public Const xlButtonControl = 0 Public Const xlByColumns = 2 Public Const xlByRows = 1 Public Const xlCalculationAutomatic = -4105 Public Const xlCalculationManual = -4135 Public Const xlCalculationSemiautomatic = 2 Public Const xlCancel = 1 Public Const xlCap = 1 Public Const xlCascade = 7 Public Const xlCategory = 1 Public Const xlCategoryScale = 2 Public Const xlCellTypeAllFormatConditions = -4172 Public Const xlCellTypeAllValidation = -4174 Public Const xlCellTypeBlanks = 4 Public Const xlCellTypeComments = -4144 Public Const xlCellTypeConstants = 2 Public Const xlCellTypeFormulas = -4123 Public Const xlCellTypeLastCell = 11 Public Const xlCellTypeSameFormatConditions = -4173 Public Const xlCellTypeSameValidation = -4175 Public Const xlCellTypeVisible = 12 Public Const xlCellValue = 1 Public Const xlCenter = -4108 Public Const xlCenterAcrossSelection = 7 Public Const xlCGM = 7 Public Const xlChangeAttributes = 6 Public Const xlChart = -4109 Public Const xlChart4 = 2 Public Const xlChartArea = 2 Public Const xlChartAsWindow = 5 Public Const xlChartInPlace = 4 Public Const xlChartSeries = 17 Public Const xlChartShort = 6 Public Const xlChartTitle = 4 Public Const xlChartTitles = 18 Public Const xlCheckBox = 1 Public Const xlChecker = 9 Public Const xlChronological = 3 Public Const xlCircle = 8 Public Const xlClassic1 = 1 Public Const xlClassic2 = 2 Public Const xlClassic3 = 3 Public Const xlClipboard = 3 Public Const xlClipboardFormatBIFF = 8 Public Const xlClipboardFormatBIFF2 = 18 Public Const xlClipboardFormatBIFF3 = 20 Public Const xlClipboardFormatBIFF4 = 30 Public Const xlClipboardFormatBinary = 15 Public Const xlClipboardFormatBitmap = 9 Public Const xlClipboardFormatCGM = 13 Public Const xlClipboardFormatCSV = 5 Public Const xlClipboardFormatDIF = 4 Public Const xlClipboardFormatDspText = 12 Public Const xlClipboardFormatEmbeddedObject = 21 Public Const xlClipboardFormatEmbedSource = 22 Public Const xlClipboardFormatLink = 11 Public Const xlClipboardFormatLinkSource = 23 Public Const xlClipboardFormatLinkSourceDesc = 32 Public Const xlClipboardFormatMovie = 24 Public Const xlClipboardFormatNative = 14 Public Const xlClipboardFormatObjectDesc = 31 Public Const xlClipboardFormatObjectLink = 19 Public Const xlClipboardFormatOwnerLink = 17 Public Const xlClipboardFormatPICT = 2 Public Const xlClipboardFormatPrintPICT = 3 Public Const xlClipboardFormatRTF = 7 Public Const xlClipboardFormatScreenPICT = 29 Public Const xlClipboardFormatStandardFont = 28 Public Const xlClipboardFormatStandardScale = 27 Public Const xlClipboardFormatSYLK = 6 Public Const xlClipboardFormatTable = 16 Public Const xlClipboardFormatText = 0 Public Const xlClipboardFormatToolFace = 25 Public Const xlClipboardFormatToolFacePICT = 26 Public Const xlClipboardFormatVALU = 1 Public Const xlClipboardFormatWK1 = 10 Public Const xlClosed = 3 Public Const xlCodePage = 2 Public Const xlColor1 = 7 Public Const xlColor2 = 8 Public Const xlColor3 = 9 Public Const xlColorIndexAutomatic = -4105 Public Const xlColorIndexNone = -4142 Public Const xlColumn = 3 Public Const xlColumnClustered = 51 Public Const xlColumnField = 2 Public Const xlColumnHeader = -4110 Public Const xlColumnItem = 5 Public Const xlColumnLabels = 2 Public Const xlColumns = 2 Public Const xlColumnSeparator = 14 Public Const xlColumnStacked = 52 Public Const xlColumnStacked100 = 53 Public Const xlColumnThenRow = 2 Public Const xlCombination = -4111 Public Const xlCommand = 2 Public Const xlCommandUnderlinesAutomatic = -4105 Public Const xlCommandUnderlinesOff = -4146 Public Const xlCommandUnderlinesOn = 1 Public Const xlCommentAndIndicator = 1 Public Const xlCommentIndicatorOnly = -1 Public Const xlComments = -4144 Public Const xlComplete = 4 Public Const xlConeBarClustered = 102 Public Const xlConeBarStacked = 103 Public Const xlConeBarStacked100 = 104 Public Const xlConeCol = 105 Public Const xlConeColClustered = 99 Public Const xlConeColStacked = 100 Public Const xlConeColStacked100 = 101 Public Const xlConeToMax = 5 Public Const xlConeToPoint = 4 Public Const xlConsolidation = 3 Public Const xlConstant = 1 Public Const xlConstants = 2 Public Const xlContents = 2 Public Const xlContext = -5002 Public Const xlContinuous = 1 Public Const xlCopy = 1 Public Const xlCorner = 2 Public Const xlCorners = 6 Public Const xlCount = -4112 Public Const xlCountNums = -4113 Public Const xlCountryCode = 1 Public Const xlCountrySetting = 2 Public Const xlCreatorCode = 1480803660 Public Const xlCrissCross = 16 Public Const xlCross = 4 Public Const xlCSV = 6 Public Const xlCSVMac = 22 Public Const xlCSVMSDOS = 24 Public Const xlCSVWindows = 23 Public Const xlCurrencyBefore = 37 Public Const xlCurrencyCode = 25 Public Const xlCurrencyDigits = 27 Public Const xlCurrencyLeadingZeros = 40 Public Const xlCurrencyMinusSign = 38 Public Const xlCurrencyNegative = 28 Public Const xlCurrencySpaceBefore = 36 Public Const xlCurrencyTrailingZeros = 39 Public Const xlCurrentPlatformText = -4158 Public Const xlCustom = -4114 Public Const xlCut = 2 Public Const xlCylinder = 3 Public Const xlCylinderBarClustered = 95 Public Const xlCylinderBarStacked = 96 Public Const xlCylinderBarStacked100 = 97 Public Const xlCylinderCol = 98 Public Const xlCylinderColClustered = 92 Public Const xlCylinderColStacked = 93 Public Const xlCylinderColStacked100 = 94 Public Const xlDash = -4115 Public Const xlDashDot = 4 Public Const xlDashDotDot = 5 Public Const xlDataAndLabel = 0 Public Const xlDatabase = 1 Public Const xlDataField = 4 Public Const xlDataHeader = 3 Public Const xlDataItem = 7 Public Const xlDataLabel = 0 Public Const xlDataLabelsShowBubbleSizes = 6 Public Const xlDataLabelsShowLabel = 4 Public Const xlDataLabelsShowLabelAndPercent = 5 Public Const xlDataLabelsShowNone = -4142 Public Const xlDataLabelsShowPercent = 3 Public Const xlDataLabelsShowValue = 2 Public Const xlDataOnly = 2 Public Const xlDataSeriesLinear = -4132 Public Const xlDataTable = 7 Public Const xlDate = 2 Public Const xlDateOrder = 32 Public Const xlDateSeparator = 17 Public Const xlDay = 1 Public Const xlDayCode = 21 Public Const xlDayLeadingZero = 42 Public Const xlDays = 0 Public Const xlDBF2 = 7 Public Const xlDBF3 = 8 Public Const xlDBF4 = 11 Public Const xlDebugCodePane = 13 Public Const xlDecimalSeparator = 3 Public Const xlDefault = -4143 Public Const xlDefaultAutoFormat = -1 Public Const xlDelimited = 1 Public Const xlDescending = 2 Public Const xlDesktop = 9 Public Const xlDiagonalDown = 5 Public Const xlDiagonalUp = 6 Public Const xlDialogActivate = 103 Public Const xlDialogActiveCellFont = 476 Public Const xlDialogAddChartAutoformat = 390 Public Const xlDialogAddinManager = 321 Public Const xlDialogAlignment = 43 Public Const xlDialogApplyNames = 133 Public Const xlDialogApplyStyle = 212 Public Const xlDialogAppMove = 170 Public Const xlDialogAppSize = 171 Public Const xlDialogArrangeAll = 12 Public Const xlDialogAssignToObject = 213 Public Const xlDialogAssignToTool = 293 Public Const xlDialogAttachText = 80 Public Const xlDialogAttachToolbars = 323 Public Const xlDialogAutoCorrect = 485 Public Const xlDialogAxes = 78 Public Const xlDialogBorder = 45 Public Const xlDialogCalculation = 32 Public Const xlDialogCellProtection = 46 Public Const xlDialogChangeLink = 166 Public Const xlDialogChartAddData = 392 Public Const xlDialogChartLocation = 527 Public Const xlDialogChartOptionsDataLabels = 505 Public Const xlDialogChartOptionsDataTable = 506 Public Const xlDialogChartSourceData = 541 Public Const xlDialogChartTrend = 350 Public Const xlDialogChartType = 526 Public Const xlDialogChartWizard = 288 Public Const xlDialogCheckboxProperties = 435 Public Const xlDialogClear = 52 Public Const xlDialogColorPalette = 161 Public Const xlDialogColumnWidth = 47 Public Const xlDialogCombination = 73 Public Const xlDialogConditionalFormatting = 583 Public Const xlDialogConsolidate = 191 Public Const xlDialogCopyChart = 147 Public Const xlDialogCopyPicture = 108 Public Const xlDialogCreateNames = 62 Public Const xlDialogCreatePublisher = 217 Public Const xlDialogCustomizeToolbar = 276 Public Const xlDialogCustomViews = 493 Public Const xlDialogDataDelete = 36 Public Const xlDialogDataLabel = 379 Public Const xlDialogDataSeries = 40 Public Const xlDialogDataValidation = 525 Public Const xlDialogDefineName = 61 Public Const xlDialogDefineStyle = 229 Public Const xlDialogDeleteFormat = 111 Public Const xlDialogDeleteName = 110 Public Const xlDialogDemote = 203 Public Const xlDialogDisplay = 27 Public Const xlDialogEditboxProperties = 438 Public Const xlDialogEditColor = 223 Public Const xlDialogEditDelete = 54 Public Const xlDialogEditionOptions = 251 Public Const xlDialogEditSeries = 228 Public Const xlDialogErrorbarX = 463 Public Const xlDialogErrorbarY = 464 Public Const xlDialogExtract = 35 Public Const xlDialogFileDelete = 6 Public Const xlDialogFileSharing = 481 Public Const xlDialogFillGroup = 200 Public Const xlDialogFillWorkgroup = 301 Public Const xlDialogFilter = 447 Public Const xlDialogFilterAdvanced = 370 Public Const xlDialogFindFile = 475 Public Const xlDialogFont = 26 Public Const xlDialogFontProperties = 381 Public Const xlDialogFormatAuto = 269 Public Const xlDialogFormatChart = 465 Public Const xlDialogFormatCharttype = 423 Public Const xlDialogFormatFont = 150 Public Const xlDialogFormatLegend = 88 Public Const xlDialogFormatMain = 225 Public Const xlDialogFormatMove = 128 Public Const xlDialogFormatNumber = 42 Public Const xlDialogFormatOverlay = 226 Public Const xlDialogFormatSize = 129 Public Const xlDialogFormatText = 89 Public Const xlDialogFormulaFind = 64 Public Const xlDialogFormulaGoto = 63 Public Const xlDialogFormulaReplace = 130 Public Const xlDialogFunctionWizard = 450 Public Const xlDialogGallery3dArea = 193 Public Const xlDialogGallery3dBar = 272 Public Const xlDialogGallery3dColumn = 194 Public Const xlDialogGallery3dLine = 195 Public Const xlDialogGallery3dPie = 196 Public Const xlDialogGallery3dSurface = 273 Public Const xlDialogGalleryArea = 67 Public Const xlDialogGalleryBar = 68 Public Const xlDialogGalleryColumn = 69 Public Const xlDialogGalleryCustom = 388 Public Const xlDialogGalleryDoughnut = 344 Public Const xlDialogGalleryLine = 70 Public Const xlDialogGalleryPie = 71 Public Const xlDialogGalleryRadar = 249 Public Const xlDialogGalleryScatter = 72 Public Const xlDialogGoalSeek = 198 Public Const xlDialogGridlines = 76 Public Const xlDialogInsert = 55 Public Const xlDialogInsertHyperlink = 596 Public Const xlDialogInsertNameLabel = 496 Public Const xlDialogInsertObject = 259 Public Const xlDialogInsertPicture = 342 Public Const xlDialogInsertTitle = 380 Public Const xlDialogLabelProperties = 436 Public Const xlDialogListboxProperties = 437 Public Const xlDialogMacroOptions = 382 Public Const xlDialogMailEditMailer = 470 Public Const xlDialogMailLogon = 339 Public Const xlDialogMailNextLetter = 378 Public Const xlDialogMainChart = 85 Public Const xlDialogMainChartType = 185 Public Const xlDialogMenuEditor = 322 Public Const xlDialogMove = 262 Public Const xlDialogNew = 119 Public Const xlDialogNote = 154 Public Const xlDialogObjectProperties = 207 Public Const xlDialogObjectProtection = 214 Public Const xlDialogOpen = 1 Public Const xlDialogOpenLinks = 2 Public Const xlDialogOpenMail = 188 Public Const xlDialogOpenText = 441 Public Const xlDialogOptionsCalculation = 318 Public Const xlDialogOptionsChart = 325 Public Const xlDialogOptionsEdit = 319 Public Const xlDialogOptionsGeneral = 356 Public Const xlDialogOptionsListsAdd = 458 Public Const xlDialogOptionsTransition = 355 Public Const xlDialogOptionsView = 320 Public Const xlDialogOutline = 142 Public Const xlDialogOverlay = 86 Public Const xlDialogOverlayChartType = 186 Public Const xlDialogPageSetup = 7 Public Const xlDialogParse = 91 Public Const xlDialogPasteNames = 58 Public Const xlDialogPasteSpecial = 53 Public Const xlDialogPatterns = 84 Public Const xlDialogPhonetic = 538 Public Const xlDialogPivotCalculatedField = 570 Public Const xlDialogPivotCalculatedItem = 572 Public Const xlDialogPivotFieldGroup = 433 Public Const xlDialogPivotFieldProperties = 313 Public Const xlDialogPivotFieldUngroup = 434 Public Const xlDialogPivotShowPages = 421 Public Const xlDialogPivotSolveOrder = 568 Public Const xlDialogPivotTableOptions = 567 Public Const xlDialogPivotTableWizard = 312 Public Const xlDialogPlacement = 300 Public Const xlDialogPrint = 8 Public Const xlDialogPrinterSetup = 9 Public Const xlDialogPrintPreview = 222 Public Const xlDialogPromote = 202 Public Const xlDialogProperties = 474 Public Const xlDialogProtectDocument = 28 Public Const xlDialogProtectSharing = 620 Public Const xlDialogPushbuttonProperties = 445 Public Const xlDialogReplaceFont = 134 Public Const xlDialogRoutingSlip = 336 Public Const xlDialogRowHeight = 127 Public Const xlDialogRun = 17 Public Const xlDialogSaveAs = 5 Public Const xlDialogSaveCopyAs = 456 Public Const xlDialogSaveNewObject = 208 Public Const xlDialogSaveWorkbook = 145 Public Const xlDialogSaveWorkspace = 285 Public Const xlDialogScale = 87 Public Const xlDialogScenarioAdd = 307 Public Const xlDialogScenarioCells = 305 Public Const xlDialogScenarioEdit = 308 Public Const xlDialogScenarioMerge = 473 Public Const xlDialogScenarioSummary = 311 Public Const xlDialogScrollbarProperties = 420 Public Const xlDialogSelectSpecial = 132 Public Const xlDialogSendMail = 189 Public Const xlDialogSeriesAxes = 460 Public Const xlDialogSeriesOptions = 557 Public Const xlDialogSeriesOrder = 466 Public Const xlDialogSeriesShape = 504 Public Const xlDialogSeriesX = 461 Public Const xlDialogSeriesY = 462 Public Const xlDialogSetBackgroundPicture = 509 Public Const xlDialogSetPrintTitles = 23 Public Const xlDialogSetUpdateStatus = 159 Public Const xlDialogSheet = -4116 Public Const xlDialogShowDetail = 204 Public Const xlDialogShowToolbar = 220 Public Const xlDialogSize = 261 Public Const xlDialogSort = 39 Public Const xlDialogSortSpecial = 192 Public Const xlDialogSplit = 137 Public Const xlDialogStandardFont = 190 Public Const xlDialogStandardWidth = 472 Public Const xlDialogStyle = 44 Public Const xlDialogSubscribeTo = 218 Public Const xlDialogSubtotalCreate = 398 Public Const xlDialogSummaryInfo = 474 Public Const xlDialogTable = 41 Public Const xlDialogTabOrder = 394 Public Const xlDialogTextToColumns = 422 Public Const xlDialogUnhide = 94 Public Const xlDialogUpdateLink = 201 Public Const xlDialogVbaInsertFile = 328 Public Const xlDialogVbaMakeAddin = 478 Public Const xlDialogVbaProcedureDefinition = 330 Public Const xlDialogView3d = 197 Public Const xlDialogWindowMove = 14 Public Const xlDialogWindowSize = 13 Public Const xlDialogWorkbookAdd = 281 Public Const xlDialogWorkbookCopy = 283 Public Const xlDialogWorkbookInsert = 354 Public Const xlDialogWorkbookMove = 282 Public Const xlDialogWorkbookName = 386 Public Const xlDialogWorkbookNew = 302 Public Const xlDialogWorkbookOptions = 284 Public Const xlDialogWorkbookProtect = 417 Public Const xlDialogWorkbookTabSplit = 415 Public Const xlDialogWorkbookUnhide = 384 Public Const xlDialogWorkgroup = 199 Public Const xlDialogWorkspace = 95 Public Const xlDialogZoom = 256 Public Const xlDiamond = 2 Public Const xlDIF = 9 Public Const xlDifferenceFrom = 2 Public Const xlDirect = 1 Public Const xlDisabled = 0 Public Const XlDisplayShapes = -4104 Public Const xlDistributed = -4117 Public Const xlDivide = 5 Public Const xlDoNotSaveChanges = 2 Public Const xlDot = -4118 Public Const xlDouble = -4119 Public Const xlDoubleAccounting = 5 Public Const xlDoubleClosed = 5 Public Const xlDoubleOpen = 4 Public Const xlDoubleQuote = 1 Public Const xlDoughnut = -4120 Public Const xlDoughnutExploded = 80 Public Const xlDown = -4121 Public Const xlDownBars = 20 Public Const xlDownThenOver = 1 Public Const xlDownward = -4170 Public Const xlDrawingObject = 14 Public Const xlDropDown = 2 Public Const xlDropLines = 26 Public Const xlDRW = 4 Public Const xlDXF = 5 Public Const xlEdgeBottom = 9 Public Const xlEdgeLeft = 7 Public Const xlEdgeRight = 10 Public Const xlEdgeTop = 8 Public Const xlEditBox = 3 Public Const xlEditionDate = 2 Public Const xlEnd = 2 Public Const xlEndSides = 3 Public Const xlEntireChart = 20 Public Const xlEPS = 8 Public Const xlEqual = 3 Public Const xlErrDiv0 = 2007 Public Const xlErrNA = 2042 Public Const xlErrName = 2029 Public Const xlErrNull = 2000 Public Const xlErrNum = 2036 Public Const xlErrorBarIncludeBoth = 1 Public Const xlErrorBarIncludeMinusValues = 3 Public Const xlErrorBarIncludeNone = -4142 Public Const xlErrorBarIncludePlusValues = 2 Public Const xlErrorBars = 9 Public Const xlErrorBarTypeCustom = -4114 Public Const xlErrorBarTypeFixedValue = 1 Public Const xlErrorBarTypePercent = 2 Public Const xlErrorBarTypeStDev = -4155 Public Const xlErrorBarTypeStError = 4 Public Const xlErrorHandler = 2 Public Const xlErrors = 16 Public Const xlErrRef = 2023 Public Const xlErrValue = 2015 Public Const xlExcel2 = 16 Public Const xlExcel2FarEast = 27 Public Const xlExcel3 = 29 Public Const xlExcel4 = 33 Public Const xlExcel4IntlMacroSheet = 4 Public Const xlExcel4MacroSheet = 3 Public Const xlExcel4Workbook = 35 Public Const xlExcel5 = 39 Public Const xlExcel7 = 39 Public Const xlExcel9795 = 43 Public Const xlExcelLinks = 1 Public Const xlExcelMenus = 1 Public Const xlExclusive = 3 Public Const xlExponential = 5 Public Const xlExpression = 2 Public Const xlExtended = 3 Public Const xlExternal = 2 Public Const xlFill = 5 Public Const xlFillCopy = 1 Public Const xlFillDays = 5 Public Const xlFillDefault = 0 Public Const xlFillFormats = 3 Public Const xlFillMonths = 7 Public Const xlFillSeries = 2 Public Const xlFillValues = 4 Public Const xlFillWeekdays = 6 Public Const xlFillWithAll = -4104 Public Const xlFillWithContents = 2 Public Const xlFillWithFormats = -4122 Public Const xlFillYears = 8 Public Const xlFilterCopy = 2 Public Const xlFilterInPlace = 1 Public Const xlFirst = 0 Public Const xlFitToPage = 2 Public Const xlFixedValue = 1 Public Const xlFixedWidth = 2 Public Const xlFloating = 5 Public Const xlFloor = 23 Public Const xlFormats = -4122 Public Const xlFormula = 5 Public Const xlFormulas = -4123 Public Const xlFreeFloating = 3 Public Const xlFront = 4 Public Const xlFrontEnd = 6 Public Const xlFrontSides = 5 Public Const xlFullPage = 3 Public Const xlFunction = 1 Public Const xlGeneral = 1 Public Const xlGeneralFormatName = 26 Public Const xlGray16 = 17 Public Const xlGray25 = -4124 Public Const xlGray50 = -4125 Public Const xlGray75 = -4126 Public Const xlGray8 = 18 Public Const xlGreater = 5 Public Const xlGreaterEqual = 7 Public Const xlGregorian = 2 Public Const xlGrid = 15 Public Const xlGridline = 22 Public Const xlGroupBox = 4 Public Const xlGrowth = 2 Public Const xlGrowthTrend = 10 Public Const xlGuess = 0 Public Const xlHairline = 1 Public Const xlHAlignCenter = -4108 Public Const xlHAlignCenterAcrossSelection = 7 Public Const xlHAlignDistributed = -4117 Public Const xlHAlignFill = 5 Public Const xlHAlignGeneral = 1 Public Const xlHAlignJustify = -4130 Public Const xlHAlignLeft = -4131 Public Const xlHAlignRight = -4152 Public Const xlHGL = 6 Public Const xlHidden = 0 Public Const xlHide = 3 Public Const xlHigh = -4127 Public Const xlHiLoLines = 25 Public Const xlHindiNumerals = 3 Public Const xlHiragana = 2 Public Const xlHorizontal = -4128 Public Const xlHourCode = 22 Public Const xlIBeam = 3 Public Const xlIcons = 1 Public Const xlIMEModeAlpha = 8 Public Const xlIMEModeAlphaFull = 7 Public Const xlIMEModeDisable = 3 Public Const xlIMEModeHangul = 10 Public Const xlIMEModeHangulFull = 9 Public Const xlIMEModeHiragana = 4 Public Const xlIMEModeKatakana = 5 Public Const xlIMEModeKatakanaHalf = 6 Public Const xlIMEModeNoControl = 0 Public Const xlIMEModeOff = 2 Public Const xlIMEModeOn = 1 Public Const xlImmediatePane = 12 Public Const xlIndex = 9 Public Const xlInfo = -4129 Public Const xlInsertDeleteCells = 1 Public Const xlInsertEntireRows = 2 Public Const xlInside = 2 Public Const xlInsideHorizontal = 12 Public Const xlInsideVertical = 11 Public Const xlInteger = 2 Public Const xlInterpolated = 3 Public Const xlInterrupt = 1 Public Const xlIntlAddIn = 26 Public Const xlIntlMacro = 25 Public Const xlJustify = -4130 Public Const xlKatakana = 1 Public Const xlKatakanaHalf = 0 Public Const xlLabel = 5 Public Const xlLabelOnly = 1 Public Const xlLabelPositionAbove = 0 Public Const xlLabelPositionBelow = 1 Public Const xlLabelPositionBestFit = 5 Public Const xlLabelPositionCenter = -4108 Public Const xlLabelPositionCustom = 7 Public Const xlLabelPositionInsideBase = 4 Public Const xlLabelPositionInsideEnd = 3 Public Const xlLabelPositionLeft = -4131 Public Const xlLabelPositionMixed = 6 Public Const xlLabelPositionOutsideEnd = 2 Public Const xlLabelPositionRight = -4152 Public Const xlLandscape = 2 Public Const xlLast = 1 Public Const xlLastCell = 11 Public Const xlLatin = -5001 Public Const xlLeaderLines = 29 Public Const xlLeft = -4131 Public Const xlLeftBrace = 12 Public Const xlLeftBracket = 10 Public Const xlLeftToRight = 2 Public Const xlLegend = 24 Public Const xlLegendEntry = 12 Public Const xlLegendKey = 13 Public Const xlLegendPositionBottom = -4107 Public Const xlLegendPositionCorner = 2 Public Const xlLegendPositionLeft = -4131 Public Const xlLegendPositionRight = -4152 Public Const xlLegendPositionTop = -4160 Public Const xlLess = 6 Public Const xlLessEqual = 8 Public Const xlLightDown = 13 Public Const xlLightHorizontal = 11 Public Const xlLightUp = 14 Public Const xlLightVertical = 12 Public Const xlLine = 4 Public Const xlLinear = -4132 Public Const xlLinearTrend = 9 Public Const xlLineMarkers = 65 Public Const xlLineMarkersStacked = 66 Public Const xlLineMarkersStacked100 = 67 Public Const xlLineStacked = 63 Public Const xlLineStacked100 = 64 Public Const xlLineStyleNone = -4142 Public Const xlLinkInfoOLELinks = 2 Public Const xlLinkInfoPublishers = 5 Public Const xlLinkInfoSubscribers = 6 Public Const xlLinkTypeExcelLinks = 1 Public Const xlLinkTypeOLELinks = 2 Public Const xlList1 = 10 Public Const xlList2 = 11 Public Const xlList3 = 12 Public Const xlListBox = 6 Public Const xlListSeparator = 5 Public Const xlLocalFormat1 = 15 Public Const xlLocalFormat2 = 16 Public Const xlLocalSessionChanges = 2 Public Const xlLocationAsNewSheet = 1 Public Const xlLocationAsObject = 2 Public Const xlLocationAutomatic = 3 Public Const xlLogarithmic = -4133 Public Const xlLogical = 4 Public Const xlLogicalCursor = 1 Public Const xlLong = 3 Public Const xlLotusHelp = 2 Public Const xlLow = -4134 Public Const xlLowerCaseColumnLetter = 9 Public Const xlLowerCaseRowLetter = 8 Public Const xlLTR = -5003 Public Const xlMacintosh = 1 Public Const xlMacrosheetCell = 7 Public Const xlMajorGridlines = 15 Public Const xlManual = -4135 Public Const xlManualUpdate = 5 Public Const xlMAPI = 1 Public Const xlMarkerStyleAutomatic = -4105 Public Const xlMarkerStyleCircle = 8 Public Const xlMarkerStyleDash = -4115 Public Const xlMarkerStyleDiamond = 2 Public Const xlMarkerStyleDot = -4118 Public Const xlMarkerStyleNone = -4142 Public Const xlMarkerStylePicture = -4147 Public Const xlMarkerStylePlus = 9 Public Const xlMarkerStyleSquare = 1 Public Const xlMarkerStyleStar = 5 Public Const xlMarkerStyleTriangle = 3 Public Const xlMarkerStyleX = -4168 Public Const xlMax = -4136 Public Const xlMaximized = -4137 Public Const xlMaximum = 2 Public Const xlMDY = 44 Public Const xlMedium = -4138 Public Const xlMetric = 35 Public Const xlMicrosoftAccess = 4 Public Const xlMicrosoftFoxPro = 5 Public Const xlMicrosoftMail = 3 Public Const xlMicrosoftPowerPoint = 2 Public Const xlMicrosoftProject = 6 Public Const xlMicrosoftSchedulePlus = 7 Public Const xlMicrosoftWord = 1 Public Const xlMin = -4139 Public Const xlMinimized = -4140 Public Const xlMinimum = 4 Public Const xlMinorGridlines = 16 Public Const xlMinusValues = 3 Public Const xlMinuteCode = 23 Public Const xlMixed = 2 Public Const xlMixedLabels = 3 Public Const xlModule = -4141 Public Const xlMonth = 3 Public Const xlMonthCode = 20 Public Const xlMonthLeadingZero = 41 Public Const xlMonthNameChars = 30 Public Const xlMonths = 1 Public Const xlMove = 2 Public Const xlMoveAndSize = 1 Public Const xlMovingAvg = 6 Public Const xlMSDOS = 3 Public Const xlMultiply = 4 Public Const xlNarrow = 1 Public Const xlNext = 1 Public Const xlNextToAxis = 4 Public Const xlNo = 2 Public Const xlNoAdditionalCalculation = -4143 Public Const xlNoButton = 0 Public Const xlNoButtonChanges = 1 Public Const xlNoCap = 2 Public Const xlNoChange = 1 Public Const xlNoChanges = 4 Public Const xlNoConversion = 3 Public Const xlNoDockingChanges = 3 Public Const xlNoDocuments = 3 Public Const xlNoIndicator = 0 Public Const xlNoLabels = -4142 Public Const xlNoMailSystem = 0 Public Const xlNoncurrencyDigits = 29 Public Const xlNone = -4142 Public Const xlNonEnglishFunctions = 34 Public Const xlNoRestrictions = 0 Public Const xlNormal = -4143 Public Const xlNormalView = 1 Public Const xlNorthwestArrow = 1 Public Const xlNoSelection = -4142 Public Const xlNoShapeChanges = 2 Public Const xlNotBetween = 2 Public Const xlNotEqual = 4 Public Const xlNotes = -4144 Public Const xlNothing = 28 Public Const xlNotPlotted = 1 Public Const xlNotXLM = 3 Public Const xlNotYetReviewed = 3 Public Const xlNotYetRouted = 0 Public Const xlNumber = -4145 Public Const xlNumbers = 1 Public Const xlOff = -4146 Public Const xlOLEControl = 2 Public Const xlOLEEmbed = 1 Public Const xlOLELink = 0 Public Const xlOLELinks = 2 Public Const xlOn = 1 Public Const xlOneAfterAnother = 1 Public Const xlOpaque = 3 Public Const xlOpen = 2 Public Const xlOpenSource = 3 Public Const xlOptionButton = 7 Public Const xlOr = 2 Public Const xlOrigin = 3 Public Const xlOtherSessionChanges = 3 Public Const xlOutside = 3 Public Const xlOverThenDown = 2 Public Const xlOverwriteCells = 0 Public Const xlPageBreakAutomatic = -4105 Public Const xlPageBreakFull = 1 Public Const xlPageBreakManual = -4135 Public Const xlPageBreakPartial = 2 Public Const xlPageBreakPreview = 2 Public Const xlPageField = 3 Public Const xlPageHeader = 2 Public Const xlPageItem = 6 Public Const xlPaper10x14 = 16 Public Const xlPaper11x17 = 17 Public Const xlPaperA3 = 8 Public Const xlPaperA4 = 9 Public Const xlPaperA4Small = 10 Public Const xlPaperA5 = 11 Public Const xlPaperB4 = 12 Public Const xlPaperB5 = 13 Public Const xlPaperCsheet = 24 Public Const xlPaperDsheet = 25 Public Const xlPaperEnvelope10 = 20 Public Const xlPaperEnvelope11 = 21 Public Const xlPaperEnvelope12 = 22 Public Const xlPaperEnvelope14 = 23 Public Const xlPaperEnvelope9 = 19 Public Const xlPaperEnvelopeB4 = 33 Public Const xlPaperEnvelopeB5 = 34 Public Const xlPaperEnvelopeB6 = 35 Public Const xlPaperEnvelopeC3 = 29 Public Const xlPaperEnvelopeC4 = 30 Public Const xlPaperEnvelopeC5 = 28 Public Const xlPaperEnvelopeC6 = 31 Public Const xlPaperEnvelopeC65 = 32 Public Const xlPaperEnvelopeDL = 27 Public Const xlPaperEnvelopeItaly = 36 Public Const xlPaperEnvelopeMonarch = 37 Public Const xlPaperEnvelopePersonal = 38 Public Const xlPaperEsheet = 26 Public Const xlPaperExecutive = 7 Public Const xlPaperFanfoldLegalGerman = 41 Public Const xlPaperFanfoldStdGerman = 40 Public Const xlPaperFanfoldUS = 39 Public Const xlPaperFolio = 14 Public Const xlPaperLedger = 4 Public Const xlPaperLegal = 5 Public Const xlPaperLetter = 1 Public Const xlPaperLetterSmall = 2 Public Const xlPaperNote = 18 Public Const xlPaperQuarto = 15 Public Const xlPaperStatement = 6 Public Const xlPaperTabloid = 3 Public Const xlPaperUser = 256 Public Const xlParamTypeBigInt = -5 Public Const xlParamTypeBinary = -2 Public Const xlParamTypeBit = -7 Public Const xlParamTypeChar = 1 Public Const xlParamTypeDate = 9 Public Const xlParamTypeDecimal = 3 Public Const xlParamTypeDouble = 8 Public Const xlParamTypeFloat = 6 Public Const xlParamTypeInteger = 4 Public Const xlParamTypeLongVarBinary = -4 Public Const xlParamTypeLongVarChar = -1 Public Const xlParamTypeNumeric = 2 Public Const xlParamTypeReal = 7 Public Const xlParamTypeSmallInt = 5 Public Const xlParamTypeTime = 10 Public Const xlParamTypeTimestamp = 11 Public Const xlParamTypeTinyInt = -6 Public Const xlParamTypeUnknown = 0 Public Const xlParamTypeVarBinary = -3 Public Const xlParamTypeVarChar = 12 Public Const xlPart = 2 Public Const xlPartial = 3 Public Const xlPasteAll = -4104 Public Const xlPasteAllExceptBorders = 6 Public Const xlPasteComments = -4144 Public Const xlPasteFormats = -4122 Public Const xlPasteFormulas = -4123 Public Const xlPasteSpecialOperationAdd = 2 Public Const xlPasteSpecialOperationDivide = 5 Public Const xlPasteSpecialOperationMultiply = 4 Public Const xlPasteSpecialOperationNone = -4142 Public Const xlPasteSpecialOperationSubtract = 3 Public Const xlPasteValues = -4163 Public Const xlPatternAutomatic = -4105 Public Const xlPatternChecker = 9 Public Const xlPatternCrissCross = 16 Public Const xlPatternDown = -4121 Public Const xlPatternGray16 = 17 Public Const xlPatternGray25 = -4124 Public Const xlPatternGray50 = -4125 Public Const xlPatternGray75 = -4126 Public Const xlPatternGray8 = 18 Public Const xlPatternGrid = 15 Public Const xlPatternHorizontal = -4128 Public Const xlPatternLightDown = 13 Public Const xlPatternLightHorizontal = 11 Public Const xlPatternLightUp = 14 Public Const xlPatternLightVertical = 12 Public Const xlPatternNone = -4142 Public Const xlPatternSemiGray75 = 10 Public Const xlPatternSolid = 1 Public Const xlPatternUp = -4162 Public Const xlPatternVertical = -4166 Public Const xlPCT = 13 Public Const xlPCX = 10 Public Const xlPercent = 2 Public Const xlPercentDifferenceFrom = 4 Public Const xlPercentOf = 3 Public Const xlPercentOfColumn = 7 Public Const xlPercentOfRow = 6 Public Const xlPercentOfTotal = 8 Public Const xlPhoneticAlignCenter = 2 Public Const xlPhoneticAlignDistributed = 3 Public Const xlPhoneticAlignLeft = 1 Public Const xlPhoneticAlignNoControl = 0 Public Const xlPIC = 11 Public Const xlPICT = 1 Public Const xlPicture = -4147 Public Const xlPie = 5 Public Const xlPieExploded = 69 Public Const xlPieOfPie = 68 Public Const xlPinYin = 1 Public Const xlPivotTable = -4148 Public Const xlPlaceholders = 2 Public Const xlPlotArea = 19 Public Const xlPLT = 12 Public Const xlPlus = 9 Public Const xlPlusValues = 2 Public Const xlPolynomial = 3 Public Const xlPortrait = 1 Public Const xlPower = 4 Public Const xlPowerTalk = 2 Public Const xlPrevious = 2 Public Const xlPrimary = 1 Public Const xlPrimaryButton = 1 Public Const xlPrinter = 2 Public Const xlPrintInPlace = 16 Public Const xlPrintNoComments = -4142 Public Const xlPrintSheetEnd = 1 Public Const xlPriorityHigh = -4127 Public Const xlPriorityLow = -4134 Public Const xlPriorityNormal = -4143 Public Const xlProduct = -4149 Public Const xlPrompt = 0 Public Const xlPublisher = 1 Public Const xlPublishers = 5 Public Const xlPyramidBarClustered = 109 Public Const xlPyramidBarStacked = 110 Public Const xlPyramidBarStacked100 = 111 Public Const xlPyramidCol = 112 Public Const xlPyramidColClustered = 106 Public Const xlPyramidColStacked = 107 Public Const xlPyramidColStacked100 = 108 Public Const xlPyramidToMax = 2 Public Const xlPyramidToPoint = 1 Public Const xlR1C1 = -4150 Public Const xlRadar = -4151 Public Const xlRadarAxisLabels = 27 Public Const xlRadarFilled = 82 Public Const xlRadarMarkers = 81 Public Const xlRange = 2 Public Const xlRangeAutoFormat3DEffects1 = 13 Public Const xlRangeAutoFormat3DEffects2 = 14 Public Const xlRangeAutoFormatAccounting1 = 4 Public Const xlRangeAutoFormatAccounting2 = 5 Public Const xlRangeAutoFormatAccounting3 = 6 Public Const xlRangeAutoFormatAccounting4 = 17 Public Const xlRangeAutoFormatClassic1 = 1 Public Const xlRangeAutoFormatClassic2 = 2 Public Const xlRangeAutoFormatClassic3 = 3 Public Const xlRangeAutoFormatColor1 = 7 Public Const xlRangeAutoFormatColor2 = 8 Public Const xlRangeAutoFormatColor3 = 9 Public Const xlRangeAutoFormatList1 = 10 Public Const xlRangeAutoFormatList2 = 11 Public Const xlRangeAutoFormatList3 = 12 Public Const xlRangeAutoFormatLocalFormat1 = 15 Public Const xlRangeAutoFormatLocalFormat2 = 16 Public Const xlRangeAutoFormatLocalFormat3 = 19 Public Const xlRangeAutoFormatLocalFormat4 = 20 Public Const xlRangeAutoFormatNone = -4142 Public Const xlRangeAutoFormatSimple = -4154 Public Const xlReadOnly = 3 Public Const xlReadWrite = 2 Public Const xlReference = 4 Public Const xlRelative = 4 Public Const xlRelRowAbsColumn = 3 Public Const xlRight = -4152 Public Const xlRightBrace = 13 Public Const xlRightBracket = 11 Public Const xlRoutingComplete = 2 Public Const xlRoutingInProgress = 1 Public Const xlRowField = 1 Public Const xlRowHeader = -4153 Public Const xlRowItem = 4 Public Const xlRowLabels = 1 Public Const xlRows = 1 Public Const xlRowSeparator = 15 Public Const xlRowThenColumn = 1 Public Const xlRTF = 4 Public Const xlRTL = -5004 Public Const xlRunningTotal = 5 Public Const xlSaveChanges = 1 Public Const xlScale = 3 Public Const xlScaleLinear = -4132 Public Const xlScaleLogarithmic = -4133 Public Const xlScreen = 1 Public Const xlScreenSize = 1 Public Const xlScrollBar = 8 Public Const xlSecondary = 2 Public Const xlSecondaryButton = 2 Public Const xlSecondCode = 24 Public Const xlSelect = 3 Public Const xlSemiautomatic = 2 Public Const xlSemiGray75 = 10 Public Const xlSendPublisher = 2 Public Const xlSeries = 3 Public Const xlSeriesAxis = 3 Public Const xlSeriesLines = 22 Public Const xlShape = 14 Public Const xlShared = 2 Public Const xlSheetHidden = 0 Public Const xlSheetVeryHidden = 2 Public Const xlSheetVisible = -1 Public Const xlShiftDown = -4121 Public Const xlShiftToLeft = -4159 Public Const xlShiftToRight = -4161 Public Const xlShiftUp = -4162 Public Const xlShort = 1 Public Const xlShowLabel = 4 Public Const xlShowLabelAndPercent = 5 Public Const xlShowPercent = 3 Public Const xlShowValue = 2 Public Const xlSides = 1 Public Const xlSimple = -4154 Public Const xlSinceMyLastSave = 1 Public Const xlSingle = 2 Public Const xlSingleAccounting = 4 Public Const xlSingleQuote = 2 Public Const xlSizeIsArea = 1 Public Const xlSizeIsWidth = 2 Public Const xlSlantDashDot = 13 Public Const xlSolid = 1 Public Const xlSortColumns = 1 Public Const xlSortLabels = 2 Public Const xlSortRows = 2 Public Const xlSortValues = 1 Public Const xlSpinner = 9 Public Const xlSplitByCustomSplit = 4 Public Const xlSplitByPercentValue = 3 Public Const xlSplitByPosition = 1 Public Const xlSplitByValue = 2 Public Const xlSquare = 1 Public Const xlStack = 2 Public Const xlStackScale = 3 Public Const xlStandardSummary = 1 Public Const xlStar = 5 Public Const xlStDev = -4155 Public Const xlStDevP = -4156 Public Const xlStError = 4 Public Const xlStockHLC = 88 Public Const xlStockOHLC = 89 Public Const xlStockVHLC = 90 Public Const xlStockVOHLC = 91 Public Const xlStretch = 1 Public Const xlStrict = 2 Public Const xlStroke = 2 Public Const xlSubscriber = 2 Public Const xlSubscribers = 6 Public Const xlSubscribeToPicture = -4147 Public Const xlSubscribeToText = -4158 Public Const xlSubtract = 3 Public Const xlSum = -4157 Public Const xlSummaryAbove = 0 Public Const xlSummaryBelow = 1 Public Const xlSummaryOnLeft = -4131 Public Const xlSummaryOnRight = -4152 Public Const xlSummaryPivotTable = -4148 Public Const xlSurface = 83 Public Const xlSurfaceTopView = 85 Public Const xlSurfaceTopViewWireframe = 86 Public Const xlSurfaceWireframe = 84 Public Const xlSYLK = 2 Public Const xlSyllabary = 1 Public Const xlSystem = 1 Public Const xlTableBody = 8 Public Const xlTabPositionFirst = 0 Public Const xlTabPositionLast = 1 Public Const xlTemplate = 17 Public Const xlText = -4158 Public Const xlTextBox = 16 Public Const xlTextMac = 19 Public Const xlTextMSDOS = 21 Public Const xlTextPrinter = 36 Public Const xlTextQualifierDoubleQuote = 1 Public Const xlTextQualifierNone = -4142 Public Const xlTextQualifierSingleQuote = 2 Public Const xlTextValues = 2 Public Const xlTextWindows = 20 Public Const xlThick = 4 Public Const xlThin = 2 Public Const xlThousandsSeparator = 4 Public Const xlTickLabelOrientationAutomatic = -4105 Public Const xlTickLabelOrientationDownward = -4170 Public Const xlTickLabelOrientationHorizontal = -4128 Public Const xlTickLabelOrientationUpward = -4171 Public Const xlTickLabelOrientationVertical = -4166 Public Const xlTickLabelPositionHigh = -4127 Public Const xlTickLabelPositionLow = -4134 Public Const xlTickLabelPositionNextToAxis = 4 Public Const xlTickLabelPositionNone = -4142 Public Const xlTickMarkCross = 4 Public Const xlTickMarkInside = 2 Public Const xlTickMarkNone = -4142 Public Const xlTickMarkOutside = 3 Public Const xlTIF = 9 Public Const xlTiled = 1 Public Const xlTimeLeadingZero = 45 Public Const xlTimeScale = 3 Public Const xlTimeSeparator = 18 Public Const xlTitleBar = 8 Public Const xlToLeft = -4159 Public Const xlToolbar = 1 Public Const xlToolbarButton = 2 Public Const xlToolbarProtectionNone = -4143 Public Const xlTop = -4160 Public Const xlTop10Items = 3 Public Const xlTop10Percent = 5 Public Const xlTopToBottom = 1 Public Const xlToRight = -4161 Public Const xlTransparent = 2 Public Const xlTrendline = 8 Public Const xlTriangle = 3 Public Const xlUnderlineStyleDouble = -4119 Public Const xlUnderlineStyleDoubleAccounting = 5 Public Const xlUnderlineStyleNone = -4142 Public Const xlUnderlineStyleSingle = 2 Public Const xlUnderlineStyleSingleAccounting = 4 Public Const xlUnlockedCells = 1 Public Const xlUp = -4162 Public Const xlUpBars = 18 Public Const xlUpdateState = 1 Public Const xlUpdateSubscriber = 2 Public Const xlUpperCaseColumnLetter = 7 Public Const xlUpperCaseRowLetter = 6 Public Const xlUpward = -4171 Public Const xlUserDefined = 22 Public Const xlUserResolution = 1 Public Const xlValidAlertInformation = 3 Public Const xlValidAlertStop = 1 Public Const xlValidAlertWarning = 2 Public Const xlValidateCustom = 7 Public Const xlValidateDate = 4 Public Const xlValidateDecimal = 2 Public Const xlValidateInputOnly = 0 Public Const xlValidateList = 3 Public Const xlValidateTextLength = 6 Public Const xlValidateTime = 5 Public Const xlValidateWholeNumber = 1 Public Const xlVAlignBottom = -4107 Public Const xlVAlignCenter = -4108 Public Const xlVAlignDistributed = -4117 Public Const xlVAlignJustify = -4130 Public Const xlVAlignTop = -4160 Public Const xlVALU = 8 Public Const xlValue = 2 Public Const xlValues = -4163 Public Const xlVar = -4164 Public Const xlVarP = -4165 Public Const xlVerbOpen = 2 Public Const xlVerbPrimary = 1 Public Const xlVertical = -4166 Public Const xlVeryHidden = 2 Public Const xlVisible = 12 Public Const xlVisualCursor = 2 Public Const xlWait = 2 Public Const xlWalls = 5 Public Const xlWatchPane = 11 Public Const xlWBATChart = -4109 Public Const xlWBATExcel4IntlMacroSheet = 4 Public Const xlWBATExcel4MacroSheet = 3 Public Const xlWBATWorksheet = -4167 Public Const xlWeekday = 2 Public Const xlWeekdayNameChars = 31 Public Const xlWhole = 1 Public Const xlWide = 3 Public Const xlWindows = 2 Public Const xlWJ2WD1 = 14 Public Const xlWJ3 = 40 Public Const xlWJ3FJ3 = 41 Public Const xlWK1 = 5 Public Const xlWK1ALL = 31 Public Const xlWK1FMT = 30 Public Const xlWK3 = 15 Public Const xlWK3FM3 = 32 Public Const xlWK4 = 38 Public Const xlWKS = 4 Public Const xlWMF = 2 Public Const xlWorkbook = 1 Public Const xlWorkbookNormal = -4143 Public Const xlWorkbookTab = 6 Public Const xlWorks2FarEast = 28 Public Const xlWorksheet = -4167 Public Const xlWorksheet4 = 1 Public Const xlWorksheetCell = 3 Public Const xlWorksheetShort = 5 Public Const xlWPG = 3 Public Const xlWQ1 = 34 Public Const xlX = -4168 Public Const xlXErrorBars = 10 Public Const xlXYScatter = -4169 Public Const xlXYScatterLines = 74 Public Const xlXYScatterLinesNoMarkers = 75 Public Const xlXYScatterSmooth = 72 Public Const xlXYScatterSmoothNoMarkers = 73 Public Const xlY = 1 Public Const xlYear = 4 Public Const xlYearCode = 19 Public Const xlYears = 2 Public Const xlYErrorBars = 11 Public Const xlYes = 1 Public Const xlZero = 2 Public Const msoFalse = 0 Public Const msoScaleFromBottomRight = 2 Public Const msoScaleFromTopLeft = 0  SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Hello again all, > I am stuggling with the syntax of VBA in Access versus VBA in Excel, so [quoted text clipped - 18 lines] > 'Line below creates error > objOpen.Range("A2:Q" & LastRow3).PasteSpecial (xlPasteValues) Dave, Thank you for your quick response, I'm still getting an error 1004, PasteSpecial method of Range class failed. In regards to your public const, If I copy this to a module, will I be able to use my Excel VBA in Access VBA? > The named arguments of excel aren't natively exposed. Give this a go. > [quoted text clipped - 295 lines] > Public Const xlDialogAlignment = 43 > Public Const xlDialogApplyNames = 133 Does the code work in excel module? Probably. Depends on how you're coding. I usually do something along the lines of; Dim oExcel As Object, oSheet As Object If IsExecuting("excel.exe") = True Then Set oExcel = GetObject(, "Excel.Application") Else Set oExcel = CreateObject("Excel.Application") End If oExcel.Workbooks.Open "C:\Data\Excel\somefile.xls" Public Function IsExecuting(sProc) Dim list As Object Set list = GetObject("winmgmts:").ExecQuery( _ "select * from win32_process where name='" & sProc & "'") If list.Count > 0 Then IsExecuting = True End Function SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Dave, > Thank you for your quick response, [quoted text clipped - 4 lines] > able > to use my Excel VBA in Access VBA? Perhaps I'm not even close enough to properly ask the question. The Access VBA code seems to work to delete the contents, just having a hard time copying and pasting to the OPEN and CLOSED tabs. Here is simple Excel code that works in Excel, Sheets("Open1").Select Cells.Select Selection.Cut Sheets("Open").Select Cells.Select ActiveSheet.Paste Sheets("Closed1").Select Sheets("Closed1").Name = "Closed1" Cells.Select Selection.Cut Sheets("Closed").Select Cells.Select ActiveSheet.Paste Here is all of by Access VBA code. (I hope this helps) On Error GoTo HandleError ' Object variables for Automation stuff Dim objXLApp As Excel.Application Dim objXLBook As Excel.Workbook Dim objTicketSummary As Excel.Worksheet Dim objOpen As Excel.Worksheet Dim objClosed As Excel.Worksheet Dim objSummaryChart As Excel.Worksheet Dim objSummaryReport As Excel.Worksheet Dim objOpen1 As Excel.Worksheet Dim objClosed1 As Excel.Worksheet Dim objqry_Release_Open1 As Excel.Worksheet Dim objqry_Release_Open2 As Excel.Worksheet Dim objqry_Release_Closed1 As Excel.Worksheet Dim objqry_Release_Closed2 As Excel.Worksheet Dim xlApp As Object 'Application Object Dim xlBook As Object 'Workbook Object Dim xlSheet As Object 'Worksheet Object Dim objxlSheet As Object 'Worksheet Object Dim objXLRange As Excel.Range Dim SQL As String Dim strFilter As String Dim strInputFileName As String Dim strTempFile As String Dim LastRow, LastRow2, LastRow3 As Integer Dim i, ii, iii As Integer ' ADO and other variables Dim cmdLost As ADODB.Command Dim rstLost As ADODB.Recordset Dim strSQL As String Dim varResults As Variant Dim varIATACode As Variant Dim varStart As Variant Dim varEnd As Variant Dim intCount As Integer 'this will prompt user to select excel file to import strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS") strInputFileName = ahtCommonFileOpenSave( _ Filter:=strFilter, OpenFile:=True, _ DialogTitle:="Please select a file for export...", _ Flags:=ahtOFN_HIDEREADONLY) Forms!frm_Milestone_Release.txt_InputFile = strInputFileName DoCmd.OpenForm "frm_Processing" Forms!frm_Milestone_Release.Refresh Forms!frm_Milestone_Release.Repaint Forms!frm_Processing.Refresh Forms!frm_Processing.Repaint strTempFile = Application.CurrentProject.Path & "\temp_Release_Ticket_Log.xls" DoCmd.SetWarnings False 'docmd.TransferSpreadsheet acExport,, DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Release_Open_Tickets_Crosstab_Apl_ID2", strTempFile, True, "qry_Release_Open1" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Release_Open_Tickets_Crosstab_Projects2", strTempFile, True, "qry_Release_Open2" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Release_Closed_Tickets_Crosstab_Apl_ID2", strTempFile, True, "qry_Release_Closed1" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Release_Closed_Tickets_Crosstab_Projects2", strTempFile, True, "qry_Release_Closed2" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Release_Open", strTempFile, True, "Open" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Release_Closed", strTempFile, True, "Closed" DoCmd.SetWarnings True ' Go ahead and create an object from the XLS file Set objXLBook = GetObject( _ Application.CurrentProject.Path & "\temp_Release_Ticket_Log.xls") ' We can use the Parent ' property of the workbook object ' to get a pointer to Excel's ' Application object Set objXLApp = objXLBook.Parent ' Set object references for the ' workbook's worksheets Set objTicketSummary = objXLBook. _ Worksheets("Ticket Summary") 'clear contents of Open tab Set objOpen = objXLBook. _ Worksheets("Open") LastRow3 = objOpen.UsedRange.Rows.Count objOpen.Range("2:" & LastRow3).ClearContents 'Clear contents of Closed tab Set objClosed = objXLBook. _ Worksheets("Closed") LastRow2 = objClosed.UsedRange.Rows.Count objClosed.Range("2:" & LastRow2).ClearContents '**** Problem with the below code ********* 'copy contents from Open1 tab to Open tab Set objOpen1 = objXLBook. _ Worksheets("Open1") LastRow3 = objOpen1.UsedRange.Rows.Count ' objOpen1.Range("A2:Q" & LastRow3).Copy objOpen1.Range("A2:Q2").Copy ' objOpen.Range("A2:Q2").PasteSpecial -4163 objXLApp.CutCopyMode = False > Does the code work in excel module? > [quoted text clipped - 25 lines] > > able > > to use my Excel VBA in Access VBA? I agree with Roy you must use .Copy not .Cut SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Perhaps I'm not even close enough to properly ask the question. > [quoted text clipped - 134 lines] > > objXLApp.CutCopyMode = False "David" <David@discussions.microsoft.com> wrote in message <4D6D6E70-3CF3-4D61-871B-A3D1DF785DBC@microsoft.com>: > Hello again all, > I am stuggling with the syntax of VBA in Access versus VBA in Excel, [quoted text clipped - 17 lines] > 'Line below creates error > objOpen.Range("A2:Q" & LastRow3).PasteSpecial (xlPasteValues) I think the problem is, that you can't paste special after cut, only after copy. The workaround here, could be using .Copy instead of cut, then .ClearContents afterwards. SignatureRoy-Vidar "RoyVidar" <roy_vidarNOSPAM@yahoo.no> wrote in message <mn.ccf37d6bd1c641f0.59509@yahoo.no>: > "David" <David@discussions.microsoft.com> wrote in message > <4D6D6E70-3CF3-4D61-871B-A3D1DF785DBC@microsoft.com>: [quoted text clipped - 23 lines] > after copy. The workaround here, could be using .Copy instead of cut, > then .ClearContents afterwards. Oh, and if you haven't done so, you might want to issue a YourXlObjectVariable.CutCopyMode = False prior to closing, to avoid the "There is a large amound of information on the Clipboard..." message. SignatureRoy-Vidar Roy, Thank you, I modified it to this, unfortunately same error. Any guidence is greatly appreciated. 'copy contents from Open1 tab to Open tab Set objOpen1 = objXLBook. _ Worksheets("Open1") LastRow3 = objOpen1.UsedRange.Rows.Count objOpen1.Range("A2:Q" & LastRow3).Copy objOpen.Range("A2:Q" & LastRow3).PasteSpecial -4163 objXLApp.CutCopyMode = False > "David" <David@discussions.microsoft.com> wrote in message > <4D6D6E70-3CF3-4D61-871B-A3D1DF785DBC@microsoft.com>: [quoted text clipped - 23 lines] > after copy. The workaround here, could be using .Copy instead of cut, > then .ClearContents afterwards. This worked fine for me. Dim oExcel As Object, oSheet As Object Dim LastRow3 As Integer LastRow3 = 3 Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Open "C:\book1.xls" Set oSheet = oExcel.ActiveWorkbook.Worksheets(1) oSheet.Range("B1:C" & LastRow3).Copy oSheet.Range("B1:C" & LastRow3).PasteSpecial -4163 oExcel.CutCopyMode = False oExcel.ActiveWorkbook.Save oExcel.ActiveWorkbook.Close oExcel.Quit Set oSheet = Nothing Set oExcel = Nothing SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Roy, > Thank you, [quoted text clipped - 38 lines] >> after copy. The workaround here, could be using .Copy instead of cut, >> then .ClearContents afterwards. Dave, Thank you, this also works for me. Thank you. Now I just need to match my variables to your code and use the existing instance of Excel and existing file that is opened with the code prior to this. > This worked fine for me. > [quoted text clipped - 55 lines] > >> after copy. The workaround here, could be using .Copy instead of cut, > >> then .ClearContents afterwards. Give this a go. Public Sub aaa() Dim oExcel As Object, oWB As Object, oSheet As Object Dim running As Boolean Dim LastRow3 As Integer LastRow3 = 3 running = False If IsExecuting("excel.exe") = True Then Set oExcel = GetObject(, "Excel.Application") running = True Else Set oExcel = CreateObject("Excel.Application") End If Set oWB = oExcel.Workbooks.Open("C:\book1.xls") 'set WB = getobject("c:\test.xls") Set oSheet = oWB.Worksheets(1) 'Set oSheet = oExcel.Worksheets(1) oSheet.Range("B1:C" & LastRow3).Copy oSheet.Range("B1:C" & LastRow3).PasteSpecial -4163 oExcel.CutCopyMode = False 'oExcel.Workbooks("C:\book1.xls").Activate oWB.Save oWB.Close If running = False Then oExcel.Quit Set oSheet = Nothing Set oWB = Nothing Set oExcel = Nothing End Sub Public Function IsExecuting(sProc) Dim list As Object Set list = GetObject("winmgmts:").ExecQuery( _ "select * from win32_process where name='" & sProc & "'") If list.Count > 0 Then IsExecuting = True End Function SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Dave, > Thank you, this also works for me. Thank you. > > Now I just need to match my variables to your code and use the existing > instance of Excel and existing file that is opened with the code prior to > this. Oops, here it is cleaned up. Public Sub aaa() Dim oExcel As Object, oWB As Object, oSheet As Object Dim running As Boolean Dim LastRow3 As Integer LastRow3 = 3 running = False If IsExecuting("excel.exe") = True Then Set oExcel = GetObject(, "Excel.Application") running = True Else Set oExcel = CreateObject("Excel.Application") End If Set oWB = oExcel.Workbooks.Open("C:\book1.xls") Set oSheet = oWB.Worksheets(1) oSheet.Range("B1:C" & LastRow3).Copy oSheet.Range("B1:C" & LastRow3).PasteSpecial -4163 oExcel.CutCopyMode = False oWB.Save oWB.Close If running = False Then oExcel.Quit Set oSheet = Nothing Set oWB = Nothing Set oExcel = Nothing End Sub Public Function IsExecuting(sProc) Dim list As Object Set list = GetObject("winmgmts:").ExecQuery( _ "select * from win32_process where name='" & sProc & "'") If list.Count > 0 Then IsExecuting = True End Function SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect Dave, Thank you for helping me with this. I isolated the code in question to a unique module and turns out the code prior to the code we were working with left the excel file in hidden mode. Once I cleaned that up, the code works great. Thank you again for all of your help. > Oops, here it is cleaned up. > [quoted text clipped - 29 lines] > If list.Count > 0 Then IsExecuting = True > End Function Good to hear it's sorted. SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Dave, > Thank you for helping me with this. I isolated the code in question to a [quoted text clipped - 5 lines] > > Thank you again for all of your help. "David" <David@discussions.microsoft.com> wrote in message <718BB471-1FAF-4DB9-A429-BD6443041B41@microsoft.com>: > Roy, > Thank you, [quoted text clipped - 15 lines] >> after copy. The workaround here, could be using .Copy instead of >> cut, then .ClearContents afterwards. I don't really know. Your code, or very similar code, works without flaws on my setup. Copy/paste operations, hovewer can be a bit troublesome - and demanding on recourses. See if you can use this in stead LastRow3 = objOpen1.UsedRange.Rows.Count objOpen.Range("A2:Q" & LastRow3) = _ objOpen1.Range("A2:Q" & LastRow3).Value If this doesn't work either, then I'm inclined to believe the root of this challenge to be something other than the copy/paste code (which is one of the more amusing "features" of automation ;-) ). Try to eliminate possible reasons by leaving only what little code is needed to perform this copy/paste operation - make it work - and add code until you find the culprit, then let's deal with that. One sure sign that there's something else going on, would be if you've got an extra instance of Excel in memory (check with Task Manager). SignatureRoy-Vidar Roy, Thank you for your assistance. I inserted Dave's code and it worked when opening the c:\book1.xls file. I used this code to close the excel file before Dave's code, but still see an instance of Excel in task manager. (no excel.exe listed prior to code execution). Is there any better way to close out Excel? objXLBook.Save objXLBook.Close objXLApp.Quit Here are the ton of variables currently defined. Dim objXLApp As Excel.Application Dim objXLBook As Excel.Workbook Dim objTicketSummary As Excel.Worksheet Dim objOpen As Excel.Worksheet Dim objClosed As Excel.Worksheet Dim objSummaryChart As Excel.Worksheet Dim objSummaryReport As Excel.Worksheet Dim objOpen1 As Excel.Worksheet Dim objClosed1 As Excel.Worksheet Dim objqry_Release_Open1 As Excel.Worksheet Dim objqry_Release_Open2 As Excel.Worksheet Dim objqry_Release_Closed1 As Excel.Worksheet Dim objqry_Release_Closed2 As Excel.Worksheet Dim xlApp As Object 'Application Object Dim xlBook As Object 'Workbook Object Dim xlSheet As Object 'Worksheet Object Dim objxlSheet As Object 'Worksheet Object Dim objXLRange As Excel.Range Dim SQL As String Dim strFilter As String Dim strInputFileName As String Dim strTempFile As String Dim LastRow, LastRow2, LastRow3 As Integer Dim i, ii, iii As Integer ' ADO and other variables Dim cmdLost As ADODB.Command Dim rstLost As ADODB.Recordset Dim strSQL As String Dim varResults As Variant Dim varIATACode As Variant Dim varStart As Variant Dim varEnd As Variant Dim intCount As Integer > "David" <David@discussions.microsoft.com> wrote in message > <718BB471-1FAF-4DB9-A429-BD6443041B41@microsoft.com>: [quoted text clipped - 37 lines] > One sure sign that there's something else going on, would be if you've > got an extra instance of Excel in memory (check with Task Manager). I think if you use late binding instead your problem may go away. SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Roy, > Thank you for your assistance. I inserted Dave's code and it worked when [quoted text clipped - 86 lines] >> One sure sign that there's something else going on, would be if you've >> got an extra instance of Excel in memory (check with Task Manager). "David" <David@discussions.microsoft.com> wrote in message <29F26818-B10B-4E85-A43E-2F01B6AE89F1@microsoft.com>: > Roy, > Thank you for your assistance. I inserted Dave's code and it worked [quoted text clipped - 47 lines] > Dim varEnd As Variant > Dim intCount As Integer I use the following principle Every object that is opened, I explicitly close Every object that is instantiated, I explicitly release The closing and releasing should be in the correct order. I e, release range objects before sheet objects, sheet objects before workbook objects - the workbook closed (if so intended) prior to releasing the variable ... Do you really use all those Excel object variables? If you do, I would release them - or remove them from the code. Just focusing on the routine (and variables) we've worked with, I'd probably Set objOpen1 = Nothing Set objOpen = Nothing ' objXLBook.Save objXLBook.Close True DoEvents ' perhaps a DoEvents to allow for closing and ' saving the workbook? Set objXLBook = Nothing objXLApp.Quit DoEvents ' perhaps a DoEvents to allow for the application to close? Set objXLApp = Nothing As I said in my previous reply, strip it down to the bares minimum to make the copy work, add incrementally until you get a problem - then inform us about what the problem really is. Now we're just guessing. Are you aware that in the following declaration Dim LastRow, LastRow2, LastRow3 As Integer you are actually declaring only LastRow3 as Integer, the others as variants. To declare all as integers, use either Dim LastRow As Integer, LastRow2 As Integer, LastRow3 As Integer or (my preference - one line per declaration) Dim LastRow As Integer Dim LastRow2 As Integer Dim LastRow3 As Integer SignatureRoy-Vidar Roy, Thank you very much for being patient with me on this. I took your advice and put just the code that was erroring out in a seperate module. Turns out the code prior to the code we worked with left the excel file visible-false. That caused the code we were working with to error out. It all works now, just one last issue regarding removing tabs... but that is in another post. > "David" <David@discussions.microsoft.com> wrote in message > <718BB471-1FAF-4DB9-A429-BD6443041B41@microsoft.com>: [quoted text clipped - 37 lines] > One sure sign that there's something else going on, would be if you've > got an extra instance of Excel in memory (check with Task Manager). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.