Solution: (No real solution unless Microsoft makes changes, but rather a workaround)

Manually make a copy of the Data Source file before running the test, then while still utilizing Data Source,

data.xlsx

Scenario SecondCol Result

89       23        Value

11       800       Value

888      111       Value

1. Load data from the copied file
2. Update the data
3. Overwrite the copied file

The following is an example, data.xlsx is the Excel Data Source file, newdata.xlsx is the copy you make before running tests.

[TestMethod]
[DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\data.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=false", "Sheet1$", DataAccessMethod.Sequential)] // This is working Excel.xlsx connection!!! Excel (ODBC, Dsn)

public void ExcelReadWrite()
{
    //Open copied Excel file and create Excel object
    string projectName = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name;
    string dir = Environment.CurrentDirectory + @"\..\..\..\";
    string targetFilename = "newdata.xlsx";
    string targetFile = dir + projectName + @"\" + targetFilename;

    object missing = Type.Missing;
    object misValue = System.Reflection.Missing.Value;
    Excel.Application excel = new Excel.Application();
    Excel.Workbook wb = excel.Workbooks.Open(targetFile, true, false);
    Excel.Worksheet ws = wb.Sheets[1];
    Excel.Range wr = ws.UsedRange;
    int rowCount = wr.Rows.Count;
    int colCount = wr.Columns.Count;

    //make some example data update for current iteration 
    int row = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow); //current iteration data row. zero base
    int generatedData = Convert.ToInt32(TestContext.DataRow[0]) + Convert.ToInt32(TestContext.DataRow[1]); //read from DataSource
    ws.Cells[row + 2, 3] = generatedData; //Worksheet, not zero base, and header row counts
    // ======================================================
    ws = wb.ActiveSheet;
    excel.DisplayAlerts = false;

    wb.SaveAs(targetFile, Excel.XlFileFormat.xlWorkbookDefault, misValue,
                                         misValue, misValue, misValue,
                                         Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                                         misValue, misValue, misValue, misValue);
    wb.Close(missing, missing, missing);
    excel.Quit();
}

Don't try to copy the Data Source file within [TestInitialize] as that will be executed before every iteration as well.

Read/write values from Excel data source file

After some more googling and experiment, I finally managed to get it to work. In order to save time for me and someone who might face the same challenge again, I think it’s beneficial to document the connection strings and some key points to pay attention to here as later reference.

The following are the data source strings found in the MSDN page:

Data Source Types and Attributes

• CSV

