Excel Data Connections

[foozball3000]

I'm trying to Import spesific data from a Text file into an excel spreadsheet via VBA. The code is copied from another Excel document that does just that... but somehow it's not working at all.

The original:

Sub RunTest()
        crtPivotTable _
                ptTable:="DataTable", _
                SqlStmnt:="SELECT * FROM TestData.txt WHERE (Test1='Yes')", _
                PgFields:=Array("Val_ind", "flag", "Grouping", "ClientName")

        FormatPivotTable _
                ptTable:="DataTable"
End Sub

Sub crtPivotTable(ptTable As String, SqlStmnt As String, PgFields, ClFields, RwFields, Descr As String)

...
    '   Import data selection from text file and create PivotTable
    With ActiveWorkbook.PivotCaches.Add(xlExternal)
        .Connection = "ODBC;DBQ=Data;DefaultDir=;Driver={Microsoft Text Driver (*.txt; *.csv)};"
        .CommandType = xlCmdSql
        .CommandText = SqlStmnt
        .CreatePivotTable "Report!R10C3", ptTable
    End With
    
...        
End Sub

The New Code

Sub ImportDocuments(SheetNumber As Integer, DocNumber As String)
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(xlExternal)
        .Connection = "ODBC;DBQ=Data;DefaultDir=;Driver={Microsoft Text Driver (*.txt; *.csv)};"
        .CommandType = xlCmdSql
        .CommandText = "SELECT * FROM Documents.txt WHERE Document = 'New'"
        .Destination = ActiveSheet.Range("A1")
    End With
End Sub

 

[SirFooK'nG]

just record a macro - do it manually once.

 

[Ou grote]

just record a macro - do it manually once.

Yup

 

[Shred]

VBA? Yuck!!!

 

[Jabberwocky]

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\Documents and Settings\Administrator\My Documents\textfilename.txt", _
Destination:=Range("$A$1"))
.Name = "textfile or whatever"
.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 = 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

 

[foozball3000]

With ActiveSheet.QueryTables.Add...

Just one problem with that... The SQL "Where" Clause.
The dataset this runs off is potencially bigger than what Excel can support.
So, it must only import a portion. Like the 1st set of code does.

 

[Jabberwocky]

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=test;DefaultDir=E:\DOCUMENTS;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT.......")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_test3"
.Refresh BackgroundQuery:=False
End With

Easiest is to record a macro, and then to just edit the code if you want.
Remember to make a data source first via administrative tools

 

[SirFooK'nG]

MS Access then ... 1000% better option.

Import txt file into a table, query for result, publish/export as needed.