Shift Excel Cell after executing a macro for second time

[LearningMacro]

Hello All,

I've written a (below)macro that pulls data from the sql server 2008 r2. My issue is when the user runs the macro for first time by entering Jobnumber (say J0001) excel puts data on the spreadsheet starting from cell "A1" which is fine. The issue here is, when the user runs the macro for the second time by entering the jobnumber (say J0002), excel puts the data for Jobnumber (J0002) on cell "A1" and shifts the cells for J0001(first job) to cell "F" instead of moving down. How can I shift the previous entry down in the spreadsheet with the latest entry on top?

Here is my macro and attachment:

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

 

[bobhc]

When this code is run it throws up syntax and compile errors, guess it is the do with the link to the raw data....just a shot in the dark but is there anything in the raw data on the server that is stipulating that A1 is used for all new entry's?.

Not much help I know but VBA is, I strongly believe, black art controlled by those who live on the dark side of the moon..........Where I used to work we used VBA a lot, but all it stood for there was...Very Busy Attitude.

 

[NARAYANK991]

Hi ,

A doubt : how is the macro to understand that it is being run a second or third time ? Is it only through the Job Number ? If so , will the Job Number the first time always be J0001 ?

If all this is so , then you can test for whether the Job Number is J0001 ; if yes , then the destcell can be set to Range("A1") otherwise , it can be set to the lastrow + 1 in column A , using the following line of code :

Set destcell = Cells(Cells(Rows.Count , "A").End(xlUp).Row + 1 , "A")

Then in your statement use destcell for the Destination ; the usage which you have at present viz. Range("a1", "a1000") is not necessary ; you can have destcell , where if the Job Number is J0001 , you can set destcell to Range("A1") ; only the top left hand corner of the range needs to be specified.

Thus ,
If Job Number = "J0001" Then
Set destcell = Range("A1")
Else
Set destcell = Cells(Cells(Rows.Count , "A").End(xlUp).Row + 1 , "A")
Endif

Set thisQT = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=destcell

should work.

Narayan