在PB中写VBA代码将Excel数据在PB数据窗口中读入

hu_dongmei

我的原理是 1、把Excel数据导出为TXT文件 2、再把TXT文件在PB的数据窗口中读入 我用PB做Excel导入到数据窗口中，如下 oleObject ole_Excel ole_Excel = Create OleObject ole_Excel.ConnectToObject('d:\jibenb.xls','Excel.Application') ole_Excel.Application.Saveas('d:\jibenb.txt',TextFileType) dw_data.ImportFile('d:\jibenb.txt') 其中，TextFileType在PB中怎么定义呢？应是个数字吧是，是多少呢？试了好多个了，“ole_Excel.Application.Saveas('d:\jibenb.txt',TextFileType) ”总出错。 谁知道，帮帮我吧

hu_dongmei

如果用的是VB，就可以直接写 ActiveWorkbook.SaveAs Filename:= _ "d:\jibenb.txt", FileFormat:= _ xlText, CreateBackup:=False 也就是说在PB中用什么可以替代xlText？ 谁哪儿有相关的资料能不能提供一下？ 我这儿有一个相关的东东，但不全 How to find the correct Visual Basic for Applications (VBA) syntax needed in Word and how to convert it into the correct Powerbuilder syntax With Word 8 / Office 97, Microsoft changed the way applications communicate with Word via OLE. Old WordBasic syntax may not work properly and converting them to VBA equivalents may be required. 1.1 Use the Word macro Editor An easy way to determine the VBA functions and their syntax by using the macro editor which is integrated in Word. To obtain the VBA functions turn on the macro recording in Word, perform the tasks you wish the macro to perform (ie. Highlight some text), and then turn off the recording. With ALT + F11 you are able to open the macro editor and see the syntax used by Word. To get a complete description of a function and to see the complete syntax you can use the help file VBAWRD8.HLP which is available through a custom installation of Word (you must specify to install this help file during the installation process, it is not installed by default). Here you will find information regarding the functions you can use in Word and their VBA and WordBasic syntax. Furthermore you will be instructed which functions you can use with Word 8. 1.2 Convert VB Syntax to the PowerBuilder Syntax Hints: a. PowerBuilder uses square brackets for array indices b. Functions with several values are separated by comma between round brackets Convertion example : Here you see a macro recorded with the macro editor in Word. Sub Macro1() ' VB Syntax in a macro ' see hint a. Selection.Information(wdWithInTable) ' see hint b. Selection.MoveLeft Unit:=wdCharacter, Count:=5 ' function without values Selection.TypeBackspace End Sub From the VBAWRD8.HLP file, here is the definition of the MoveLeft() function used in the macro: Selection.MoveLeft(Unit, Count, Extend) Unit optional Variant: wdCell, wdCharacter, wdWord or wdSentence. Default = wdCharacter. Count optional: . Number of Units to move. Default = 1. Extend optional: wdMove or wdExtend Here is the syntax to call the MoveLeft() function // PB syntax ole_object.selection.Information[12] ole_object.selection.MoveLeft(1,5,1) ole_object.selection.TypeBackspace() The question is now: what value does the constants "wdWithInTable", "wdCharacter" contain? These are only known to MS Word which prevents PowerBuilder from having any knowledge about them. However each constant is associated with a value which can be used in its place. In general most of them are integers and you can display them in a message box within the macro to determine their actual value i.e. Sub Makro1() ' this is a small messagebox to find out the integer ' value of an enumerated datatype in Word. ' in this case the value 5 Answer = MsgBox(wdLine, 0) End Sub Alternatively, Micorosoft's support website has a technical article which documents all of the constants and their numerical equivalents that may be found in Microsoft Office. The article is ID# Q112671. In PowerBuilder, you would pass the actual value of the Word constant to the function. You can also declare a constant in PowerScript and associate it with the same value; when calling the Word function, pass it the constant you declared instead of the actual value. This allows you to make global changes easily to your code if Microsoft decides to change what the value of the Word constant in future releases. Under section 3. you find a list with common constants and their numeric equivalents. 1.3 The Word Object Model If you open VBAWRD8.HLP file you will find the hierarchy of the Microsoft Word Objects. Through OLE, you can traverse the hierarchy by using PowerBuilder's dot notation. Some common objects/methods and attributes are: Application, System, MailMessage, Selection, Selection.Font, Selection.Pagesetup, Selection.Border, Selection.Words, ActiveDocument, ActivePrinter, Checkspelling, CheckGrammar ... Several methods and attributes of the Application object can be used without specifying the "application" constant. For example the ActiveDocument or Documents are considered global objects within Word. Instead of writing: ole_object.application.ActiveDocument.CheckSpelling() you can write: ole_object.ActiveDocument.CheckSpelling() Below you'll find some code examples using the objects, methods and attributes of Word. 2. Code Examples For all following examples the PowerBuilder OleObject is assumed to have already been create dynamically with: OLEObject ole_object ole_object = CREATE OLEObject If you use the PowerBuilder OLE control (ole_1) you must remember to include the "object" constant before accessing Microsoft Word objects, properties, and functions. i.e. myinteger = ole_1.object.application.Activedocument.kind or ole_1.object.application.Selection.Font.Italic = TRUE 2.1 Connect to Word 8 // Connect to Word 8 (starts Word 8) , if successful Opens employee.doc , // if unsuccessful , display msg box for "ole error". IF ole_object.ConnectToNewObject("word.application.8") = 0 THEN ole_object.Documents.open("c:\my documents\employee.doc") ELSE MessageBox('OLE Error','Unable to start an OLE server process!',Exclamation!) END IF // Make Word 8 visible - ( Set to FALSE for Word 8 to be invisible ) ole_object.Visible=TRUE 2.2 bookmarks // To insert text in a specal place you can define a Bookmark // The function Exists() veryfies if the Bookmark exists in the document // `GoTo` brings the cursor to the bookmank and `TypeText` insert the text. IF ole_object.object.ActiveDocument.Bookmarks.Exists( " bookmark_name " ) THEN ole_object.Selection.Goto(TRUE,0,0,"bookmark_name") ole_object.Selection.TypeText("text to insert") ELSE ole_object.ActiveDocument.bookmarks.add("bookmark_name") END IF 2.3 spellchecker and grammarchecker method // starts the grammar / spell check of word for the whole active document ole_object.ActiveDocument.CheckGrammar() ole_object.ActiveDocument.CheckSpelling() // starts the grammar / spell check for the specific text. ole_object.ActiveDocument.CheckGrammar("text to check") ole_object.ActiveDocument.CheckSpelling("text to check") 2.4 How should the text look like // set the font to italic, bold or change the current font to Arial with // size 24 ole_object.Selection.Font.Italic = TRUE ole_object.Selection.Font.bold = TRUE ole_object.Selection.Font.Name = "Arial" ole_object.Selection.Font.Size = 24 ole_object.Selection.Words[1].Italic = True ole_object.ActiveDocument.Paragraphs[1].Range.Font = "Arial" 2.5 Step through and find the text // goto the bookmark `state` ole_object.selection.GoTo(TRUE,0,0,"state") // goto the next line wdGoToLine = 3 ole_object.selection.GoToNext(3) // find a text ole_object.Selection.Find.Forward = True ole_object.Selection.ClearFormatting ole_object.Selection.MatchWholeWord = True ole_object.Selection.MatchCase = False ole_object.Selection.Wrap = wdFindContinue ole_object.Selection.Execute("PowerBuilder") 2.6 Activate and access a Word blob in a datawindow If you have an OLE column specified in a datawindow you must activate the column to and then associate it with an OLEObject to get access to its properties and functions.: // Activate the ole blob in this case is `ole` the name of the ole blob // column and the blob is in the first row. dw_1.oleActivate(1,"ole",0) // Create a oleobject to get access to the running word application server OLEObject myoleobject myoleobject = CREATE OLEObject // connect to Word that is currently running from OleActivate() IF myoleobject.ConnectToObject("", "word.application.8") <> 0 THEN MessageBox('OLE Error','Unable to start an OLE server process!',Exclamation!) END IF myoleobject.Visible=TRUE // access the propertie CHOOSE CASE myoleobject.ActiveDocument.kind CASE 0 ... CASE 1 ... END CHOOSE 3. Parital Listing of Visual Basic for Applications Constants and their Values for PowerBuilder The following is a list of some of the VBA constants that may be found within Microsoftt Word. For a comprehensive listing, please visit the Microsoft Support Website and search their knowledge base for technical article Q112671. //wdSeekView Type constant int wdSeekMainDocument = 0, constant int wdSeekPrimaryHeader = 1, constant int wdSeekFirstPageHeader = 2, constant int wdSeekEvenPagesHeader = 3, constant int wdSeekPrimaryFooter = 4, constant int wdSeekFirstPageFooter = 5, constant int wdSeekEvenPagesFooter = 6, constant int wdSeekFootnotes = 7, constant int wdSeekEndnotes = 8, constant int wdSeekCurrentPageHeader = 9, constant int wdSeekCurrentPageFooter = 10 // WdSaveOptions constant int wdDoNotSaveChanges = 0 constant int wdPromptToSaveChanges = -2 constant int wdSaveChanges = -1 // WdConstants Constant long wdAutoPosition = 0 Constant long wdFirst = 1 Constant long wdToggle = 9999998 Constant long wdUndefined = 9999999 Constant long wdForward = 1073741823 Constant long wdBackward = -1073741823 Constant long wdCreatorCode = 1297307460 // WdLanguageID Constant long wdDanish = 1030 Constant long wdGerman = 1031 Constant long wdGreek = 1032 Constant long wdEnglishUS = 1033 Constant long wdSpanish = 1034 Constant long wdFinnish = 1035 Constant long wdFrench = 1036 Constant long wdBelgianDutch = 2067 Constant long wdBelgianFrench = 2060 Constant long wdDutch = 1043 Constant long wdEnglishUK = 2057 Constant long wdItalian = 1040 Constant long wdNoProofing = 1024 Constant long wdPortuguese = 2070 Constant long wdSpanishModernSort = 3082 Constant long wdSwedish = 1053 // WdCountry Constant long wdUS = 1 Constant long wdNetherlands = 31 Constant long wdFrance = 33 Constant long wdSpain = 34 Constant long wdItaly = 39 Constant long wdUK = 44 Constant long wdDenmark = 45 Constant long wdSweden = 46 Constant long wdGermany = 49 Constant long wdFinland = 358 // WdDictionaryType Constant long wdSpelling = 0 Constant long wdGrammar = 1 Constant long wdThesaurus = 2 Constant long wdHyphenation = 3 Constant long wdSpellingComplete = 4 Constant long wdSpellingCustom = 5 Constant long wdSpellingLegal = 6 Constant long wdSpellingMedical = 7 // WdDocumentType Constant long wdTypeDocument = 0 Constant long wdTypeTemplate = 1 // WdBuiltInProperty Constant long wdPropertyTitle = 1 Constant long wdPropertySubjec = 2 Constant long wdPropertyAuthor = 3 Constant long wdPropertyConstants = 4 Constant long wdPropertyComments = 5 Constant long wdPropertyTemplate = 6 Constant long wdPropertyLastAuthor = 7 Constant long wdPropertyRevision = 8 Constant long wdPropertyAppName = 9 Constant long wdPropertyTimeLastPrinted = 10 Constant long wdPropertyTimeCreated = 11 Constant long wdPropertyTimeLastSaved = 12 Constant long wdPropertyVBATotalEdit = 13 Constant long wdPropertyPages = 14 Constant long wdPropertyWords = 15 Constant long wdPropertyCharacters = 16 Constant long wdPropertySecurity = 17 Constant long wdPropertyCategory = 18 Constant long wdPropertyFormat = 19 Constant long wdPropertyManager = 20 Constant long wdPropertyCompany = 21 Constant long wdPropertyBytes = 22 Constant long wdPropertyLines = 23 Constant long wdPropertyParas = 24 Constant long wdPropertySlides = 25 Constant long wdPropertyNotes = 26 Constant long wdPropertyHiddenSlides = 27 Constant long wdPropertyMMClips = 28 Constant long wdPropertyHyperlinkBase = 29 Constant long wdPropertyCharsWSpaces = 30 // WdAlertLevel Constant long wdAlertsAll = -1 Constant long wdAlertsMessageBox = -2 Constant long wdAlertsNone = 0 // WdArrangeStyle Constant long wdIcons = 1 Constant long wdTiled = 0 // WdBookmarkSortBy Constant long wdSortByLocation = 1 Constant long wdSortByName = 0 // WdBreakType Constant long wdSectionBreakNextPage = 2 Constant long wdSectionBreakContinuous = 3 Constant long wdSectionBreakEvenPage = 4 Constant long wdSectionBreakOddPage = 5 Constant long wdLineBreak = 6 Constant long wdPageBreak = 7 Constant long wdColumnBreak = 8 // WdBrowseTarget Constant long wdBrowsePage = 1 Constant long wdBrowseSection = 2 Constant long wdBrowseComment = 3 Constant long wdBrowseFootnote = 4 Constant long wdBrowseEndnote = 5 Constant long wdBrowseField = 6 Constant long wdBrowseTable = 7 Constant long wdBrowseGraphic = 8 Constant long wdBrowseHeading = 9 Constant long wdBrowseEdit = 10 Constant long wdBrowseFind = 11 Constant long wdBrowseGoTo = 12 // WdCharacterCase Constant long wdLowerCase = 0 Constant long wdUpperCase = 1 Constant long wdTitleWord = 2 Constant long wdTitleSentence = 4 Constant long wdToggleCase = 5 Constant long wdNextCase = -1 // WdCollapseDirection Constant long wdCollapseEnd = 0 Constant long wdCollapseStart = 1 // WdInternationalIndex Constant long wdProductLanguageID = 26 // wdWindowState constant int wdWindowStateNormal = 0 constant int wdWindowStateMaximize = 1 constant int wdWindowStateMinimize = 2 // wdGoToDirection constant int wdGoToAbsolute = 1 constant int wdGoToFirst = 1 constant int wdGoToLast = -1 constant int wdGoToNext = 2 constant int wdGoToPrevious = 3 constant int wdGoToRelative = 2 // wdGoToItem constant int wdGoToBookmark = -1 constant int wdGoToSection = 0 constant int wdGoToPage = 1 constant int wdGoToTable = 2 constant int wdGoToLine = 3 constant int wdGoToFootNote = 4 constant int wdGoToEndNote = 5 constant int wdGoToComment = 6 constant int wdGoToField = 7 constant int wdGoToGraphic = 8 constant int wdGoToObject = 9 constant int wdGoToEquation = 10 constant int wdGoToHeading = 11 constant int wdGoToPercent = 12 constant int wdGoToSpellingError = 13 constant int wdGoToGrammaticalError = 14 constant int wdGoToProofReadingError = 15 // wdMovementType constant int wdMove = 0 constant int wdExtend = 1 // wdUnits constant int wdCharacter = 1 constant int wdWord = 2 constant int wdSentence = 3 constant int wdLine = 5 constant int wdStory = 6 constant int wdColumn = 9 constant int wdRow = 10 constant int wdCell = 12 //wdViewType constant int wdNormalView = 1 constant int wdOutlineView = 2 constant int wdPageView = 3 constant int wdPrintPreview = 4 constant int wdMasterView = 5 constant int wdOnlineView = 6 4. Related Technical Documents: #44559: OLE Automation for Clients and Servers #44643: Using OLE with Powerbuilder and MS Office 97 (Word 8)