> I had to automate importing a tab-delimited csv file into Excel > yesterday and I went through a lot of trial-and-error (lots of error) > in the process of figuring out enough of Excel's object model and how > to use vbscript. I figured I'd save someone else some effort.
What your code has done is to create a data link between Excel and the text file so that whenever the text file is changed, you can switch to that workbook and do a "refresh" and it will retrieve the then-current data from the file. Was this what you wanted to do? Or did you just want to open a tab-delimited text file in Excel as a new document without the data link? If it's just opening it as a document, you can use either the "Open" method (for comma-delimited data): Set objXL = CreateObject("Excel.Application") objXL.Visible = True objXL.Workbooks.Open "C:\MyFolder\MyFile.csv" or the "OpenText" method (for tab-delimited data): Set objXL = CreateObject("Excel.Application") objXL.Visible = True objXL.Workbooks.OpenText "C:\MyFolder\MyFile.txt",437,1,1,1,False,True (BTW, it is usually best to have *comma*-delimited data in .csv files, and *tab*-delimited data in .txt files (Excel can get confused sometimes with tab-delimited data in .csv files).) > And I've got a question - does anyone have any vbscript resources they > use? IDEs, object model documentation, anything? Microsoft's examples > don't work (I wasted several hours on that) since they rely on being > in a VBA environment for Office automation: "ActiveSheet", for > instance, doesn't seem to exist as an object in vbscript run > externally. And while TextFileTabDelimiter seems to work as a property > of a QueryTable, xlDelimiter doesn't. OK, let me show you how I work with stuff like this, which might help in tracking this stuff down... Looking at the example for OpenText, how did I know the "437", or the "1"s, or the False/Trues? I did the same thing as Mark (recorded a simple macro), and when you go to edit the macro, the VBA command is actually this (VB uses "_" as a continuation char like we use "\"): Workbooks.OpenText Filename:="C:\MyFolder\MyFile.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,1), _ Array(2,1), Array(3,1)), TrailingMinusNumbers:=True If you try to paste this into a VBScript file, it won't work because VBScript can't use (AFAIK) the <ParamName:=Value> construct. Also, any value that is not a recognizable type (i.e. integer, boolean, etc,) and starts with letters that correspond to the application itself (in this case "xl") are almost always constants. The VBScript will choke on the constants "xlDelimited" and "xlDoubleQuote" because although these are known to Excel when running a macro, they are unknown to VBScript when it tries to execute the script since they aren't defined anywhere in the script file. So to determine just what we *can* do, you need to look at the Object Browser, which is lets you see the VBA Object Model for Excel (and other currently running relevant apps). To do this, you go into the VB script editor (either by going to Tools > Macro > Macros.. (or typing Alt+F8) and find your macro you recorded and edit it, or just type Alt-F11 to open the Script Editor to the last script you were editing). Choose View > Object Browser (or type F2) and when it comes up, choose "Excel" from the popup menu at the top - this will limit what you see in the Object Browser to just what Excel provides. The first thing to check out is the actual syntax for the Workbooks.OpenText command, so choose "Workbooks" from the Classes list, and "OpenText" from the "Members of 'Workbooks'" list. This shows you the syntax: Sub OpenText(Filename As String, [Origin], [StartRow], [DataType], [TextQualifier As XLTextQualifier = xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [TextVisualLayout], [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers], [Local]) As you can see, everything but the Filename parameter is optional, and since you can't use the <ParamName:=Value> construct, you'll have to provide values for all params up to the last one your care about (so long as the defaults for the other params work out). OK, so that means we have to provide this syntax: Sub OpenText(Filename As String, [Origin], [StartRow], [DataType], [TextQualifier As XLTextQualifier = xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab]) The Origin ("437") and StartRow ("1"), can be extracted from the macro, but what about the DataType? The macro shows it as "xlDelimited". Even if you knew this was a constant, you don't know what the value of it should be. So you need to look it up. To do this, you can enter a value in the the *second* popup menu at the top of the Object Browser window, to the left of the binoculars button. This is the "Find" field, so if you type "xlDelimited" and click the binoculars, you get a search results list that shows: Library Class Member Excel XLTextParsingType xlDelimited If you click on the line in the search results, at the bottom of the Object Browser it shows: Const xlDelimited = 1 Member of Excel.XLTestParsingType OK... so the value for "xlDelimited" is "1". Now to find the value for the TextQualifier, which is "xlDoubleQuote" according to the macro. Perform the same search, but with "xlDoubleQuote" as your search string, and you'll get one hit, which when clicked on shows this at the bottom of the Object Browser: Const xlDoubleQuote = 1 Member of Excel.Constants All the rest of the parameters have fixed values that can be extracted from the macro, just like Origin and StartRow. > Microsoft's examples > don't work (I wasted several hours on that) since they rely on being > in a VBA environment for Office automation: "ActiveSheet", for > instance, doesn't seem to exist as an object in vbscript run > externally. Actually, it *does* work, but you either need to reference a currently running instance of Excel through using "GetObject" instead of "CreateObject": Set objXL = GetObject(,"Excel.Application") MsgBox objXL.ActiveSheet.Name (and "yes", the comma in front of "Excel.Application" MUST be there) or as a property of a workbook in a newly opened instance of Excel (which is what "CreateObject" Set objXL = CreateObject("Excel.Application") objXL.Visible = True Dim wb Set wb = objXL.Workbooks.Add MsgBox wb.ActiveSheet.Name My recommendation is to review the Excel Object Model, which is usually in the Excel help system, or if not, you can use this page to tell you where to look: http://support.microsoft.com/kb/222101, or downloading a specific language reference at: http://www.microsoft.com/technet/scriptcenter/topics/office/vba.mspx. In addition to that, *I* use a couple of VBA books I got at Borders, one for "VBA for the 2007 Microsoft Office System" and the "Microsoft Windows 2000 Scripting Guide". HTH, Ken Ray Sons of Thunder Software, Inc. Email: [EMAIL PROTECTED] Web Site: http://www.sonsothunder.com/ _______________________________________________ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution