OLE Excel: How to Set Array Properties

Dear all,
I would like to import a csv file into Excel using a DXL script, as this is much faster than writing a huge amount of data directly to Excel.
The following script works fine, but I can't set the TextFileColumnDataTypes property of the QueryTable object, which is responsible for correctly formatting the columns during importing of csv data into Excel: 

#include <addins/ExcelFunctions.inc>
 
const string cPropertyQueryTables = "QueryTables"
const string cMethodRefresh = "Refresh"
const int xlInsertDeleteCells = 1
const int xlDelimited = 1
const int xlTextQualifierDoubleQuote = 1
 
OleAutoObj objQueryTables = null;
OleAutoObj objQueryTable = null;
 
if (openExcel) {
    makeVisible(objExcel)
        if (getWorkBooks) {
                if (addWorkBook) {
                        if(getActiveSheet) {
                                checkResult(oleGet(objSheet, cPropertyQueryTables, objQueryTables));   // gets the query tables
                                // Get the destination range
                                if (getCellRange(1, 1, 1, 1)) {
                                        if (!null objQueryTables && !null objRange) {
                                                clear(args);
                                                put(args, "Connection","TEXT;C:\\Documents and Settings\\xyz\\Local Settings\\Temp\\DP133.csv");
                                                put(args, "Destination", objRange);
                                                checkResult(oleMethod(objQueryTables, cMethodAdd, args, objQueryTable))
                                                if (!null objQueryTable) {
                                                checkResult(olePut(objQueryTable, "Name", "DP133"));
                                                checkResult(olePut(objQueryTable, "FieldNames", true));
                                                checkResult(olePut(objQueryTable, "RowNumbers", false));
                                                checkResult(olePut(objQueryTable, "FillAdjacentFormulas", false));
                                                checkResult(olePut(objQueryTable, "PreserveFormatting", true));
                                                checkResult(olePut(objQueryTable, "RefreshOnFileOpen", false));
                                                checkResult(olePut(objQueryTable, "RefreshStyle", xlInsertDeleteCells));
                                                checkResult(olePut(objQueryTable, "SavePassword", false));
                                                checkResult(olePut(objQueryTable, "SaveData", true));
                                                checkResult(olePut(objQueryTable, "AdjustColumnWidth", true));
                                                checkResult(olePut(objQueryTable, "RefreshPeriod", 0));
                                                checkResult(olePut(objQueryTable, "TextFilePromptOnRefresh", false));
                                                checkResult(olePut(objQueryTable, "TextFilePlatform", 437));
                                                checkResult(olePut(objQueryTable, "TextFileStartRow", 1));
                                                checkResult(olePut(objQueryTable, "TextFileParseType", xlDelimited));
                                                checkResult(olePut(objQueryTable, "TextFileTextQualifier", xlTextQualifierDoubleQuote));
                                                checkResult(olePut(objQueryTable, "TextFileConsecutiveDelimiter", false));
                                                checkResult(olePut(objQueryTable, "TextFileTabDelimiter", true));
                                                checkResult(olePut(objQueryTable, "TextFileSemicolonDelimiter", false));
                                                checkResult(olePut(objQueryTable, "TextFileCommaDelimiter", false));
                                                checkResult(olePut(objQueryTable, "TextFileSpaceDelimiter", false));
                                                // checkResult(olePut(objQueryTable, "TextFileColumnDataTypes", "Array(2, 2, 2, 2, 2, 2, 2)"));
                                                checkResult(olePut(objQueryTable, "TextFileTrailingMinusNumbers", true));
                                                clear(args);
                                                put(args, "BackgroundQuery", false);
                                                checkResult(oleMethod(objQueryTable, cMethodRefresh, args))
                                                }
                                                else {
                                                        errorBox("Add QueryTable failed.");
                                                }
                                        }
                                        else {
                                                errorBox("Get QueryTables failed.");
                                        }
                                }
                                else {
                                        errorBox("Get Range failed.");
                                }
                        }
                        else {
                                errorBox("Get ActiveSheet failed.");
                        }
                }
                else {
                        errorBox("Add WorkBook failed.");
                }
        }
        else {
                errorBox("Get WorkBooks failed.");
        }
}
else {
        errorBox("Open Excel failed.");
}

 


The property TextFileColumnDataTypes requires an array of integers, but DOORS DXL function olePut() does only support basic data types like int, bool and string but no integer arrays. The code example shows the VBA syntax for setting TextFileColumnDataTypes as comment.

Any ideas, how to overcome this problem?

 


rdratlos - Mon Jun 13 01:26:55 EDT 2011

Re: OLE Excel: How to Set Array Properties
Mathias Mamsch - Mon Jun 13 06:44:46 EDT 2011

Unfortunately I do not know a solution for this. I have tried a couple of times to get DOORS to work with OLE array types but I did not manage to get a working solution. But i can maybe suggest an alternative quick way to write a huge amount of data into excel quickly. Excel will recognize csv data too, when pasted from the clipboard. So what worked very well for me even on huge data set is to create a big buffer with the csv/tsv data in it, copy it to the clipboard and paste it to excel. See the below example which builds up a buffer containing 5000 lines, 10 columns, containing 10% escaped strings (with newlines, line breaks). Paste this to excel, on my computer this is pretty fast.

Maybe this helps, regards, Mathias
  

pragma runLim, 0
 
Regexp gListviewEscapeCharacters = regexp "[\""""\\n\\t]"
 
// This function will escape  newline (\n)  tab (\t)  and  hyphens (") 
// to export them to a tab separated string
// i.e.    Hello\n"you"\t      will become      "Hello\n""You""\t"
// This can be pasted to Excel.
 
void AddEscapedStringToBuffer (string s, Buffer b) {
    // Buffer bResult = create()
    int i
    bool needEscape = false    
    if (null s) return
    // check if we need to escape the string
    if (gListviewEscapeCharacters s) needEscape = true
    
    if ( needEscape ) {    
        b += "\""
        for i in 0:length(s)-1 do {            
            if ( s[i] == '\"' || s[i] == '"' || s[i] == '"' || s[i] == '"') b += s[i]        
            b += s[i]            
        }    
        b += "\""
    } else { 
        // if not: just add it unescaped ...
        b += s
        return
    }
    
}
 
Buffer b = create(1000000) 
 
string s1 = "Hello \"Excel\"!\nI can haz cheezeburgerz!"
string s2 = "Hello Excel"
 
for i in 0:5000 do {
   for j in 0:10 do {
     if ((i % 10) == j)  
          AddEscapedStringToBuffer (s1, b)
       else 
            AddEscapedStringToBuffer (s2, b)
          
       b += '\t'
   }
   b+='\n'
}
 
copyToClipboard tempStringOf b
ack "Paste To Excel"

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

Re: OLE Excel: How to Set Array Properties
rdratlos - Thu Jun 16 02:01:47 EDT 2011

Mathias Mamsch - Mon Jun 13 06:44:46 EDT 2011

Unfortunately I do not know a solution for this. I have tried a couple of times to get DOORS to work with OLE array types but I did not manage to get a working solution. But i can maybe suggest an alternative quick way to write a huge amount of data into excel quickly. Excel will recognize csv data too, when pasted from the clipboard. So what worked very well for me even on huge data set is to create a big buffer with the csv/tsv data in it, copy it to the clipboard and paste it to excel. See the below example which builds up a buffer containing 5000 lines, 10 columns, containing 10% escaped strings (with newlines, line breaks). Paste this to excel, on my computer this is pretty fast.

Maybe this helps, regards, Mathias
  

pragma runLim, 0
 
Regexp gListviewEscapeCharacters = regexp "[\""""\\n\\t]"
 
// This function will escape  newline (\n)  tab (\t)  and  hyphens (") 
// to export them to a tab separated string
// i.e.    Hello\n"you"\t      will become      "Hello\n""You""\t"
// This can be pasted to Excel.
 
void AddEscapedStringToBuffer (string s, Buffer b) {
    // Buffer bResult = create()
    int i
    bool needEscape = false    
    if (null s) return
    // check if we need to escape the string
    if (gListviewEscapeCharacters s) needEscape = true
    
    if ( needEscape ) {    
        b += "\""
        for i in 0:length(s)-1 do {            
            if ( s[i] == '\"' || s[i] == '"' || s[i] == '"' || s[i] == '"') b += s[i]        
            b += s[i]            
        }    
        b += "\""
    } else { 
        // if not: just add it unescaped ...
        b += s
        return
    }
    
}
 
Buffer b = create(1000000) 
 
string s1 = "Hello \"Excel\"!\nI can haz cheezeburgerz!"
string s2 = "Hello Excel"
 
for i in 0:5000 do {
   for j in 0:10 do {
     if ((i % 10) == j)  
          AddEscapedStringToBuffer (s1, b)
       else 
            AddEscapedStringToBuffer (s2, b)
          
       b += '\t'
   }
   b+='\n'
}
 
copyToClipboard tempStringOf b
ack "Paste To Excel"

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

Dear Mathias,

thank you very much for your quick and very helpful answer. In the meantime, I also tried another option. Instead of importing the csv file into Excel using the DXL OLE interface, I put a CSV import macro into the new Excel workbook using the DXL OLE functions and started it from DXL. It worked well, but had the major drawback, that starting from Excel 2003, the user has to explicitly allow access to the Visual Basic project from outside Excel. Otherwise, DXL will stop with OLE failure. As this is a non-acceptable security issue, I didn't further follow this option.

Then I took your script, modified it to my needs and let it import my CSV file data. The nearly 25000 lines where instantly imported into Excel. Great script.

Here's the script for all interested people: 

pragma runLim, 0
 
// Taken from http://www.baselinesinc.com/?page_id=11&filerepoaction=showpost&filepost_id=10
#include <addins/ExcelFunctions.inc>
 
OleAutoObj objCells = null;
OleAutoObj objActiveWindow = null;
 
string csvFileName = "C:\\Documents and Settings\\I149635D\\Local Settings\\Temp\\DP119.csv"
 
//=================================================================
// Function:     fileSize()
// Usage:       Tests if a file exists and returns file size
//
// Input:       Filename
//
// Output:      File size in [byte]
//              0: File not found
//
// Required:    Variable input_definition_file_folder has to point
//              to the folder where file "filename" is located
//=================================================================
int fileSize(string filename)
{
        int fileSize = 0
        Stat s
        s = create(filename)
        if (!null s) {
                fileSize = size(s)
                delete s
        }
        return fileSize
}
 
 
//=================================================================
// Function:    copyCsvToClipboard()
// Usage:       Open a csv file and copy its content to system 
//              clipboard
//
// Input:       Filename of the csv file
//
// Output:      Result: true/false
//=================================================================
bool copyCsvToClipboard (string fname)
{
        Stream csvFile
        Buffer b
        
        int csvFileSize = 0 
        
        csvFileSize = fileSize(fname)
        if(csvFileSize == 0 || !canOpenFile(fname "", false))
        {
                error("CSV file '" fname "' with the verification results not found.")
                return false
        }
 
        b = create(csvFileSize + 1000)
        csvFile = read csvFileName
        if(null csvFile)
        {
                error("Open CSV file '" fname "' failed.")
                return false
        }
        
        csvFile >> b
        
        // Bug in DXL: copyToClipboard always returns false???
        // result = copyToClipboard(tempStringOf(b))
        copyToClipboard(tempStringOf(b))
        
        close csvFile
        delete b
        
        return true
}
 
 
///=================================================================
// Function:    importCSVfile()
// Usage:       Opens a csv file and copy and paste its content  
//              into Excel spreadsheet
//
// Input:       none
//
// Output:      Result: true/false
//=================================================================
bool importCSVfile()
{
        bool failed = false
 
        if (!openExcel) {
                print "Open Excel failed."
                return false
        }
        makeVisible(objExcel)
        if (!getWorkBooks) {
                print "Get WorkBooks failed."
                closeExcel
                return false
        }
        if (!addWorkBook) {
                print "Add WorkBook failed."
                closeExcel
                return false
        }
        if(!getActiveSheet) {
                print "Get ActiveSheet failed."
                closeExcel
                return false
        }
        // Get the Cells (all rows in the active sheet) property
        checkResult(oleGet(objSheet, cPropertyCells, objCells))
        if (null objCells) {
                print "Get Cells failed."
                closeExcel
                return false
        }
        // Set the format of all cells to be "Text" and enable "Wrap Text"
        failed = !checkResult(olePut(objCells, cPropertyNumberFormat, "@")) || failed
        failed = !checkResult(olePut(objCells, cPropertyWrapText, true)) || failed
        if (failed) {
                print "Cannot set cells properties."
                closeExcel
                return false
        }
        // Now copy the content of the csv file to system clipboard
        if (!copyCsvToClipboard(csvFileName)) {
                print "Cannot copy verification results to system clipboard."
                closeExcel
                return false
        }
        // Select first cell in sheet
        if (!getCell(1, 1)) {
                print "Select first cell in Excel spreadsheet failed."
                closeExcel
                return false
        }
        // Paste the verification results into the Excel spreadsheet
        if (!checkResult(oleMethod(objSheet, cMethodPaste))) {
                print "Could not paste verification results from clipboard into Excel spreadsheet."
                closeExcel
                return false
        }
        return true
}

 


