Get Deta From Closed Workbook Sheet

[Priyantha]

Dear All,

I am tring to get deta to a array from closed work book sheet. But not working correctly.

strSQL = "SELECT * FROM [" & strSheetName & "$" & strRange & "] WHERE [Date] >= #" & startDate & "# AND [Date] <= #" & endDate & "# AND [Employee_Number] = '" & employeeNumber & "'"

rs.Open strSQL, conn, 1, 3 _ This line seems a issue (Run time error - 2147217913, "Deta type mismatch in criteria Expression")

My detabase file & active file attached here with'

Thanks,

Priyantha

[HansV]

Employee_Number is a number value, not a text, so you should not enclose it in single quotes:

strSQL = "SELECT * FROM [" & strSheetName & "$" & strRange & "] WHERE [Date] >= #" & startDate & "# AND [Date] <= #" & endDate & "# AND [Employee_Number] = " & employeeNumber

[Priyantha]

Employee_Number is a number value, not a text, so you should not enclose it in single quotes:

strSQL = "SELECT * FROM [" & strSheetName & "$" & strRange & "] WHERE [Date] >= #" & startDate & "# AND [Date] <= #" & endDate & "# AND [Employee_Number] = " & employeeNumber

That code working, Thank Hans,


Br,

Priyantha

[DocAElstein]

Hello
Here is an alternative, just for passing interest’s sake. It uses simple VBA / non ADODB techniques.
It uses closed workbook reference technique to bring the entire data from the closed workbook
( Employees Deta.xlsx )
into a temporary worksheet,
( Temporary EMPDeta )
, in the main opened file,
( Out Source - Increment & Arrears Format 2.xlsm )
Then it does simple VBA stuff to pick out the sub range array wanted.

It could probably be made a bit more efficient and it might need modification based on what actual real data you might have.
It is just intended as academic interest to compare ways to do it.

I modified the sub range selection criteria data (dates and employee number) in the main workbook so as to actually test if the macros work, (since the original data did not test if any macro worked as the dates and employee number weren’t in the closed workbook data file)

_.____

To test you put both workbooks in the same place, then open just
Out Source - Increment & Arrears Format 2.xlsm
, then run either my macro,
Function GetSelectedEmployeeDataFromClosedWorkbook2()
, or the original with Hans correction,
Function GetSelectedEmployeeDataFromClosedWorkbook()

They both seem to give similar results in the final array, arrData(). The actual format/ orientation etc. is a bit different, but they basically get the same sub range data

Alan

Code: Select all

 '  https://eileenslounge.com/viewtopic.php?f=30&t=41187
Function GetSelectedEmployeeDataFromClosedWorkbook2() As Variant
Rem 1 Get data from closed workbook temporarily into this workbook
Dim strFilePath As String, strSheetName As String, strRange As String, strClosedWorkbook As String
 Let strFilePath = ThisWorkbook.Path '    CHANGE TO SUIT
 Let strClosedWorkbook = "Employees Deta.xlsx": Let strSheetName = "EMPDeta": Let strRange = "A1:J1000"
'  we need a closed workbook referrence string of this sort of form   ='G:\BlogsBugs\Closed Workbook\[Employees Deta.xlsx]EMPDeta'!A1
 Let ThisWorkbook.Worksheets("Temporary EMPDeta").Range(strRange) = "='" & strFilePath & "\[" & strClosedWorkbook & "]" & strSheetName & "'!A1"
Rem 2 Get sub range selection criteria data
Dim startDate As Long, endDate As Long, employeeNumber As Long
Dim ws As Worksheet
 Set ws = ThisWorkbook.Sheets("ArrearsFormat")
 Let startDate = ws.Range("H4").Value         '   43891
 Let endDate = ws.Range("I4").Value           '   43983
 '                                                                                                                       .... WHERE [Date] >= #" & startDate & "# AND [Date] <= #" & endDate & "# AND
 Let employeeNumber = ws.Range("C4").Value    '   37224
Rem 3 Get the start and stop row for the required sub range
    If ThisWorkbook.Worksheets("Temporary EMPDeta").Range("B2").Value2 = employeeNumber And ThisWorkbook.Worksheets("Temporary EMPDeta").Range("A2").Value2 <= endDate Then
    Dim subrngstartRow As Long, subrngstopRow As Long
     Let subrngstartRow = 2: Let subrngstopRow = 2
        Do While ThisWorkbook.Worksheets("Temporary EMPDeta").Range("A" & subrngstartRow & "").Value2 < startDate
         Let subrngstartRow = subrngstartRow + 1
        Loop
      Let subrngstopRow = subrngstartRow
        Do While ThisWorkbook.Worksheets("Temporary EMPDeta").Range("A" & subrngstopRow & "").Value2 < endDate
         Let subrngstopRow = subrngstopRow + 1
        Loop
Rem 4 get the sub range array
    Dim arrData() As Variant
     Let arrData() = ThisWorkbook.Worksheets("Temporary EMPDeta").Range("A" & subrngstartRow & ":J" & subrngstopRow & "").Value2
    Else
    End If
 Let GetSelectedEmployeeDataFromClosedWorkbook2 = arrData()
End Function

[Priyantha]

Thak u soo mach

Priyantha

[DocAElstein]

u Willkommen isd, Thak you auch for the feedback