[DataSource(“Microsoft.VisualStudio.TestTools.DataSource.CSV”, “|DataDirectory|\\data.csv”, “data#csv”, DataAccessMethod.Sequential), DeploymentItem(“data.csv”), TestMethod]

• Excel

[DataSource(“System.Data.Odbc”, “Dsn=ExcelFiles;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true”, “Sheet1$”, DataAccessMethod.Sequential), DeploymentItem(“Sheet1.xls”), TestMethod]

• Test case in Team Foundation Server

[DataSource(“Microsoft.VisualStudio.TestTools.DataSource.TestCase”, “http://vlm13261329:8080/tfs/DefaultCollection;Agile", “30”, DataAccessMethod.Sequential), TestMethod]

• XML

[DataSource(“Microsoft.VisualStudio.TestTools.DataSource.XML”, “|DataDirectory|\\data.xml”, “Iterations”, DataAccessMethod.Sequential), DeploymentItem(“data.xml”), TestMethod]

• SQL Express

[DataSource(“System.Data.SqlClient”, “Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=True”, “Data”, DataAccessMethod.Sequential), TestMethod]

I know I can convert the “.xlsx” format into the old format “.xls”, but I rather have more options than less.

Here is the Attributes:

Data Source Types and Attributes

• Excel, “.xlsx” format

[TestMethod][DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\data.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet1$", DataAccessMethod.Sequential)]

where “data.xlsx” is the Excel file you want to use as Data Source, “Sheet1$” is the worksheet you are using in the Excel file. Using this to replace the normal [TestMethod] attribute before the test method.

If this is not working, check the following:

1. Add “data.xlsx” Excel file to your CodedUI Project:
   Right Click Project -> Add -> Existing Item… -> Add
2. Check “data.xlsx” Properties in VS, make sure to set “Copy to Output Directory” Property to “Copy always” or “Copy if newer”
3. If you get a connection error, it’s most likely due to the fact that you don’t have the 2007 Office System Driver installed for the OLEDB provider. (Thanks to blog.danbrust.net and incyclesoftware), you can download it from the following Microsoft link:
   Link 2007 Office System Driver: Data Connectivity Components

This will install the Office 2007 drivers. Since the Access and Excel file formats haven’t changed since 2007, these drivers work just fine with Office 2010, 2016, etc.

Another comment for CSV file: if CSV file is not working, check the encoding. It should be ANSI or UTF-8 without BOM.

.xlsx Excel files as Data Source in Coded UI test was originally published in jot down note on Medium, where people are continuing the conversation by highlighting and responding to this story.

Well, it turned out to be that it’s a problem with the company network I am working with. It doesn’t have problem from my home network.

Is there anything I can do with this restriction? As for some thoughts or solutions, it’s better to note them down right away.

Well, it turned out to be that it’s a problem with the company network I am working with. It doesn’t have problem from my home network.

Is there anything I can do with this restriction? As for some thoughts or solutions, it’s better to note them down right away.

My Sites loading forever was originally published in jot down note on Medium, where people are continuing the conversation by highlighting and responding to this story.

[code lang=”text” light=”true”]
set expandtab
set shiftwidth=4
set tabstop=4
set smartindent
set number
color slate
set guifont=peep:h11:cOEM
set linespace=0
set guioptions=egrLtm
[/code]

My .vimrc settings was originally published in jot down note on Medium, where people are continuing the conversation by highlighting and responding to this story.

[code lang=”text” light=”true”]
set expandtab
set shiftwidth=4
set tabstop=4
set smartindent
set number
color slate
set guifont=peep:h11:cOEM
set linespace=0
set guioptions=egrLtm
[/code]

“ Add menu bar
set guioption+=m

“ Add toolbar
set guioptions+=T

“ Remove menu bar
set guioptions-=m

“ Remove toolbar
set guioptions-=T

While already in gvim, enter command line mode, for example:

:set guioptions-=m

“ Add menu bar
set guioption+=m

“ Add toolbar
set guioptions+=T

“ Remove menu bar
set guioptions-=m

“ Remove toolbar
set guioptions-=T

While already in gvim, enter command line mode, for example:

:set guioptions-=m

Add or Remove the menu bar and toolbar from gvim was originally published in jot down note on Medium, where people are continuing the conversation by highlighting and responding to this story.

1. Download the client for your platform (Windows, OS X, Linux x86, Linux x64)
2. Unzip the archive in a convenient directory.
3. Add the the executable to your system/shell $PATH by:
4. moving it to a directory in your $PATH,
5. creating a symlink from a directory in your $PATH, or
6. creating a shell alias for the executable.
7. Confirm that the client is installed correctly by running stackato -v or stackato help.

To connect the stackato client from you favorite command line interface, you will need to specify the API Endpoint, which is the hostname prepended with “api.”: api.stackato-pry5.local

Target and Login

PS C:\stackato> stackato target api.stackato-pry5.local
Successfully targeted to [https://api.stackato-pry5.local]
PS C:\stackato> stackato login
Attempting login to [https://api.stackato-pry5.local]
Email: <your email account>@foobar.com
Password: ******************
Successfully logged into [https://api.stackato-pry5.local]
Reset current group: OK

Group and Apps

PS C:\stackato> stackato group f5qa
Successfully set current group to [f5qa]
PS C:\stackato> stackato apps

Stackato Logs

To view and application log stream, use the stackato logs command:

PS C:\stackato> stackato logs [myapp]
PS C:\stackato> stackato logs f5qarc4-console

To limit the number of lines displayed, use the — num option:

PS C:\stackato> stackato logs [myapp] — num 50
To view log stream as it is updated, use the — follow option:

PS C:\stackato> stackato logs [myapp] — follow

Note stackato logs buffers only 400 lines of the log stream history (i.e. lines generated prior to it being run). If you need earlier log lines, use the stackato files command to fetch the relevant log file from the logs/ directory

You can save whatever you get to your local machine by using redirection operators:

PS C:\stackato> stackato logs f5qarc4-relay — num 50 >> relaylog01.txt
PS C:\stackato> stackato logs f5qarc4-relay — follow

Stackato Files

PS C:\stackato> stackato files [appname] [path] [–all]

PS C:\stackato> stackato files f5qarc4-relay /logs
staging.log 16B
stderr.log 1.4K
stdout.log 67.9M
stdout.log.1.gz 6.8M
stdout.log.2.gz 6.9M
stdout.log.3.gz 7.0M
stdout.log.4.gz 7.0M
stdout.log.5.gz 8.4M
stdout.log.6.gz 7.3M
stdout.log.7.gz 7.4M
stdout.log.8.gz 7.4M
stdout.log.9.gz 8.8M
PS C:\stackato> stackato files f5qarc4-relay /logs/stderr.log >> relay-stderr.log
PS C:\stackato>