The only drawback, which is valid for both, import and copy and paste of text data from CSV file, is that Excel (at least the 2003) version, does not correctly handle the degree character. Excel imports it as special Unicode character, which has to be replaced manually after import.

Best regards

Thomas

 

Re: OLE Excel: How to Set Array Properties
Mathias Mamsch - Thu Jun 16 03:53:27 EDT 2011

rdratlos - Thu Jun 16 02:01:47 EDT 2011

Dear Mathias,

thank you very much for your quick and very helpful answer. In the meantime, I also tried another option. Instead of importing the csv file into Excel using the DXL OLE interface, I put a CSV import macro into the new Excel workbook using the DXL OLE functions and started it from DXL. It worked well, but had the major drawback, that starting from Excel 2003, the user has to explicitly allow access to the Visual Basic project from outside Excel. Otherwise, DXL will stop with OLE failure. As this is a non-acceptable security issue, I didn't further follow this option.

Then I took your script, modified it to my needs and let it import my CSV file data. The nearly 25000 lines where instantly imported into Excel. Great script.

Here's the script for all interested people: 

pragma runLim, 0
 
// Taken from http://www.baselinesinc.com/?page_id=11&filerepoaction=showpost&filepost_id=10
#include <addins/ExcelFunctions.inc>
 
OleAutoObj objCells = null;
OleAutoObj objActiveWindow = null;
 
string csvFileName = "C:\\Documents and Settings\\I149635D\\Local Settings\\Temp\\DP119.csv"
 
//=================================================================
// Function:     fileSize()
// Usage:       Tests if a file exists and returns file size
//
// Input:       Filename
//
// Output:      File size in [byte]
//              0: File not found
//
// Required:    Variable input_definition_file_folder has to point
//              to the folder where file "filename" is located
//=================================================================
int fileSize(string filename)
{
        int fileSize = 0
        Stat s
        s = create(filename)
        if (!null s) {
                fileSize = size(s)
                delete s
        }
        return fileSize
}
 
 
//=================================================================
// Function:    copyCsvToClipboard()
// Usage:       Open a csv file and copy its content to system 
//              clipboard
//
// Input:       Filename of the csv file
//
// Output:      Result: true/false
//=================================================================
bool copyCsvToClipboard (string fname)
{
        Stream csvFile
        Buffer b
        
        int csvFileSize = 0 
        
        csvFileSize = fileSize(fname)
        if(csvFileSize == 0 || !canOpenFile(fname "", false))
        {
                error("CSV file '" fname "' with the verification results not found.")
                return false
        }
 
        b = create(csvFileSize + 1000)
        csvFile = read csvFileName
        if(null csvFile)
        {
                error("Open CSV file '" fname "' failed.")
                return false
        }
        
        csvFile >> b
        
        // Bug in DXL: copyToClipboard always returns false???
        // result = copyToClipboard(tempStringOf(b))
        copyToClipboard(tempStringOf(b))
        
        close csvFile
        delete b
        
        return true
}
 
 
///=================================================================
// Function:    importCSVfile()
// Usage:       Opens a csv file and copy and paste its content  
//              into Excel spreadsheet
//
// Input:       none
//
// Output:      Result: true/false
//=================================================================
bool importCSVfile()
{
        bool failed = false
 
        if (!openExcel) {
                print "Open Excel failed."
                return false
        }
        makeVisible(objExcel)
        if (!getWorkBooks) {
                print "Get WorkBooks failed."
                closeExcel
                return false
        }
        if (!addWorkBook) {
                print "Add WorkBook failed."
                closeExcel
                return false
        }
        if(!getActiveSheet) {
                print "Get ActiveSheet failed."
                closeExcel
                return false
        }
        // Get the Cells (all rows in the active sheet) property
        checkResult(oleGet(objSheet, cPropertyCells, objCells))
        if (null objCells) {
                print "Get Cells failed."
                closeExcel
                return false
        }
        // Set the format of all cells to be "Text" and enable "Wrap Text"
        failed = !checkResult(olePut(objCells, cPropertyNumberFormat, "@")) || failed
        failed = !checkResult(olePut(objCells, cPropertyWrapText, true)) || failed
        if (failed) {
                print "Cannot set cells properties."
                closeExcel
                return false
        }
        // Now copy the content of the csv file to system clipboard
        if (!copyCsvToClipboard(csvFileName)) {
                print "Cannot copy verification results to system clipboard."
                closeExcel
                return false
        }
        // Select first cell in sheet
        if (!getCell(1, 1)) {
                print "Select first cell in Excel spreadsheet failed."
                closeExcel
                return false
        }
        // Paste the verification results into the Excel spreadsheet
        if (!checkResult(oleMethod(objSheet, cMethodPaste))) {
                print "Could not paste verification results from clipboard into Excel spreadsheet."
                closeExcel
                return false
        }
        return true
}

 


