QueryTables

[jstevens]

Is there something equivalent to a TEXT file QueryTable that can be used on an EXCEL file?

Sample QueryTable Code:

Code: Select all

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & oFilePath _
        , Destination:=Range("A2"))
        .Name = "extract_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Thanks,
John

[rory]

Yes - you would use ODBC and the Excel driver. If you record a macro, it will give you the basic code.

[jstevens]

Thanks for the suggestion.

I have a challenge with the following code:

Code: Select all

Sub Connect_To_Excel()
'Select MS ActiveX Data Objects XX Library

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=D:\Test.xls;" & _
          "Extended Properties=Excel 8.0;"
    .Open
End With

strQuery = "SELECT * FROM [Sheet1$]"       'CHALLENGE is on this line

cn.Close

End Sub

Where data is not being returned on the "strQuery" line of code.

From what I can tell, this should work.

The "Test" file contains a header row (A1:B1) and data in Range("A2:B5").


Your thoughts are appreciated,
John

[jstevens]

I found a good example of what I'm trying to achieve.

Regards,
John

[rory]

You will note that in the example you linked to, a recordset is opened based on the query. You are not doing that, hence you get no data!

[jstevens]

Rory,

The example uses and I'm not sure what Wscript.Echo is doing. If I put a Msgbox objRecordset.Fields.Item("Name") & objRecordset.Fields.Item("Number") line of code in, I will see the values.

Wscript.Echo objRecordset.Fields.Item("Name"), _
objRecordset.Fields.Item("Number")
objRecordset.MoveNext

What path would you recommend me to follow? And no, I'm not going to Jezza's house in the woods.

Thanks,
John

[HansV]

The article you linked to is about VBScript; this is similar to but not the same as VBA. The Echo method either displays a message box or outputs text to a command prompt window.

What exactly do you want to accomplish?

[jstevens]

I have a number of excel files that I would like read without opening them. Similar to using an ado querytable on a text file. I'll be compiling all the data in one sheet. Each Excel file has the same format.

Regards,
John

[HansV]

Open a recordset as described in the article, then use the CopyFromRecordset method of the Range object to insert the data from the recordset into a worksheet. For example:

Dim oCell As Range
' Set oCell to the first blank cell below the used range in column A
Set oCell = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
' Copy the recordset, with oCell as top left corner
oCell.CopyFromRecordset objRecordset

See CopyRecordset Method.