Answered by:
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
- Edited by Girija Beuria Wednesday, October 7, 2015 1:11 PM
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.- Proposed as answer by Edward8520Microsoft contingent staff Monday, October 19, 2015 8:45 AM
- Marked as answer by Edward8520Microsoft contingent staff Tuesday, October 20, 2015 3:29 AM
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.- Proposed as answer by Edward8520Microsoft contingent staff Monday, October 19, 2015 8:45 AM
- Marked as answer by Edward8520Microsoft contingent staff Tuesday, October 20, 2015 3:29 AM
Thursday, October 8, 2015 3:06 AM -
Hi Girija,
Create a connection without text import wizard in C#
path -- path of the text file
this will create a connection and load the data in the cell which is selected.Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
QueryTable q = activeWorksheet.QueryTables.Add("TEXT;" + path, (Excel.Range)this.Application.ActiveCell);
Hope this helps.
Thanks,
Mahesh
Friday, March 11, 2016 7:33 PM