Workbook connections AddFromFile2 CSV data without user interaction

Question

Hi all,

I am trying to achieve this functionality programmatically through VSTO addin (I am using C#).

On click of button, in the current active workbook, I should create a connection to a CSV file. In this connection I should only create the connection and add the data to data model. Manually If I have to do this I will follow these steps:

a) Open Excel

b) Go to data tab

c) Click on "From Text"

d) In the file dialog select the CSV file. Wizard opens.

e) Go through the wizard (Selecting delimited & My data has headers, comma as delimiter and general formatting in the wizard pages respectively)

I ran a macro and got this code in VBA:

Sub Macro1()
'
' Macro1 Macro
'

'
    Workbooks("Book1").Connections.AddFromFile _
        "D:\Test.csv" _
        , True, False
End Sub

When I try the same in C# (below), it pops up the wizard. I want to add this connection silently without pop up to user.

Workbook workbook = null;
            Application application =  Globals.ThisAddIn.Application;
            try
            {
                application = AppLoader.CurrentApplication;

                workbook = application.Workbooks.Add();


                WorkbookConnection wkbConn = workbook.Connections.AddFromFile2(@"D:\Test.csv", true, false);
}
catch(Exception de)
{
	throw de;
}

Is this possible ? I have tried everything but not able to achieve this. I also tried Connection.Add2 but that threw exception.

Also I want to be able to change the path of CSV later (May be D:\Test1.csv) through C# when I want to refresh the model data. Is that also possible ?

- Girija

---

Please mark responses as answered if it helped you.. This helps others... - Girija Shankar Beuria

Answer 1

Hi Girija,

>> When I try the same in C# (below), it pops up the wizard. I want to add this connection silently without pop up to user.

Based on your code, I think you need to add “QueryTables.Add”. I suggest you manually operate your requirement, and record these steps with Record Macro. Here is a simple code:

Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\OfficeDev\Excel\Book2.csv", Destination:=Range("$E$15"))
        .Name = "Book3"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

You could record your own steps, and convert it to C# code.

>> I want to be able to change the path of CSV later (May be D:\Test1.csv) through C# when I want to refresh the model data. Is that also possible ?
Yes, it is possible, and you could record the modify steps again. A simple vba code like below:

Sub Macro4()
'
' Macro4 Macro
'

'
    With ActiveWorkbook.Connections("Book21")
        .Name = "Book21"
        .Description = ""
    End With
    Range("E15:E16").Select
    With Selection.QueryTable
        .Connection = "TEXT;D:\OfficeDev\Excel\T2.csv"
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("F10").Select
End Sub

Note, when you record the steps, it will generate ".CommandType = 0", and it might cause error, you could remove it if you only add text file.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi Girija,

Create a connection without text import wizard in C#

path -- path of the text file

Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);  
QueryTable q = activeWorksheet.QueryTables.Add("TEXT;" + path, (Excel.Range)this.Application.ActiveCell);

this will create a connection and load the data in the cell which is selected.

Hope this helps.

Thanks,

Mahesh