The only drawback, which is valid for both, import and copy and paste of text data from CSV file, is that Excel (at least the 2003) version, does not correctly handle the degree character. Excel imports it as special Unicode character, which has to be replaced manually after import.

Best regards

Thomas

 

In fact there are a couple of other characters that will not be imported correctly like german umlauts. I could resolve that by inserting a
  

convertToCodePage(1, buffer)

 


before copying the buffer to the clipboard, where 1 = OEM Latin European codepage (or whatever you may need). Maybe that will help you too, regards, Mathias

 

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

Re: OLE Excel: How to Set Array Properties
rdratlos - Thu Jun 16 04:01:57 EDT 2011

rdratlos - Thu Jun 16 02:01:47 EDT 2011

Dear Mathias,

thank you very much for your quick and very helpful answer. In the meantime, I also tried another option. Instead of importing the csv file into Excel using the DXL OLE interface, I put a CSV import macro into the new Excel workbook using the DXL OLE functions and started it from DXL. It worked well, but had the major drawback, that starting from Excel 2003, the user has to explicitly allow access to the Visual Basic project from outside Excel. Otherwise, DXL will stop with OLE failure. As this is a non-acceptable security issue, I didn't further follow this option.

Then I took your script, modified it to my needs and let it import my CSV file data. The nearly 25000 lines where instantly imported into Excel. Great script.

Here's the script for all interested people: 

pragma runLim, 0
 
// Taken from http://www.baselinesinc.com/?page_id=11&filerepoaction=showpost&filepost_id=10
#include <addins/ExcelFunctions.inc>
 
OleAutoObj objCells = null;
OleAutoObj objActiveWindow = null;
 
string csvFileName = "C:\\Documents and Settings\\I149635D\\Local Settings\\Temp\\DP119.csv"
 
//=================================================================
// Function:     fileSize()
// Usage:       Tests if a file exists and returns file size
//
// Input:       Filename
//
// Output:      File size in [byte]
//              0: File not found
//
// Required:    Variable input_definition_file_folder has to point
//              to the folder where file "filename" is located
//=================================================================
int fileSize(string filename)
{
        int fileSize = 0
        Stat s
        s = create(filename)
        if (!null s) {
                fileSize = size(s)
                delete s
        }
        return fileSize
}
 
 
//=================================================================
// Function:    copyCsvToClipboard()
// Usage:       Open a csv file and copy its content to system 
//              clipboard
//
// Input:       Filename of the csv file
//
// Output:      Result: true/false
//=================================================================
bool copyCsvToClipboard (string fname)
{
        Stream csvFile
        Buffer b
        
        int csvFileSize = 0 
        
        csvFileSize = fileSize(fname)
        if(csvFileSize == 0 || !canOpenFile(fname "", false))
        {
                error("CSV file '" fname "' with the verification results not found.")
                return false
        }
 
        b = create(csvFileSize + 1000)
        csvFile = read csvFileName
        if(null csvFile)
        {
                error("Open CSV file '" fname "' failed.")
                return false
        }
        
        csvFile >> b
        
        // Bug in DXL: copyToClipboard always returns false???
        // result = copyToClipboard(tempStringOf(b))
        copyToClipboard(tempStringOf(b))
        
        close csvFile
        delete b
        
        return true
}
 
 
///=================================================================
// Function:    importCSVfile()
// Usage:       Opens a csv file and copy and paste its content  
//              into Excel spreadsheet
//
// Input:       none
//
// Output:      Result: true/false
//=================================================================
bool importCSVfile()
{
        bool failed = false
 
        if (!openExcel) {
                print "Open Excel failed."
                return false
        }
        makeVisible(objExcel)
        if (!getWorkBooks) {
                print "Get WorkBooks failed."
                closeExcel
                return false
        }
        if (!addWorkBook) {
                print "Add WorkBook failed."
                closeExcel
                return false
        }
        if(!getActiveSheet) {
                print "Get ActiveSheet failed."
                closeExcel
                return false
        }
        // Get the Cells (all rows in the active sheet) property
        checkResult(oleGet(objSheet, cPropertyCells, objCells))
        if (null objCells) {
                print "Get Cells failed."
                closeExcel
                return false
        }
        // Set the format of all cells to be "Text" and enable "Wrap Text"
        failed = !checkResult(olePut(objCells, cPropertyNumberFormat, "@")) || failed
        failed = !checkResult(olePut(objCells, cPropertyWrapText, true)) || failed
        if (failed) {
                print "Cannot set cells properties."
                closeExcel
                return false
        }
        // Now copy the content of the csv file to system clipboard
        if (!copyCsvToClipboard(csvFileName)) {
                print "Cannot copy verification results to system clipboard."
                closeExcel
                return false
        }
        // Select first cell in sheet
        if (!getCell(1, 1)) {
                print "Select first cell in Excel spreadsheet failed."
                closeExcel
                return false
        }
        // Paste the verification results into the Excel spreadsheet
        if (!checkResult(oleMethod(objSheet, cMethodPaste))) {
                print "Could not paste verification results from clipboard into Excel spreadsheet."
                closeExcel
                return false
        }
        return true
}

 


The only drawback, which is valid for both, import and copy and paste of text data from CSV file, is that Excel (at least the 2003) version, does not correctly handle the degree character. Excel imports it as special Unicode character, which has to be replaced manually after import.

Best regards

Thomas

 

For all interested people here's also a code example for import of a csv file into Excel using a macro, that is created, transfered to Excel and started within Excel by a DXL script:
  

#include <addins/ExcelFunctions.inc>
 
const string cPropertyCountOfLines = "CountOfLines"
const string cPropertyCodeModule = "CodeModule"
const string cPropertyVBProject = "VBProject"
const string cCollectionVBComponents = "VBComponents"
const string cMethodRefresh = "Refresh"
const string cMethodInsertLines = "InsertLines"
 
