Shift Excel Cell after executing a macro

[MacroUser]

Hello All,

I've written a (below)macro that pulls data from the sql server 2008 r2. My issue is when the user enters the Jobnumber for the first time excel puts data on the spreadsheet starting from "A1" cell. The issue here is, when the user runs the macro for the second time all the data is again put on spreadsheet starting from "A1" cell. How can I shift the previous entry down in the spreadsheet with the latest entry on top?

Here is my macro:


Sub Task()

Dim sqlstring As String


Dim connstring As String


Dim Strcode As String


Strcode = Trim(InputBox("Please enter a Job #", "Task history"))


sqlstring = "select distinct m.JobNumber , cast(m.ExpectedDate as DATE) 'Ship Date' ,m.CustLongName 'Customer' & _
" from ArchiveJobHeader m left join AuxiliaryInfoFile af (nolock) on af.jobnumber=m.jobnumber & _
" where m.JobNumber = '" & Trim(Strcode) & "'" & _
" order by 'Resulttime'"
connstring = "ODBC;DSN=SQLDSN;UID=test;PWD=test123"


Dim thisQT As QueryTable


Set thisQT = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("a1", "a1000"))


thisQT.BackgroundQuery = False


thisQT.Sql = sqlstring




thisQT.Refresh


End Sub

[Bob Phillips]

Sub Task()
Dim thisQT As QueryTable
Dim sqlstring As String
Dim connstring As String
Dim Strcode As String

Strcode = Trim(InputBox("Please enter a Job #", "Task history"))

sqlstring = "select distinct m.JobNumber , cast(m.ExpectedDate as DATE) 'Ship Date' ,m.CustLongName 'Customer'" & _
" from ArchiveJobHeader m left join AuxiliaryInfoFile af (nolock) on af.jobnumber=m.jobnumber" & _
" where m.JobNumber = '" & Trim(Strcode) & "'" & _
" order by 'Resulttime'"
connstring = "ODBC;DSN=SQLDSN;UID=test;PWD=test123"

Rows(1).Insert
Set thisQT = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("a1", "a1000"))

thisQT.BackgroundQuery = False
thisQT.Sql = sqlstring
thisQT.Refresh
End Sub

[MacroUser]

Hi xld,

Thank you for your response. I tried the above code you suggested. When I run the macro second time (after cell "A1" has data for the first Job # say J0001) for Job # J0002, the entries for first Job # (J0001) are shifted to right side of the spreadsheet starting from column "J". I want the first job entry to move down instead of moving on right.

Thanks

[Bob Phillips]

It's working fine for me.

[MacroUser]

Hi xld,

I've attached the screenshot of the spreadsheet. Please take a look at it.


12683

[Bob Phillips]

a) I can't see looking at a picture will help much

b) I get an invalid link with that file.

[MacroUser]

Hi xld,

Still stuck with this issue. Can you please help?

Thanks