jo15765
11-11-2012, 06:48 PM
I am running this code in Excel:
lStr_ComLine = ""
lStr_ComLine = lStr_ComLine & "ODBC;"
lStr_ComLine = lStr_ComLine & "DSN='Enter DSN Name HEre;"
lStr_ComLine = lStr_ComLine & "UID=sa;"
lStr_ComLine = lStr_ComLine & "APP=Microsoft Office XP;"
lStr_ComLine = lStr_ComLine & "DATABASE=TestDB;"
lStr_ComLine = lStr_ComLine & "Trusted_Connection=Yes"
'Problem is I actually want the data to go into D7, but if I put D7 in the 'Range the data goes into D8
'So the data is going one row lower than what I want. How can I update 'this?
With ActiveSheet.QueryTables.Add(Connection:=lStr_ComLine, Destination:=Range("D6"))
'Target Query
.CommandText = 'SQL Statement Here
Debug.Print .CommandText
.Name = 'Name of Query
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
.Refresh BackgroundQuery:=False
End With
As my comment above states Problem is I actually want the data to go into D7, but if I put D7 in the Range the data goes into D8. So the data is going one row lower than what I want. How can I update this?
Can someone help me out with what the issue is?! Do I just need to use the Offset statement in my Range statement to have it offset?
lStr_ComLine = ""
lStr_ComLine = lStr_ComLine & "ODBC;"
lStr_ComLine = lStr_ComLine & "DSN='Enter DSN Name HEre;"
lStr_ComLine = lStr_ComLine & "UID=sa;"
lStr_ComLine = lStr_ComLine & "APP=Microsoft Office XP;"
lStr_ComLine = lStr_ComLine & "DATABASE=TestDB;"
lStr_ComLine = lStr_ComLine & "Trusted_Connection=Yes"
'Problem is I actually want the data to go into D7, but if I put D7 in the 'Range the data goes into D8
'So the data is going one row lower than what I want. How can I update 'this?
With ActiveSheet.QueryTables.Add(Connection:=lStr_ComLine, Destination:=Range("D6"))
'Target Query
.CommandText = 'SQL Statement Here
Debug.Print .CommandText
.Name = 'Name of Query
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
.Refresh BackgroundQuery:=False
End With
As my comment above states Problem is I actually want the data to go into D7, but if I put D7 in the Range the data goes into D8. So the data is going one row lower than what I want. How can I update this?
Can someone help me out with what the issue is?! Do I just need to use the Offset statement in my Range statement to have it offset?