Hi,
I've inherited an existing excel sheet and am "just" adding 2 extra fields
into the SELECT which is grabbing info from a Timberline database and
throwing it in an excel sheet.
The working code is this (existing code is working fine):
Set NewSheet = Worksheets.Add
NewSheet.Name = "Tax"
Set TaxSht = Worksheets("Tax")
Worksheets("Tax").Activate
'SendKeys LoginVar & "{TAB}" & PasswordVar & "{ENTER}", False
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Timberline Data
Source;UID=TAKENOUT;PWD=TAKENOUT;DBQ=T:\TAKENOUT;CODEPAGE=1252;MaxColSupport=1536;DictionaryMode" _
),
Array("=0;StandardMode=0;MaxColSupport=1500;ShortenNames=0;DatabaseType=1;")), _
Destination:=Worksheets("Tax").Range("A1"))
.CommandText = Array( _
"SELECT CURRENT_ART_TRANSACTION.Job, CURRENT_ART_TRANSACTION.Amount"
& Chr(13) & "" & Chr(10) & _
"FROM CURRENT_ART_TRANSACTION CURRENT_ART_TRANSACTION" & Chr(13) &
"" & Chr(10) & _
"WHERE CURRENT_ART_TRANSACTION.Job='" &
Worksheets("WIP").Cells(WIPRow, 2) & "'" & Chr(13) & "" & Chr(10) & _
"ORDER BY CURRENT_ART_TRANSACTION.Job")
.Name = "qry_Tax"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
----------------------------------------
Now when I add 2 more fields to the SELECT statement it gives me a Data
Mismatch error and I have no idea why. The 2 extra fields have text in them.
New Line:
"SELECT CURRENT_ART_TRANSACTION.Job, CURRENT_ART_TRANSACTION.Amount,
CURRENT_ART_TRANSACTION.Transaction_Type,
CURRENT_ART_TRANSACTION.Amount_Type" & Chr(13) & "" & Chr(10) & _
I am certainly not an Excel VBA expert and just dont know why 2 extra fields
in a brand new worksheet would cause this error.
Anyone have any thoughts?
|
Similar Threads
