excel query table

[agusado]

Hi everyone

I need help with Excel activeX

I have an Excel Template file that I need to import a text file to a specific sheet.

The Macro shown below works

With ActiveSheet.QueryTables.Add(Connection:= _

      "TEXT;I:\533 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

This is done from the Excel Data Menu / Get External Data / Import Text File

I want to do the same thing using activeX

Here is what I have

ExcelObj_QueryTable retValue;   

VARIANT varConnection;      

ExcelObj_Range destination;

CA_VariantSetCString (&varConnection,"I:\\K5533 calibration data.txt");  

error = Excel_QueryTablesAdd (ExcelWorksheetHandle, NULL, varConnection,
          destination, CA_DEFAULT_VAL, &retValue);

This is not working!!!!!

1) I dont know what to set destination to since it wont accept anything like "A1"

2) Do I need to add   Connection:=  to my destination definition

3) Is Handle to worksheet appropriate or to I need handle to workbook or application.

Sorry for long question but I am trying to provide as much info as possible.

Sure hope one of you can provide some suggestions

Thanks in advance

Mario

[sarahk]

Hi, Mario.

I notice that your macro begins with the line ActiveSheet.QueryTables.Add, which indicates that the handle needed in Excel_QueryTablesAdd () is the worksheet handle. (You can access the active worksheet handle with Excel_ActiveWorksheet().)

Determining the destination argument in Excel_QueryTablesAdd() is a bit more complicated, however. The specifics of that argument are provided by Microsoft, and they can be found as follows in MS Excel 2003:

1. Run Excel
2. Select Tools>>Macro>>Visual Basic Editor
3. Once the editor opens, select Help>>Microsoft Visual Basic Help
4. When the table of contents appears on the right side of the screen, select Microsoft Excel Visual Basic Reference>>Methods>>A>>Add Method
5. Select Add method as it applies to the QueryTables object.

For further help with Microsoft's functions, I recommend checking out www.msdn.com.

Regarding your question about "Connection:=", I'm a little unclear about where you're considering adding that line. Can you elaborate on that a little more?

Good luck.

[sarahk]

Another answer to this was posted in another thread.