Excel VBA not retrieving all data from SQL Server.

nbradleywilkerson

New Member
Joined
Apr 5, 2018
Messages
1
Looking for help with why the vba code that is currently running to retrieve data from SQL server is not displaying all of the data that is being retrieved.

I have 28 programs in the columns which I am retrieving data for 15 categories in the rows. The data has been retrieving fine for the last 3 years without any issues but now the last column of data is not populating in the Excel workbook. I have confirmed the stored procedure is working properly in SSMS and the data is being retrieved.

Here is the code I am using to retrieve the data:
Function getDataDetail()
Dim evalConn As ADODB.Connection
Dim evalData As ADODB.RecordSet
Dim evalDataField As ADODB.Field

Set evalConn = New ADODB.Connection
Set evalData = New ADODB.RecordSet

evalConn.ConnectionString = conStrSQL
evalConn.Open

On Error GoTo CloseConnection

'Application.Workbooks("WorkBook.xlsm").Activate
'Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Responses by Program"
'Range("B" & Rows.Count).End(xlUp).Offset(3).Select

With evalData
.ActiveConnection = evalConn
.Source = GetSQLGetDataDetail
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

On Error GoTo CloseRecordset


' For Each evalDataField In evalData.Fields
' ActiveCell.Value = evalDataField.Name
' ActiveCell.Offset(0, 1).Select
' Next evalDataField

Sheets("Responses by Program").Select
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.CopyFromRecordset evalData
'ThisWorkbook.Worksheets("Responses by Program").Cells.EntireColumn.AutoFit

On Error GoTo 0

CloseRecordset:
evalData.Close

CloseConnection:
evalConn.Close

End Function



Function GetSQLDataDetail() As String
Dim courseID As String
Dim classEndDate As Date
Dim fiscalYear As String
Dim sqlString As String
Dim SQL As String




SQL = Sheet1.Range("BC3").Text


sqlString = SQL

GetSQLDataDetail = sqlString

End Function

Any help with understanding why the data is not being retrieved into Excel would be greatly appreciated.
 

Forum statistics

Threads
1,081,705
Messages
5,360,759
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top