Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

So my problem is that between 2 different computers my connection to the string no longer can open a connection.

Here is what I have:

Dim sFileName As String
Dim sFilePath As String

sFileName = "DataSource.xls"
sFilePath = "C:\test\testingData\"

Dim sConn As String
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sFilePath & sFileName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes"";"

Dim errorObject As ADODB.Error
Set errorObject = CreateObject("ADODB.Error")

On Error GoTo ErrorDisplay:

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
conn.Open sConn

Dim rst As ADODB.Recordset '* Record Set
Set rst = New ADODB.Recordset

rst.Open sSql, conn, adOpenForwardOnly

Worksheets(1).Range("A2").CopyFromRecordset rst

sSQL = "SELECT * FROM [MySheet$] WHERE [Status] = 2 AND [DocNo] LIKE '%-A%'

When I run this section of code on one computer it runs perfectly as I built it. When I try it on a similar computer it keeps giveing an error "Object variable or With Block variable not set". I have found out that the problem is with the conn.Open sConn but not sure what is causing it.

I have been searching about looking for a solution but as of yet I have not found anything that worked.

Additional Info:
Both computers:
Windows 7 x64
Excel 2007 x32

Any thoughts as to why this would not work?

--Update: I just finished trying the excel file with the macro out on a 3rd windows 7 computer and that one works fine.

--Update: I have just tried running the program from 

sFilePath = "C:\Users\FirstName.LastName\Desktop\"

and 

sFilePath = "C:\Users\FirstName.LastName\Desktop\Test\"

The thing I would really like to know is that it's working from the "C:\Users\FirstName.LastName\Desktop\Test\" but none of the others???

share|improve this question
You sure the path is correct on the second computer?? – John Bustos Jun 12 at 21:18
Yes, I have double checked the path a few times. And it is indeed correct on both – NewfieEdge Jun 12 at 21:51
add comment (requires an account with 50 reputation)

2 Answers

up vote 0 down vote

You may try below code. 

' Add reference to Adodb library
    Dim conn As ADODB.Connection

    Public Function CreateConnection() As Boolean
        On Error GoTo ErrorRet

        Dim X As Integer
        Dim sConn As String
        Dim sFileName As String
        Dim sFilePath As String

        X = 0
    Reconn:
        X = X + 1

        sFileName = "DataSource.xls"
        sFilePath = "C:\test\testingData\"

        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & sFilePath & sFileName & ";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes"";"

        Set conn = New ADODB.Connection
        conn.Open sConn

        CreateConnection = True
        Exit Function

    ErrorRet:
        If Err.Number = -2147467259 Then
            GoTo Reconn
        Else
            If X > 5 Then
                CreateConnection = False
            Else
                GoTo Reconn
            End If
        End If
    End Function

    Sub test()

        Dim sSql As String
        Dim rst As ADODB.Recordset
        Set rst = New ADODB.Recordset

        If CreateConnection Then
            sSql = "SELECT * FROM [MySheet$]"
            rst.Open sSql, conn
            Worksheets(1).Range("A2").CopyFromRecordset rst

        End If

    End Sub
share|improve this answer
add comment (requires an account with 50 reputation)
up vote 0 down vote

Are you sure that the Primary Interop Assemblies are installed on both computers?

share|improve this answer
I am not exactly sure what that is. How would I check to see if it is installed? – NewfieEdge Jun 12 at 21:48
It is a small program very easy to download and to install: microsoft.com/en-us/download/details.aspx?id=18346 it is intended for coupling .NET programs with MS Office but the connection string you are using is equivalent to the one used by .NET programs, so I guess that it is testing-worthy. – varocarbas Jun 12 at 21:55
It's definately a strange problem since the 3 machines are just Ghosts of a master copy – NewfieEdge Jun 12 at 23:03
Strange things tend to happen with MS Macros. Installing the Primary Interop Assemblies is a really quick process (less than 1 min. for downloading and installing) and might be useful anyway (you need that to perform any MS Office 2007 coupling with a .NET application), that's why my suggestion: it is a harmless and quick check. – varocarbas Jun 13 at 7:21
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.