Marcus' Macro Scheduler Blog

This morning, forum user hip asked how a script could sort a CSV file. Seems CSV files, sorting and Excel has become a bit of a hot topic here lately. Since Excel can read and save to CSV and also has a Sort function we might as well make use of it. Here’s a little VBScript function I put together which will sort a CSV file by a given column:

VBSTART
Sub SortCSVFile(file,sort_column)
  Set XLApp = CreateObject("Excel.Application")
  XLApp.Workbooks.Open(file)
  
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
  Set SortCol = XLApp.Range(sort_column)
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(SortCol)
  Set Cell = XLApp.ActiveWorkbook.ActiveSheet.Cells.Find("*", XLApp.ActiveWorkbook.ActiveSheet.Cells(1, 1),,,,2)
  Set mYRange = XLApp.Range("A1", XLApp.ActiveWorkBook.ActiveSheet.Cells(Cell.Row, Cell.Column))
    
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SetRange(mYRange)
  XLApp.ActiveWorkbook.ActiveSheet.Sort.Apply
  
  XLApp.ActiveWorkBook.Save
  XLApp.ActiveWorkBook.Close false
  XLApp.quit
End Sub
VBEND

The function opens the CSV file in Excel – without making Excel visible – then performs the sort, saves the file and closes Excel. You won’t see Excel appear on the screen.

Here’s how to use it. If you want to sort your file on column B and don’t have a header row, use:

VBRun>SortCSVFile,C:\Documents\my.csv,B1

To sort on column A and you DO have a header row use:

VBRun>SortCSVFile,C:\Documents\my.csv,A2

UPDATE: Here’s a simplified version which should work in Excel XP/2003 and should be able to detect whether or not your data has a header row:

VBSTART
Sub SortCSVFile(file,sort_column)
  Set XLApp = CreateObject("Excel.Application")
  XLApp.Workbooks.Open(file) 
  XLApp.ActiveWorkbook.ActiveSheet.Range("A1").Sort XLApp.ActiveWorkbook.ActiveSheet.Range(sort_column),,,,,,,0  
  XLApp.ActiveWorkBook.Save
  XLApp.ActiveWorkBook.Close false
  XLApp.quit
End Sub
VBEND

What if you don’t have Microsoft Excel?

As mentioned in my reply on the forum another method you could use is to open the CSV file as a recordset using DBQuery and apply an “ORDER BY” clause to your SQL statement. That would sort it into memory. Then you would need to loop through the array and write the data back out to a CSV file using WriteLn.

Something like:

//new file to write to
Let>new_file=%SCRIPT_DIR%\example_sorted.csv
IfFileExists>new_file
  DeleteFile>new_file
Endif

//set WriteLn NOT to add line breaks (we'll add those ourselves)
Let>WLN_NOCRLF=1
//The CSV delimiter
Let>comma=,

//Connect to and query the CSV using ORDER BY PRICE to sort on Price column
Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%;Extended Properties='text;HDR=YES;FMT=Delimited'
DBConnect>ConStr,dbH
DBQuery>dbH,select * from example.csv ORDER BY PRICE,fields,num_recs,num_fields

//If we have records, loop through records and fields, writing out to new file
If>num_recs>0
  //first need to copy the header record ...
  ReadLn>%SCRIPT_DIR%\example.csv,1,header_line
  WriteLn>new_file,wlnres,%header_line%%CRLF%

  //the loop
  Let>rec=0
  Repeat>rec
    Let>rec=rec+1
    Let>field=0
    Repeat>field
      Let>field=field+1
      Let>this_field=fields_%rec%_%field%
      //write the field out to the new file
      WriteLn>new_file,wlnres,this_field
      //add the comma delimiter if needed
      If>field<num_fields
        WriteLn>new_file,wlnres,comma
      Endif
    Until>field=num_fields
    //add CRLF at end of line
    WriteLn>new_file,wlnres,%CRLF%
  Until>rec=num_recs
Endif
DBClose>dbH

