I have a vba macro written in an excel worksheet (WS1) that reads another excel file (WS2) and retrieves data and writes it into the worksheet with the macro (WS1). It works beauifully except for one little problem. When I send it to a co-worker the numbers part of the data returns null values. I am connecting to (WS2) using ADO connection strings for the spreadsheet and recordset. The only difference I see is that in references of WS1, it shows Microsoft ActiveX Data Objects 2.7 library, it shows 2.8 library on my machine. Also it shows recordset 2.8 library on my coworkers machine, as well as mine. Any ideas would be GREATLY appreciated.
Null Values From Retrieved Spreadsheet Data
-
-
-
Re: Null Values From Retrieved Spreadsheet Data
I suppose it could be related to the different versions of Excel, as it appears (?) that you suspect.
Have you tried stepping through (hitting F8 repeatedly while in the module) the code under your co-worker's log-in on his computer to see what is happening to the values as they transfer? That would be where I'd start.
If your co-worker doesn't have access to all the directories that you do, it might also cause the problems retrieving values. Have him try opening the workbooks that the sheet references. If that's the problem, you could simply copy the sheets that are referenced to a directory that he can access.
It's really hard to guess here with what little you've provided. What's the code you're using that causes the values to return?
-
Re: Null Values From Retrieved Spreadsheet Data
I have stepped through the code on his laptop. I researched it some more and it appears to be a difference in data types. According to the MSDN website, it will evaluate a row and determine data types. If it comes across data within the column that is, say, a number of 1.435, but it had determined that to be a text column, it will return a null value. It is only the columns with numbers in it and when I look at the top of the spreadsheet, there are cells with text in them (headers & what not). They suggested using the 'IMEX=1' in my connection string but when I did that, it could not load the ISAM reference. Again, I don't know why it works on mine? I appreciate the feedback and if you have any other ideas, please let me know.
-
Re: Null Values From Retrieved Spreadsheet Data
I'm sorry, I was asked to provide the code I am using and I omitted that piece of info. The code I am using is as follows:
Code
Display MoreFor iCntEm = 0 To 24 For iClearEm = 0 To 4 iTrav(iClearEm) = 0 iLum(iClearEm) = 0 Next sSql1 = ThisWorkbook.Sheets("Sheet2").Cells(iCntEm + 200, 2).Value sSql = "Select * From [Prescriber Trends$] Where F5 = '" & sSql1 & _ "' AND (F9 = 'TRAV' OR F9 = 'TRAVZ' OR F9 = 'TTL LUM') And F10 = 'Total' And F11 = 'NRx';" Set rsTop = New ADODB.Recordset rsTop.Open sSql, cn, adOpenStatic Do While Not rsTop.EOF If Left(rsTop("F9"), 4) = "TRAV" Then If Not IsNull(rsTop("F14")) Then iTrav(0) = iTrav(0) + rsTop("F14") If Not IsNull(rsTop("F15")) Then iTrav(1) = iTrav(1) + rsTop("F15") If Not IsNull(rsTop("F16")) Then iTrav(2) = iTrav(2) + rsTop("F16") If Not IsNull(rsTop("F17")) Then iTrav(3) = iTrav(3) + rsTop("F17") If Not IsNull(rsTop("F18")) Then iTrav(4) = iTrav(4) + rsTop("F18") Else If Not IsNull(rsTop("F14")) Then iLum(0) = iLum(0) + rsTop("F14") If Not IsNull(rsTop("F15")) Then iLum(1) = iLum(1) + rsTop("F15") If Not IsNull(rsTop("F16")) Then iLum(2) = iLum(2) + rsTop("F16") If Not IsNull(rsTop("F17")) Then iLum(3) = iLum(3) + rsTop("F17") If Not IsNull(rsTop("F18")) Then iLum(4) = iLum(4) + rsTop("F18") End If rsTop.MoveNext Loop For iFCnt = 0 To 4 ThisWorkbook.Sheets("Top25Xal").Cells(iCnt, iFCnt + 4).Value = iLum(iFCnt) ThisWorkbook.Sheets("Top25Xal").Cells(iCnt + 1, iFCnt + 4).Value = iTrav(iFCnt) Next iCnt = iCnt + 3 iFCnt = 0 rsTop.Close Next
Where sSql1 is a value in a list I am using to look up the correspoding record. Any thoughts??
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!