Cell Returns Value of 0- Please Help!

[bemp87]

HI Everyone,

I am needing a fix for a cell returning a value of '0' when pulling data from a closed workbook. I have two workbooks, one houses all my data that practically remains closed, the other is the active workbook used by my team which pulls data from the closed workbook:

I have the active workbook pulling data from the closed workbook via the following code:

Option Explicit

Sub GetData_SystemCodes()
GetData ThisWorkbook.Path & "\Test_Test_v1.xlsm", "Codes", _
"A2:E163", Sheets("Codes").Range("A3"), True, True
End Sub

The problem I am having is when running this macro and pulling the data from the closed workbook to the active workbook, for example if cells E140:E150 are blank on the closed wookbook, they are returning a value of ;0' on the active workbook.

I have tried Conditional Formatting, I have tried formatting the cells with '0;-0;;@' and still nothing works. Also on the cells that are returning with a 0 value, I get an error of "The number in this cell is formated as text or preceded by an apostrophe".

I am not sure what exactly is causing this error or how to return a blank cell when the cell on the closed workbook is blank. If it helps i am using the following code on the closed workbook in the cell the data is being pulled from:

Code:
=IF(ISERROR(VLOOKUP(A2,SystemCodes!$A$2:$B$164,2,0)),"",VLOOKUP(A2,SystemCodes!$A$2:$B$164,2,0))

Thanks in advance!

 

[sergioMabres]

Hi Bemp87
I guess the problem is not in the code that you posted but in the code of the function/sub GetData that you are using in your sub
The code of GetData is the code you should be posting to solve the problem
Cheers
Sergio

 

[bemp87]

Hi Sergio, Thanks for the assistance! The code for the 'GetData' function is posted below, it's a bit lengthy, but any assistance you can provide is much appreciated.

Option Explicit
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long
' Create the connection string.
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If
If SourceSheet = "" Then
' workbook level name
szSQL = "SELECT * FROM " & SourceRange$ & ";"
Else
' worksheet level name or range
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
End If
On Error GoTo SomethingWrong
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1
' Check to make sure we received data and copy the data
If Not rsData.EOF Then
If Header = False Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
'Add the header cell in each column if the last argument is True
If UseHeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1
TargetRange.Cells(1, 1 + lCount).Value = _
rsData.Fields(lCount).Name
Next lCount
TargetRange.Cells(2, 1).CopyFromRecordset rsData
Else
TargetRange.Cells(1, 1).CopyFromRecordset rsData
End If
End If
Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If
' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub
SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
vbExclamation, "Error"
On Error GoTo 0
End Sub
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function Array_Sort(ArrayList As Variant) As Variant
Dim aCnt As Integer, bCnt As Integer
Dim tempStr As String
For aCnt = LBound(ArrayList) To UBound(ArrayList) - 1
For bCnt = aCnt + 1 To UBound(ArrayList)
If ArrayList(aCnt) > ArrayList(bCnt) Then
tempStr = ArrayList(bCnt)
ArrayList(bCnt) = ArrayList(aCnt)
ArrayList(aCnt) = tempStr
End If
Next bCnt
Next aCnt
Array_Sort = ArrayList
End Function