const int vbext_ct_StdModule = 1
 
OleAutoObj objVBProject = null;
OleAutoObj objVBComponents = null;
OleAutoObj objVBComponent = null;
OleAutoObj objCodeModule = null;
 
 
const string dxlScriptName = "DF Verification"
 
 
/* 
 * Function getImportVBMacro:
 * Writes the Excel VBA macro for csv file import into a buffer
 * Adapt the array for the TextFileColumnDataTypes property according to your needs
 *
 */
string getImportVBMacro(Buffer &bVBmacro, string dxlScriptName, string csvFileName)
{
    string macroName = "ImportDFVerificationResult"
        
        bVBmacro += "Sub "
        bVBmacro += macroName
        bVBmacro += "()\n"
        bVBmacro += "'\n"
        bVBmacro += "' ImportDFVerificationResult Macro\n"
        bVBmacro += "' created on " 
        bVBmacro += dateAndTime(today) ""
        bVBmacro += " by DOORS DXL script \""
        bVBmacro += dxlScriptName
        bVBmacro += "\"\n"
        bVBmacro += "'\n"
        bVBmacro += "    With ActiveSheet.QueryTables.Add(Connection:= \"TEXT;"
        bVBmacro += csvFileName
        bVBmacro += "\", Destination:=Range(\"A1\"))\n"
        bVBmacro += "        .Name = \"Import CSV1\"\n"
        bVBmacro += "        .FieldNames = True\n"
        bVBmacro += "        .RowNumbers = False\n"
        bVBmacro += "        .FillAdjacentFormulas = False\n"
        bVBmacro += "        .PreserveFormatting = True\n"
        bVBmacro += "        .RefreshOnFileOpen = False\n"
        bVBmacro += "        .RefreshStyle = xlInsertDeleteCells\n"
        bVBmacro += "        .SavePassword = False\n"
        bVBmacro += "        .SaveData = True\n"
        bVBmacro += "        .AdjustColumnWidth = True\n"
        bVBmacro += "        .RefreshPeriod = 0\n"
        bVBmacro += "        .TextFilePromptOnRefresh = False\n"
        bVBmacro += "        .TextFilePlatform = 437\n"
        bVBmacro += "        .TextFileStartRow = 1\n"
        bVBmacro += "        .TextFileParseType = xlDelimited\n"
        bVBmacro += "        .TextFileTextQualifier = xlTextQualifierDoubleQuote\n"
        bVBmacro += "        .TextFileConsecutiveDelimiter = False\n"
        bVBmacro += "        .TextFileTabDelimiter = True\n"
        bVBmacro += "        .TextFileSemicolonDelimiter = False\n"
        bVBmacro += "        .TextFileCommaDelimiter = False\n"
        bVBmacro += "        .TextFileSpaceDelimiter = False\n"
        bVBmacro += "        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2)\n"
        bVBmacro += "        .TextFileTrailingMinusNumbers = True\n"
        bVBmacro += "        .Refresh BackgroundQuery:=False\n"
        bVBmacro += "    End With\n"
        bVBmacro += "    Cells.Select\n"
        bVBmacro += "    With Selection\n"
        bVBmacro += "        .WrapText = True\n"
        bVBmacro += "    End With\n"
        bVBmacro += "    ActiveWindow.SplitRow = 2\n"
        bVBmacro += "    ActiveWindow.FreezePanes = True\n"
        bVBmacro += "    Range(\"A3\").Select\n"
        bVBmacro += "End Sub\n"
        
        return macroName
}
 
/* 
 * Function addExcelVbaLineOfCode:
 */
bool addExcelVbaLineOfCode(OleAutoObj &objCodeModule, string lineOfCode)
{
        int lineNum = 0
        
        if (null objCodeModule) {
                print "ERROR: Excel VBA code module not initialised."
                return false
        }
        if (!checkResult(oleGet(objCodeModule, cPropertyCountOfLines, lineNum))) {
                print "ERROR: Cannot retrieve number of lines of code in Excel VBA code module."
                return false
        }
        clear(args);
        put(args, ++lineNum)
        put(args, lineOfCode)
        if (!checkResult(oleMethod(objCodeModule, cMethodInsertLines, args))) {
                print "ERROR: Cannot insert line of code at line: " lineNum " within Excel VBA code module."
                return false
        }
        
        return true
}
 
 
///=================================================================
// Function:    importCSVfile()
// Usage:       Open Excel, store an import CSV file macro and  
//              excecute it to import the CSV file into Excel 
//              spreadsheet
// Input:       CSV file name
//
// Output:      Result: true/false
//=================================================================
bool importCSVfile(string csvFileName)
{
        string importMacroName = ""
        Buffer bMacro
        int from = 0 
        bool macroTransfered = true
        Regexp line = regexp ".*"      // matches up to newline 
 
        
        if (!openExcel) {
                print "Open Excel failed."
                return false
        }
        makeVisible(objExcel)
        if (!getWorkBooks) {
                print "Get WorkBooks failed."
                closeExcel
                return false
        }
        if (!addWorkBook) {
                print "Add WorkBook failed."
                closeExcel
                return false
        }
        // Access the Visual Basic project
        checkResult(oleGet(objWorkbook, cPropertyVBProject, objVBProject));   // gets the Visual Basic project
        if (null objVBProject) {
                print "Get Visual Basic Project failed.\n"
                print "Please check if programmatic access to VBA is enabled. See http://support.microsoft.com/kb/282830/EN-US for further details.\n"
                closeExcel
                return false
        }
        // Get Visual Basic components
        checkResult(oleGet(objVBProject, cCollectionVBComponents, objVBComponents));   // gets the Visual Basic components
        if (null objVBComponents) {
                print "Get Visual Basic components list failed.\n"
                closeExcel
                return false
        }
        // Add VB component
        clear(args);
        put(args, vbext_ct_StdModule);
        checkResult(oleMethod(objVBComponents, cMethodAdd, args, objVBComponent))
        if (null objVBComponent) {
                print "Add Visual Basic component.\n"
                closeExcel
                return false
        }
        // Define a name for the component
        checkResult(olePut(objVBComponent, cPropertyName, "DxlReferencedCode"))
        // Get access to VB code module
        checkResult(oleGet(objVBComponent, cPropertyCodeModule, objCodeModule))
        if (null objCodeModule) {
                print "Get Code Module failed.\n"
                closeExcel
                return false
        }
        // Transfer macro line by line to CodeModule
        bMacro = create
        importMacroName = getImportVBMacro(bMacro, dxlScriptName, csvFileName)
        while (search(line, bMacro, from)) { 
                // takes a line at a time from buffer bMacro 
                int offset = end 0 
                // end of the match within buffer 
                string codeLine = bMacro[from:from+offset] 
                from += offset + 2      
                 // move 'from' over any newline 
                if (null codeLine) {        // we are done
                        break 
                }
                // print codeLine "\n"
                macroTransfered = macroTransfered && addExcelVbaLineOfCode(objCodeModule, codeLine)
        } 
        delete bMacro
        if (!macroTransfered || null importMacroName) {
                print "ERROR: Could not write CSV import macro into Excel VBA code module.\n"
                closeExcel
                return false
        }
        if (!runExcelMacro(importMacroName)) {
                print "ERROR: Could not run Excel CSV import macro \"" importMacroName "\".\n"
                closeExcel
                return false
        }
        return true
}

 


