StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel 2007 VBA - Error: "Object variable or With Block variable not set" when using OLEDB connection to another Excel File
text
Body
copied!<p>So my problem is that between 2 different computers my connection to the string no longer can open a connection.</p> <p>Here is what I have:</p> <pre><code>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 </code></pre> <hr> <pre><code>sSQL = "SELECT * FROM [MySheet$] WHERE [Status] = 2 AND [DocNo] LIKE '%-A%' </code></pre> <hr> <p>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 <strong>"Object variable or With Block variable not set"</strong>. I have found out that the problem is with the <strong><em>conn.Open sConn</em></strong> but not sure what is causing it.</p> <p>I have been searching about looking for a solution but as of yet I have not found anything that worked.</p> <p>Additional Info: <br> Both computers:<br> Windows 7 x64<br> Excel 2007 x32 <br></p> <p>Any thoughts as to why this would not work?</p> <p><strong>--Update:</strong> I just finished trying the excel file with the macro out on a 3rd windows 7 computer and that one works fine.</p> <p><strong>--Update:</strong> I have just tried running the program from <br></p> <pre><code>sFilePath = "C:\Users\FirstName.LastName\Desktop\" </code></pre> <p>and </p> <pre><code>sFilePath = "C:\Users\FirstName.LastName\Desktop\Test\" </code></pre> <p>The thing I would really like to know is that it's working from the <strong><em>"C:\Users\FirstName.LastName\Desktop\Test\"</em></strong> but none of the others???</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload