WSH scripts provides a simple interface to access Office application like Microsoft Excel from a script. I have shown the details within the previous sample. But there is one problem, if you try to import a Comma Separated Value (CSV) file which doesn't use semicolons as separators. In this case the CSV file's content is imported in the first column of the first workbook.
|Note: This behavior was pointed out by Dat Nguyen from Montreal, Canada, during our discussion about WSH features. Later on I found the reason: The separator character is user defined and depend on the operating system (a German Windows uses different characters than a US Windows).|
My first attempt to use named parameters defined for the Excel Open method failed (WSH script engines don't support named parameters). I was able to pass the parameters in their default order, but the properties to set the separator value causes nothing. So I decided to develope another solution. The ideas is rather simple: I invoke Excel, create an empty Workbook and import the CSV file into the first Worksheet. During this import we can set several properties for the Import wizard. Further details may be found in the following listing. The CSV file Names.csv must be located in the same folder as the VBScript file. The sample requires Microsoft Excel 2000.
'************************************************ ' File: ExcelCSV2.vbs (WSH sample in VBScript) ' Author: (c) GŁnter Born (last edit 5-April-1999) ' ' A modified VBScript sample which demonstrates how to ' import a CSV file (with several separators like commas, ' semicolon, etc.) into a new sheet. We use the import wizard ' to avoid the trouble that CSV files with comma delimiters ' are getting read into the first column (a little bit tricky, ' but I was not able to get Open method to accept the format ' properties - they was ignored). ' ' Well, here we go: ' Launch Excel, add a worksheet, import the CSV file ' select the current worksheet, read some cell ' values back into the script, print the CSV content ' and terminate. ' ' Names.csv is a simple text file created with an editor ' using the following structure: ' ' Name:STRING,ID:INT ' Born,123 ' Miller,728 ' Myers,1334 ' McFyer,789 ' Thommy,345 ' ' Here I have used a comma as a field separator. The CSV file Names.csv ' must be located in the same folder as the script for this example. ' ' In no way shall the author be liable for any ' losses or damages resulting from the use of this ' program. Use AS-IS at your own risk. ' ' The code is the property of the author. You may ' use the code and modify it, as far as this header ' remains intact. Further updates and other samples ' may be found on my site mentioned above. ' This sample was derived from samples shown in my book: ' Inside Windows Scripting Host, MS Press Germany ' ' Check out Born's Windows Script Host Bazaar at: ' http://www.borncity.de ''************************************************ Option Explicit Const vbNormal = 1 ' window style DIM objXL, objWb, objR, objTab ' Excel object variables DIM Title, Text, tmp, i, j, file, name Title = "WSH sample - by G. Born" ' here you may set the name of the file to be imported file = "Names.csv" ' must be located in the script folder ' create an Excel object reference Set objXL = WScript.CreateObject ("Excel.Application") ' set the Excel window properties (not absolutely necessary) objXL.WindowState = vbNormal ' Normal objXL.Height = 300 ' height objXL.Width = 400 ' width objXL.Left = 40 ' X-Position objXL.Top = 20 ' Y-Position objXL.Visible = true ' show window ' Create new Workbook (needed for import the CSV file= Set objWb = objXl.WorkBooks.Add ' Get the first loaded worksheet object of the current workbook Set objWb = objXL.ActiveWorkBook.WorkSheets(1) objWb.Activate ' not absolutely necessary (for CSV) ' Now invoke the import wizard Set objTab = objWb.QueryTables.Add ("TEXT;"+GetPath + file, objWb.Range("A1")) ' here comes the mumbo jumbo to set all the properties for the wizard ' Oh Microsoft, how do I wish to has a With feature or a possibility to ' pass named arguments to methods .... objTab.Name = "Names" objTab.FieldNames = True objTab.RowNumbers = False objTab.FillAdjacentFormulas = False objTab.PreserveFormatting = True objTab.RefreshOnFileOpen = False objTab.RefreshStyle = 1 'xlInsertDeleteCells objTab.SavePassword = False objTab.SaveData = True objTab.AdjustColumnWidth = True objTab.RefreshPeriod = 0 objTab.TextFilePromptOnRefresh = False objTab.TextFilePlatform = 2 'xlWindows objTab.TextFileStartRow = 1 objTab.TextFileParseType = 1 'xlDelimited objTab.TextFileTextQualifier = -4142 ' xlTextQualifierNone objTab.TextFileConsecutiveDelimiter = False objTab.TextFileTabDelimiter = True ' ### my delimiters objTab.TextFileSemicolonDelimiter = True objTab.TextFileCommaDelimiter = True objTab.TextFileSpaceDelimiter = False objTab.TextFileColumnDataTypes = Array(1, 1) objTab.Refresh False WScript.Echo "We have loaded the worksheet" ' demonstrate how to read the column header values Text = "Worksheet " & objWb.name & vbCRLF Text = Text & "Column titles" & vbCRLF Text = Text & CStr(objWb.Cells(1, 1).Value) & vbTab Text = Text & CStr(objWb.Cells(1, 2).Value) & vbCRLF ' show some cell values (using the "hard coded method") Text = Text & CStr(objWb.Cells(2, 1).Value) & vbTab Text = Text & CStr(objWb.Cells(2, 2).Value) & vbCRLF Text = Text & CStr(objWb.Cells(3, 1).Value) & vbTab Text = Text & CStr(objWb.Cells(3, 2).Value) & vbCRLF Text = Text & CStr(objWb.Cells(4, 1).Value) & vbTab Text = Text & CStr(objWb.Cells(4, 2).Value) & vbCRLF ' Show results MsgBox Text, vbOkOnly+ vbInformation, Title objXl.ActiveSheet.PrintOut ' print Worksheet WScript.Echo "We are printing, close after printing" ' I like to prevent the warning message about the unsaved data ' during closing Excel objXL.DisplayAlerts = False ' prevent all message boxes objXl.Quit ' Quit Excel Set objXL = Nothing '########################## Function GetPath ' Retrieve the script path DIM path path = WScript.ScriptFullName ' Script name GetPath = Left(path, InstrRev(path, "\")) End Function '*** End
Additional information about the WSH may be found in my German WSH book Inside Windows Script Host, published by Microsoft Press Germany.
(c) G. Born