The script uses an Excel functions library that can be obtained from here.

As already outlined, this cript requires "programmatic" access to Excel VBA (see Microsoft Knowledge Base 282830), which is not safe. I recommend to use a script based on Mathias' proposal.

Best regards

Thomas

 

Re: OLE Excel: How to Set Array Properties
rdratlos - Thu Jun 16 05:39:46 EDT 2011

Mathias Mamsch - Thu Jun 16 03:53:27 EDT 2011

In fact there are a couple of other characters that will not be imported correctly like german umlauts. I could resolve that by inserting a
  

convertToCodePage(1, buffer)

 


before copying the buffer to the clipboard, where 1 = OEM Latin European codepage (or whatever you may need). Maybe that will help you too, regards, Mathias

 

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

This function didn't help. I tried it with:
  • CP_LATIN1 // ANSI Latin-1
  • CP_UTF8 // Unicode UTF-8 encoding
  • CP_UNICODE // UTF-16 little-endian encoding (= CP_UTF16_LE).
None of them solved this issue. In addition, convertToCodepage caused a DOORS crash when called with codepage CP_UNICODE (see APAR PM12462).

My current workaround is to add a line at the beginning of the csv file with the main used special characters. After import into Excel, I take cell by cell the wrong imported (special) characters and let Excel replace them with original correct (special) character. When finished, I delete the first row.

br Thomas

Re: OLE Excel: How to Set Array Properties
Mathias Mamsch - Thu Jun 16 07:59:52 EDT 2011

rdratlos - Thu Jun 16 04:01:57 EDT 2011

For all interested people here's also a code example for import of a csv file into Excel using a macro, that is created, transfered to Excel and started within Excel by a DXL script:
  

#include <addins/ExcelFunctions.inc>
 
const string cPropertyCountOfLines = "CountOfLines"
const string cPropertyCodeModule = "CodeModule"
const string cPropertyVBProject = "VBProject"
const string cCollectionVBComponents = "VBComponents"
const string cMethodRefresh = "Refresh"
const string cMethodInsertLines = "InsertLines"
 
const int vbext_ct_StdModule = 1
 
OleAutoObj objVBProject = null;
OleAutoObj objVBComponents = null;
OleAutoObj objVBComponent = null;
OleAutoObj objCodeModule = null;
 
 
const string dxlScriptName = "DF Verification"
 
 
/* 
 * Function getImportVBMacro:
 * Writes the Excel VBA macro for csv file import into a buffer
 * Adapt the array for the TextFileColumnDataTypes property according to your needs
 *
 */
string getImportVBMacro(Buffer &bVBmacro, string dxlScriptName, string csvFileName)
{
    string macroName = "ImportDFVerificationResult"
        
        bVBmacro += "Sub "
        bVBmacro += macroName
        bVBmacro += "()\n"
        bVBmacro += "'\n"
        bVBmacro += "' ImportDFVerificationResult Macro\n"
        bVBmacro += "' created on " 
        bVBmacro += dateAndTime(today) ""
        bVBmacro += " by DOORS DXL script \""
        bVBmacro += dxlScriptName
        bVBmacro += "\"\n"
        bVBmacro += "'\n"
        bVBmacro += "    With ActiveSheet.QueryTables.Add(Connection:= \"TEXT;"
        bVBmacro += csvFileName
        bVBmacro += "\", Destination:=Range(\"A1\"))\n"
        bVBmacro += "        .Name = \"Import CSV1\"\n"
        bVBmacro += "        .FieldNames = True\n"
        bVBmacro += "        .RowNumbers = False\n"
        bVBmacro += "        .FillAdjacentFormulas = False\n"
        bVBmacro += "        .PreserveFormatting = True\n"
        bVBmacro += "        .RefreshOnFileOpen = False\n"
        bVBmacro += "        .RefreshStyle = xlInsertDeleteCells\n"
        bVBmacro += "        .SavePassword = False\n"
        bVBmacro += "        .SaveData = True\n"
        bVBmacro += "        .AdjustColumnWidth = True\n"
        bVBmacro += "        .RefreshPeriod = 0\n"
        bVBmacro += "        .TextFilePromptOnRefresh = False\n"
        bVBmacro += "        .TextFilePlatform = 437\n"
        bVBmacro += "        .TextFileStartRow = 1\n"
        bVBmacro += "        .TextFileParseType = xlDelimited\n"
        bVBmacro += "        .TextFileTextQualifier = xlTextQualifierDoubleQuote\n"
        bVBmacro += "        .TextFileConsecutiveDelimiter = False\n"
        bVBmacro += "        .TextFileTabDelimiter = True\n"
        bVBmacro += "        .TextFileSemicolonDelimiter = False\n"
        bVBmacro += "        .TextFileCommaDelimiter = False\n"
        bVBmacro += "        .TextFileSpaceDelimiter = False\n"
        bVBmacro += "        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2)\n"
        bVBmacro += "        .TextFileTrailingMinusNumbers = True\n"
        bVBmacro += "        .Refresh BackgroundQuery:=False\n"
        bVBmacro += "    End With\n"
        bVBmacro += "    Cells.Select\n"
        bVBmacro += "    With Selection\n"
        bVBmacro += "        .WrapText = True\n"
        bVBmacro += "    End With\n"
        bVBmacro += "    ActiveWindow.SplitRow = 2\n"
        bVBmacro += "    ActiveWindow.FreezePanes = True\n"
        bVBmacro += "    Range(\"A3\").Select\n"
        bVBmacro += "End Sub\n"
        
        return macroName
}
 
