Importing Stored Procedure execution in excel - New Columns not showing up

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  March 29, 2017 at 12:46 pm

  #149398

  We have a stored procedure which we use to import the data in Excel using following vba
    'With ActiveSheet.QueryTables.Add(Connection:=Array(ConString1), Destination:=Range("A1"))
          .CommandType = xlCmdSql
          .CommandText = IpSQLCommand
          .FieldNames = True
          .RowNumbers = False

          .Refresh BackgroundQuery:=False
      'End With
  This has been working fine.
  Recently we added few more fields to the output of Stored Procedure.
  In the SQL studio all the fields shows up but in excel all the new fields are missing.

• Jeff Moden

  SSC Guru

  Points: 1004589

  More actions

  April 2, 2017 at 7:00 pm

  #1936195

  My recommendation would be to use an "External Data Source" in the spreadsheet rather than trying to write your own in VBA.

  Other than that, you may have to clear local cache on the box for your spreadsheet to work correctly as it is.  I have no idea how to do that, though.  I just overhear folks in the group of people at work that do know such things and it seems to work miracles for misbehaving spreadsheets and other applications.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)