SourceFile problem after changing a macro

[celias]

Hello all,
I've been trying to change a pre-existing excel file to make it work with different source data. It is a timesheet master file that gathers data from a set of separate excel sheets which are each individual timesheet, and builds a report based on that information.

I changed the list of employees and their names, I changed the list of clients names and I changed the location of the source files on the macro to the location where my new files now are. Unfortunately I am not an expert in Excel or VBA and I am missing something on the code that prevents the macro to run completely. It gives me the following error: "The source file or source range is invalid!". This message is in fact set up by the module code itself. Would any of you be so kind to have a look and see where the error could be?

Thank you in advance for any help you might provide.

I am pasting the code of the module below, where I think the error might be, but in fact this project is working with 4 different files at the same time and I can attach them all if necessary.
Thanks, again.

Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
    Application.Volatile
    On Error Resume Next ' ignore any errors
    For Each cl In InputRange
        UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
    Next cl
    On Error GoTo 0
    CountUniqueValues = UniqueValues.Count
End Function

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
    TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
'   this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this will return data from any worksheet in SourceFile
' SourceRange must include the range headers
'
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    Set TargetCell = TargetRange.Cells(1, 1)
    If IncludeFieldNames Then
        For i = 0 To rs.Fields.Count - 1
            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
        Next i
        Set TargetCell = TargetCell.Offset(1, 0)
    End If
    TargetCell.CopyFromRecordset rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set TargetCell = Nothing
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Sub
InvalidInput:
    MsgBox "The source file or source range is invalid!", _
        vbExclamation, "Get data from closed workbook"
End Sub

 

[JLGWhiz]

Since the error message you are getting is a user programmed message, it really does not provide a clue as to what caused the error. But, I suspect that your ADODB connection might not be connecting. To get the actual Excel error message, you can comment out the line:

On Error GoTo InvalidInput

and let VBA generate its own error message which will highlight the line of code where the error occurs if you click the Debug button. You can edit the code line by line using the F8 function key by placing the mouse pointer anywhere inside the code and left clicking once. Then use the F8 key to step through the code. By hovering the mouse pointer over variables, the tool tips pop up will display the values and you can see where the error actually occurs.

 

[celias]

I guess that you are right. This is the error message that I get:

Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Excel Driver]'9unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

And this is the line the Debug button is pointing out:
dbConnection.Open dbConnectionString ' open the database connection

By hovering the mouse on that line, the error message that comes up is: "dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile".

I have no idea of what this might be. Any tips would be very much appreciated. Could this be referring to a server to which my computer is not connected to, or to which my login credentials do no give me access to?

Thank you.

 

[JLGWhiz]

I am afraid that my knowledge regarding ADODB is practically zero. Maybe someone more astute in database queries will pick up on the thread and give you an answer. If you don't get any response within the next few hours, just click the reply button and type "BUMP" and post the replay.

 

[celias]

Bump