Excel VBA import data from SQL server. Data imported to excel shows slight difference in values.
Having this issue with importing data from SQL server express to Excel 2010 (win7). Using excel VBA to execute the SQL. No problem getting the data into excel, but for some reason it seems to have ever so slight differences from the orginal data as contained in the database. I tried different SQL comands including without any calculation (eg select top 100), so problem isn't here. The database data is stored as real(4). The imported data once in excel arrives as text values (i don't know why) with slight errors introduced at around the eighth and ninth decimal place. For example, if the orginal data reads 0.7400, the same number imported to excel reads as 0.74000018 - a slight but annoying difference. My connection parameter statement follows. Thanks. With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array ( "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBname & ";Data Source=SAX\SQLEXPRESS;Use ", "Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SAX;Use Encryption for Data=False;Tag with column", " collation when possible=False"), Destination:=Range(PositionWriteData)).QueryTable .CommandType = xlCmdSql .CommandText = StringToArray(Ssql) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceConnectionFile = "C:\Users\Guy\Documents\My Data Sources\SAX_SQLEXPRESS " & DBname & " " & DBtable & ".odc" .ListObject.DisplayName = "SQLdataTable" .Refresh BackgroundQuery:=False End With