locked
Workbook connections AddFromFile2 CSV data without user interaction RRS feed

  • 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


    Wednesday, October 7, 2015 1:11 PM

Answers

  • 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.


    Thursday, October 8, 2015 3:06 AM

All replies

  • 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.


    Thursday, October 8, 2015 3:06 AM
  • 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

    Friday, March 11, 2016 7:33 PM