OK everyone I need some help. I tried everything I can think of and am out of ideas.
I was able to import a text file to a new worksheet using Excel activeX but what I need to do is import data ( the same text file ) into a template that is
already open and then to a specific worksheet in that template. Shown below is the macro that does what I need. I just cant seem to figure out how to get the activeX command to work.
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\K5533 calibration data.txt" _
, Destination:=Range("A1"))
.Name = "K5533 calibration data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
In my program I have
ExcelObj_QueryTable retValue;
VARIANT varConnection;
ExcelObj_QueryTable retValue;
ExcelObj_Range destination=11; // here I dont really know what to set it to since this wont except "A1"
CA_VariantSetCString (&varConnection,"I:\\K5533 calibration data.txt");
error = Excel_QueryTablesAdd (ExcelWorksheetHandle, NULL, varConnection,
destination, CA_DEFAULT_VAL, &retValue);
I have 3 unknowns
1) Is it correct to use ExcelWorksheetHandle or do I need ExcelAppHandle ?
2) Do I need to add Connection:=
