PDA

View Full Version : VBA code giving error on some system



harish1805
02-02-2012, 01:31 AM
Hi All,
I have a VBA project which I sent to many people in different regions globally. Iam facing some issue here that some of the system they are unable to run the code and its giving some errors. I did a debug on their systems and found that they didn't have the library references set for
OLA/ADO/Vb application on excel etc.
Its not feaseable to change library references manual for all the users. Is there a way to update library references on fly or runtime from lowest version of excel and VBA.
Also why is it that for few systems it working without any problem and for some its not working. Please me help.
Thanks,
Harish Nair.

Bob Phillips
02-02-2012, 05:55 AM
Switch to late binding.

Need details of the code to help further.

harish1805
02-02-2012, 06:25 AM
James, please let me know how can I do a late binding.
Please find one of the sample code which I am working on.

Dim rs1 As ADODB.Recordset
Dim cn1 As ADODB.Connection
Dim strPath As String
Dim cap As String
Private Sub UserForm_Activate()
Set cn1 = New ADODB.Connection
strPath = ActiveWorkbook.FullName
cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & strPath & "';Extended Properties=Excel 8.0;Persist Security Info=False"
end sub

Private sub display_info()
Dim rs2 As ADODB.Recordset
Dim cn2 As ADODB.Connection
Set cn2 = New ADODB.Connection
strPath = ActiveWorkbook.FullName
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & strPath & "';Extended Properties=Excel 8.0;Persist Security Info=False"
sSQL2 = "SELECT DISTINCT [name] FROM [info$] where [cap] = '" & cap & "'"
Set rs2 = New ADODB.Recordset
rs2.Open sSQL2, cn2
If rs2.BOF = False Then
rs2.MoveFirst
Label6.Visible = True
Label6.Caption = rs2![name]
End If
rs2.Close
Set rs2 = Nothing
cn2.Close
Set cn2 = Nothing
end sub

Bob Phillips
02-02-2012, 06:49 AM
Try this



Dim rs1 As Object 'ADODB.Recordset
Dim cn1 As Object 'ADODB.Connection
Dim strPath As String
Dim cap As String

Private Sub UserForm_Activate()
Set cn1 = CreateObject("ADODB.Connection")
strPath = ActiveWorkbook.FullName
cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='" & strPath & "';" & _
"Extended Properties=Excel 8.0;" & _
"Persist Security Info=False"
End Sub

Private Sub display_info()
Dim rs2 As Object 'ADODB.Recordset
Dim cn2 As Object 'ADODB.Connection

Set cn2 = CreateObject("ADODB.Connection")
strPath = ActiveWorkbook.FullName
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='" & strPath & "';" & _
"Extended Properties=Excel 8.0;" & _
"Persist Security Info=False"
sSQL2 = "SELECT DISTINCT [name] FROM [info$] where [cap] = '" & cap & "'"

Set rs2 = CreateObject("ADODB.Recordset")
rs2.Open sSQL2, cn2
If rs2.BOF = False Then
rs2.MoveFirst
Label6.Visible = True
Label6.Caption = rs2![Name]
End If

rs2.Close
Set rs2 = Nothing
cn2.Close
Set cn2 = Nothing
End Sub

harish1805
02-02-2012, 10:31 AM
Thanks a lot, I did this its working for me hope it works for others too.. I have already sent this to them.. Surprisingly they also have same MS Excel 2007, not sure why are they getting this error then.

Bob Phillips
02-02-2012, 11:53 AM
Same Excel makes no difference, except in terms of the version of libraries. The ADO library is not automatically linked in VBA, it has to be done manually, you did, they hadn't.

harish1805
02-03-2012, 12:04 AM
James, then why is that for few people its working they haven't added any libarary, they don't know how to do it.

Bob Phillips
02-03-2012, 03:55 AM
Somehow they must have. Go on one of the machines, goto the VBIDE>References and check it.

harish1805
02-03-2012, 10:23 AM
James, one of them is still getting some error, attaching the screen shot. When they close the excel they get password prompt because of which they are unable to close it.

Bob Phillips
02-03-2012, 10:33 AM
NO ideas, I would need to see the system to even try to find the problem.

harish1805
02-03-2012, 10:38 AM
last time when i was looking at one of the users system i found they have something missing library checked, will that cause issue. because while it was checked i tried checking ado ref but it didn't work until i removed missing libarary checkbox.

Bob Phillips
02-03-2012, 10:40 AM
aAH! [MISSING] should always be unchecked, they create chaos.

harish1805
02-03-2012, 10:41 AM
will the code in this forum help me in removing it.. http://www.vbaexpress.com/kb/getarticle.php?kb_id=272