none
Shift Excel Cell after executing a macro for second time RRS feed

  • Question

  • 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

    Tuesday, January 6, 2015 2:36 PM