/* 
 * Function addExcelVbaLineOfCode:
 */
bool addExcelVbaLineOfCode(OleAutoObj &objCodeModule, string lineOfCode)
{
        int lineNum = 0
        
        if (null objCodeModule) {
                print "ERROR: Excel VBA code module not initialised."
                return false
        }
        if (!checkResult(oleGet(objCodeModule, cPropertyCountOfLines, lineNum))) {
                print "ERROR: Cannot retrieve number of lines of code in Excel VBA code module."
                return false
        }
        clear(args);
        put(args, ++lineNum)
        put(args, lineOfCode)
        if (!checkResult(oleMethod(objCodeModule, cMethodInsertLines, args))) {
                print "ERROR: Cannot insert line of code at line: " lineNum " within Excel VBA code module."
                return false
        }
        
        return true
}
 
 
///=================================================================
// Function:    importCSVfile()
// Usage:       Open Excel, store an import CSV file macro and  
//              excecute it to import the CSV file into Excel 
//              spreadsheet
// Input:       CSV file name
//
// Output:      Result: true/false
//=================================================================
bool importCSVfile(string csvFileName)
{
        string importMacroName = ""
        Buffer bMacro
        int from = 0 
        bool macroTransfered = true
        Regexp line = regexp ".*"      // matches up to newline 
 
        
        if (!openExcel) {
                print "Open Excel failed."
                return false
        }
        makeVisible(objExcel)
        if (!getWorkBooks) {
                print "Get WorkBooks failed."
                closeExcel
                return false
        }
        if (!addWorkBook) {
                print "Add WorkBook failed."
                closeExcel
                return false
        }
        // Access the Visual Basic project
        checkResult(oleGet(objWorkbook, cPropertyVBProject, objVBProject));   // gets the Visual Basic project
        if (null objVBProject) {
                print "Get Visual Basic Project failed.\n"
                print "Please check if programmatic access to VBA is enabled. See http://support.microsoft.com/kb/282830/EN-US for further details.\n"
                closeExcel
                return false
        }
        // Get Visual Basic components
        checkResult(oleGet(objVBProject, cCollectionVBComponents, objVBComponents));   // gets the Visual Basic components
        if (null objVBComponents) {
                print "Get Visual Basic components list failed.\n"
                closeExcel
                return false
        }
        // Add VB component
        clear(args);
        put(args, vbext_ct_StdModule);
        checkResult(oleMethod(objVBComponents, cMethodAdd, args, objVBComponent))
        if (null objVBComponent) {
                print "Add Visual Basic component.\n"
                closeExcel
                return false
        }
        // Define a name for the component
        checkResult(olePut(objVBComponent, cPropertyName, "DxlReferencedCode"))
        // Get access to VB code module
        checkResult(oleGet(objVBComponent, cPropertyCodeModule, objCodeModule))
        if (null objCodeModule) {
                print "Get Code Module failed.\n"
                closeExcel
                return false
        }
        // Transfer macro line by line to CodeModule
        bMacro = create
        importMacroName = getImportVBMacro(bMacro, dxlScriptName, csvFileName)
        while (search(line, bMacro, from)) { 
                // takes a line at a time from buffer bMacro 
                int offset = end 0 
                // end of the match within buffer 
                string codeLine = bMacro[from:from+offset] 
                from += offset + 2      
                 // move 'from' over any newline 
                if (null codeLine) {        // we are done
                        break 
                }
                // print codeLine "\n"
                macroTransfered = macroTransfered && addExcelVbaLineOfCode(objCodeModule, codeLine)
        } 
        delete bMacro
        if (!macroTransfered || null importMacroName) {
                print "ERROR: Could not write CSV import macro into Excel VBA code module.\n"
                closeExcel
                return false
        }
        if (!runExcelMacro(importMacroName)) {
                print "ERROR: Could not run Excel CSV import macro \"" importMacroName "\".\n"
                closeExcel
                return false
        }
        return true
}

 


The script uses an Excel functions library that can be obtained from here.

As already outlined, this cript requires "programmatic" access to Excel VBA (see Microsoft Knowledge Base 282830), which is not safe. I recommend to use a script based on Mathias' proposal.

Best regards

Thomas

 

You might want to try the following script to find the right codepage for your system. For me the OEM Codepages did the trick. Just change the string s to contain the special characters that come out wrong.

You need to keep in mind though, that if you read characters from a file, you need to know the encoding of the file and probably need to convert to UTF-8 first by executing convertFromCodepage ...

it would be nice to know your systems locale, the installed language of DOORS and Excel and which codepage did the trick for you. Hope this helps, regards, Mathias
  

OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
OleAutoArgs args = create
 
void openExcel() {
   OleAutoObj ex = oleCreateAutoObject "Excel.Application"
   oleGet (ex, "Workbooks", wbs) 
   oleMethod (wbs, "Add", args, wb)
   olePut(ex, "Visible", true)
   put(args, 1)
   oleGet (ex, "Worksheets", args, ws) 
} 
 
