excel query table

[agusado]

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:= 

[D S]

I dont know if this will help you but this is what I do to import large numbers of text files into excel and then have excel process them.

There are 2 parts to it:

1) using the windows database driver for your text file

2) modifying the active x example from CVI to process files.

Setting up the text database driver

What you do is:

Copy your desired text file to a fixed file name in the directory where it will appear (like "infile.csv")

If you have a text file you can use windows ODBC services to load it for you to excel.

go to

Control Panel -> Administrative Tools -> Data Sources (ODBC)

click on System DSN tab

click Add..

select Microsoft Text Driver (*.txt, *.csv)

click Finish

A new panel appears

type in a data source name

unclick use current directory

click select directory

select the directory where your data will be (this is fixed) hit OK

click on options

click remove after highlighting the extention that your file is NOT

click on define format

now you should see your "datain.csv" file

click on it, click on column name header if you have it otherwise you will need to do it manually

if you have column names then click guess

now it might give an error!  this is a windows bug.  in the directory you have selected the file "schema.ini" should have appeared.

if you have text columns this wizard forgets to put some stuff in the ini file, you have to fix it like the example below.

"bad file"

[sn_0268540006.csv]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=SN_SUFFIX Integer
Col2="CASE NUMBER" Integer
Col3=TEST
Col4=COEFFICIENT_TYPE Integer
Col5=ERROR_CODE Integer
Col6=FILE

"fixed file"

[sn_0268540006.csv]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=SN_SUFFIX Integer
Col2="CASE NUMBER" Integer
Col3=TEST Char Width 255
Col4=COEFFICIENT_TYPE Integer
Col5=ERROR_CODE Integer
Col6=FILE Char Width 255

now you can import your text file easily into excel by clicking

data->import external data->new database query->(your query name that you chose)

you can pick and columns you want (if you get the message that a column is invalid you still have the above char width problem)

it will ask you where you want to put the data.  it is best to not accept the default but to first click on the sheet where you want the data and then the cell.  this way the query will always write to that sheet.

now you can make up graphs and stuff with your data.

it is easy to make a macro to update the data

this will update the query on sheet "database" at cell A1.

    Sheets("database").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Copy of summary").Select
    Range("A1").Select

so now what can you do with this?  you can easily edit the active x application that CVI comes with as an example to load your excel template, run a macro to update the data from the text file, and then print to pdf say.  What I do is have a bunch of text files, i use find first file() and next file() to go through them, i rename them to my infile.csv, load excel and print out the pdf then rename the pdf that was generated and so on until i am out of text files.

This will print to a PDF 995 printer driver:

  Application.ActivePrinter = "PDF995 on Ne00:"
    Sheets("Copy of summary").Select
    Range("A1").Select
    Sheets(Array("Sheet1", "Sheet10", "Sheet11")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Copy of summary").Select
    Range("A1").Select