//if you want, delete the input file and replace with the new sorted one:
//DeleteFile>%SCRIPT_DIR%\example.csv
//RenameFile>%SCRIPT_DIR%\example_sorted.csv,%SCRIPT_DIR%\example.csv

Earlier this month I wrote about how you can read and write data from/to CSV files as if they were database tables using DBQuery. Andrew asked if I knew of a way that XML files could be treated as database tables in the same way. I did find a couple of references to some ODBC drivers online but one was not yet available and the other was part of a larger commercial package so I was unable to try them.

Since then it has occurred to me that there is a way we can convert XML files to CSV (or XLS) files using Excel. Once the data is in CSV file format we can then retrieve it easily using DBQuery.

The more technically aware will probably ask why we can’t just use the MSXML.DomDocument object in VBScript to read in the XML and parse it. Well, while that is of course possible, many will find the ability to access the XML data in a tabular database-like format easier and more familiar.

So, without further ado, here’s my ConvertXMLtoCSV function:

VBSTART
Sub ConvertXMLtoCSV(xmlFile,csvFile)
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.OpenXML(xmlFile,2)
    xlBook.SaveAs csvFile, 6
    xlBook.close false
    xlApp.quit
End Sub
VBEND

You will need Excel installed for this to work. Use the function like this:

VBRun>ConvertXMLtoCSV,c:\docs\my.xml,c:\docs\converted.csv

And now you can quickly read the data into an array using DBQuery.

The function works quietly in the background. You won’t see Excel open – just a short delay while the CSV file is created.

To convert to an Excel file rather than a CSV file change the code to:

VBSTART
Sub ConvertXMLtoXLS(xmlFile,xlsFile)
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.OpenXML(xmlFile,2)
    xlBook.SaveAs xlsFile, 56
    xlBook.close false
    xlApp.quit
End Sub
VBEND

Please note I’m using Excel 2007 and haven’t tried this with earlier versions though I believe Excel 2003 can also read from XML files. Success may also depend on the format of your XML.

See:
Reading From CSV Files
More on Reading and Writing CSV Files
Retrieve Entire Excel Sheet Using DBQuery

If you are automating web pages or web applications you may at some point need to automate dialog boxes produced by client side script, like the one that pops up when you click the link below:

Confirm Popup

So you might want to be aware that the title of these popups varies between different versions of Internet Explorer. In IE6 it was “Microsoft Internet Explorer”. In IE7 it is “Windows Internet Explorer” and in IE8 it is now “Message from webpage”.

I much prefer the last one from an automation point of view since it differs significantly from the title of the main IE window which usually contains the phrase “Microsoft Internet Explorer” in IE7 and “Windows Internet Explorer” in IE8.

So if we wanted to click on the Yes button if we were writing a script for IE8 we could use the following code:

SetFocus>Message from webpage
Press Enter

But what if we didn’t know what version of IE our script would be automating? If you were giving your script to someone else or putting it on some other PC you may not know what version of IE was being used. What we need to do is make the script find out what version of IE is installed on the computer.

Here’s how to make the script determine which version of IE is in use:

RegistryReadKey>HKEY_LOCAL_MACHINE,SOFTWARE\Microsoft\Internet Explorer,Version,IEver
Separate>IEVER,.,bits
Let>IEVER=bits_1

IEVER now contains the major version number of Internet Explorer.

So we could now use this to define the title we expect from the dialog box and confirm it:

RegistryReadKey>HKEY_LOCAL_MACHINE,SOFTWARE\Microsoft\Internet Explorer,Version,IEver
Separate>IEVER,.,bits
Let>IEVER=bits_1

Let>dialogTitle_IE6=Microsoft Internet Explorer
Let>dialogTitle_IE7=Windows Internet Explorer
Let>dialogTitle_IE8=Message from webpage

SetFocus>dialogTitle_IE%IEVER%
Press Enter

Of course this is all assuming you’re using the English version of Internet Explorer. I cannot vouch for other languages.