void pasteString (int y, int x, string s) {
                clear args
                        put (args, y)
                        put (args, x)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
                        copyToClipboard s
                        print oleMethod (ws, "Paste",args)
}
 
openExcel() 
// insert special characters here 
string s = "ä ö ü ° ^"
 
int cp 
int i = 1
for cp in supportedCodepages do {
  pasteString (i+1,1, convertToCodepage(cp, s) ) 
  pasteString (i+1,2, cp "") 
  pasteString (i+1,3, codepageName cp "") 
  i++
}

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

Re: OLE Excel: How to Set Array Properties
rdratlos - Mon Jun 20 09:18:37 EDT 2011

Mathias Mamsch - Thu Jun 16 07:59:52 EDT 2011

You might want to try the following script to find the right codepage for your system. For me the OEM Codepages did the trick. Just change the string s to contain the special characters that come out wrong.

You need to keep in mind though, that if you read characters from a file, you need to know the encoding of the file and probably need to convert to UTF-8 first by executing convertFromCodepage ...

it would be nice to know your systems locale, the installed language of DOORS and Excel and which codepage did the trick for you. Hope this helps, regards, Mathias
  

OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
OleAutoArgs args = create
 
void openExcel() {
   OleAutoObj ex = oleCreateAutoObject "Excel.Application"
   oleGet (ex, "Workbooks", wbs) 
   oleMethod (wbs, "Add", args, wb)
   olePut(ex, "Visible", true)
   put(args, 1)
   oleGet (ex, "Worksheets", args, ws) 
} 
 
void pasteString (int y, int x, string s) {
                clear args
                        put (args, y)
                        put (args, x)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
                        copyToClipboard s
                        print oleMethod (ws, "Paste",args)
}
 
openExcel() 
// insert special characters here 
string s = "ä ö ü ° ^"
 
int cp 
int i = 1
for cp in supportedCodepages do {
  pasteString (i+1,1, convertToCodepage(cp, s) ) 
  pasteString (i+1,2, cp "") 
  pasteString (i+1,3, codepageName cp "") 
  i++
}

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

Dear Mathias,
this did the trick. I was not aware of so many codepages supported by this DXL function. Codepage 850 works well on my system. Thank you very much for your support.
Best regards
Thomas

Here's the adapted code: 

pragma runLim, 0
 
// Taken from http://www.baselinesinc.com/?page_id=11&filerepoaction=showpost&filepost_id=10
#include <addins/ExcelFunctions.inc>
 
OleAutoObj objCells = null;
OleAutoObj objActiveWindow = null;
 
//=================================================================
// Function:     fileSize()
// Usage:       Tests if a file exists and returns file size
//
// Input:       Filename
//
// Output:      File size in [byte]
//              0: File not found
//
// Required:    Variable input_definition_file_folder has to point
//              to the folder where file "filename" is located
//=================================================================
int fileSize(string filename)
{
        int fileSize = 0
        Stat s
        s = create(filename)
        if (!null s) {
                fileSize = size(s)
                delete s
        }
        return fileSize
}
 
 
//=================================================================
// Function:    copyCsvToClipboard()
// Usage:       Open a csv file and copy its content to system 
//              clipboard
//
// Input:       Filename of the csv file
//
// Output:      Result: true/false
//=================================================================
bool copyCsvToClipboard (string fname)
{
        Stream csvFile
        Buffer b
        
        int csvFileSize = 0 
        
        csvFileSize = fileSize(fname)
        if(csvFileSize == 0 || !canOpenFile(fname "", false))
        {
                print "CSV file '" fname "' with the verification results not found."
                return false
        }
 
        b = create(csvFileSize + 1000)
        csvFile = read fname
        if(null csvFile)
        {
                print "Open CSV file '" fname "' failed."
                return false
        }
        
        csvFile >> b
        
        // Bug in DXL: copyToClipboard always returns false???
        // result = copyToClipboard(tempStringOf(b))
        copyToClipboard(convertToCodepage(850, tempStringOf(b)))
        
        close csvFile
        delete b
        
        return true
}
 
 
///=================================================================
// Function:    importCSVfile()
// Usage:       Opens a csv file and copy and paste its content  
//              into Excel spreadsheet
//
// Input:       none
//
// Output:      Result: true/false
//=================================================================
bool importCSVfile()
{
        bool failed = false
 
        if (!openExcel) {
                print "Open Excel failed."
                return false
        }
        makeVisible(objExcel)
        if (!getWorkBooks) {
                print "Get WorkBooks failed."
                closeExcel
                return false
        }
        if (!addWorkBook) {
                print "Add WorkBook failed."
                closeExcel
                return false
        }
        if(!getActiveSheet) {
                print "Get ActiveSheet failed."
                closeExcel
                return false
        }
        // Get the Cells (all rows in the active sheet) property
        checkResult(oleGet(objSheet, cPropertyCells, objCells))
        if (null objCells) {
                print "Get Cells failed."
                closeExcel
                return false
        }
        // Set the format of all cells to be "Text" and enable "Wrap Text"
        failed = !checkResult(olePut(objCells, cPropertyNumberFormat, "@")) || failed
        failed = !checkResult(olePut(objCells, cPropertyWrapText, true)) || failed
        if (failed) {
                print "Cannot set cells properties."
                closeExcel
                return false
        }
        // Now copy the content of the csv file to system clipboard
        if (!copyCsvToClipboard(csvFileName)) {
                print "Cannot copy verification results to system clipboard."
                closeExcel
                return false
        }
        // Select first cell in sheet
        if (!getCell(1, 1)) {
                print "Select first cell in Excel spreadsheet failed."
                closeExcel
                return false
        }
        // Paste the verification results into the Excel spreadsheet
        if (!checkResult(oleMethod(objSheet, cMethodPaste))) {
                print "Could not paste verification results from clipboard into Excel spreadsheet."
                closeExcel
                return false
        }
        return true
}
 
// For testing
// string csvFileName = "C:\\Documents and Settings\\xyz\\Local Settings\\Temp\\DP1.csv"
// importCSVfile(csvFileName)