Twice today, for two different people, I needed to write some code to iterate through files in a tree of subfolders. The subfolder structure could not be known up front and there may be any number of subfolders and any number of folders deep.

One could use GetDirList iteratively, or use VBScript’s FileSystem Object. But then I was reminded of the power of DOS by JRL in this post.

The following returns a file containing a list of the jpg files throughout all folders, starting in c:\root_folder\ with full paths:

dir c:\root_folder\*.jpg /s /b > outputfile.txt

So, as JRL suggests, we can call that command from Macro Scheduler and then read in the output file. We should use a temporary file:

Let>RP_WINDOWMODE=2
Let>RP_WAIT=1
Run>cmd /c dir c:\root_folder\*.jpg /s /b > %TEMP_DIR%~temp_dir_list~
ReadFile>%TEMP_DIR%~temp_dir_list~,dir_list

Now we can explode that list into an array and loop through it, doing whatever it is we need to do to each file:

Separate>dir_list,CRLF,files
If>files_count>0
Let>f=0
Repeat>f
  Let>f=f+1
  Let>this_file=files_%f%
  //do something with this_file
Until>f=files_count

It’s easy to forget the power of DOS – it can save you a fair few lines of code sometimes.

Remember that any DOS command can be “piped” to an output file by appending >filename to the command line. This is very useful as we can then use ReadFile to get the output from the command, as we have done above.

Gale Dyvig has just posted some code in the forums showing how to use the Microsoft Office Document Imaging OCR component to locate text on the screen, find its position and click on it. Very useful. You’ll find it here.

MODI is a component that comes with Microsoft Office. You need to ensure Microsoft Office Document Imaging and Office Shared Features / Proofing Tools is installed in Office Tools.

Thanks Gale!

A project I’m currently working on involves populating data into a rather poorly designed user interface.  A particular challenge were the drop down lists and list boxes due to the fact that you can’t “drill down” by sending the text of the item you want selected.

The solution we settled upon uses the text capture function GetTextAtPoint in a loop which arrows down the entire list, with GetTextAtPoint capturing the text in the selection box to see if it’s the value required.  If it is we stop, else we continue to press down and loop.  We stop if the item selected is the same as the previous item, signifying that we’ve reached the end (obviously this assumes the list doesn’t have duplicates appearing together).

Here’s the subroutine:

SRT>dropDownSelect
/*****
Usage: GoSub>dropDownSelect,X,Y,text_to_select

Where X and Y are coordinates in the edit box (where the item appears once selected)
 and text_to_select is the text you want selected  

Important: the drop down needs to have the focus
*****/
  If>dropDownSelect_Var_3<>
    GetTextReset
    Press Home
    Wait>0.1
    Let>prev_comboText=Z@$%#XXX
    Let>dropDownFound=FALSE
    Label>select_drop
    GetTextAtPoint>dropDownSelect_Var_1,dropDownSelect_Var_2,comboText,c
    Pos>dropDownSelect_Var_3,comboText,1,p
    If>p<>1
      If>prev_comboText<>comboText
        Press Down
        Wait>0.2
        Let>prev_comboText=comboText
        Goto>select_drop
      Endif
    Else
      Let>dropDownFound=TRUE
    Endif
  Endif
END>dropDownSelect

So, for example, you could call it something like:

Press Tab
GoSub>dropDownSelect,240,355,Apples

Note that the drop down needs to be focused. Usually you’d be “tabbing” through fields on the form, so once you’ve used “Press Tab” to get focus onto the control you can call dropDownSelect.

This version requires that you pass the X,Y screen position of the text box area of the drop down. In development at the moment we have a new function which returns the handle of the currently focused object, and this can then be used in GetWindowPos to get the X,Y position. So in future you will be able to determine the X,Y position dynamically.

Hopefully you won’t need this function as you can usually “drill down” on a list box or drop down list by sending the text of the item you want as mentioned here. But for those badly designed controls that won’t allow that this function can be a life saver.