Query a Lotus 123 File

NateO · Nov 1, 2003

Good day fellow board members,

I'm moving up to the starting line of interfacing MS Sql servers, which I believe are housing Peoplesoft and another ERP system's data. I'm going to work with the respective companies involved, but I was doing some quick research in advance of this.

It became apparent to me that ODBC is a good option for querying in Excel, but I'd have rework the native functionality to push data. My readings have also lead me to believe that while I can use ODBC, OLEDB is MS's preferred interface, faster and more robust.

As a practice exercise, I decided to use OLEDB and ADO to interface Lotus 123 and Excel, just to get more comfortable with the methods. I came up with the following, which is working nicely today:

Sub test()
Dim LotusCn As Object, rsLotus As Object
Dim strSql As String
Set LotusCn = CreateObject("ADODB.Connection")
LotusCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=C:\temp\FILE0014.WK4;" & _
 "Extended Properties=Lotus WK4;Persist Security Info=False"
'Look @ Sheet a, a3:12
strSql = "Select * from [a:a3..a:a12]"
Set rsLotus = CreateObject("ADODB.Recordset")
With rsLotus
 .Open strSql, LotusCn, 3, 3 'adOpenStatic, adLockOptimistic
 If Not .EOF Then _
 Sheets(1).[a1].Resize(.RecordCount).CopyFromRecordset rsLotus
 .Close
End With
LotusCn.Close
Set rsLotus = Nothing: Set LotusCn = Nothing
End Sub

But it didn't work last night! In fact, it completely killed my ability to access the file in any manner... So, I have some reservations with this...

Turns out I was on a network last night, whereas today I'm not... Could this be the reason? It's the only real change, and I know networks can bollox up Excel and automation.

Please, for both Christ's sake and mine, do not respond to this telling me to save the .wk4 as an Excel file or open the file in Excel, that is not the point of this exercise!

I simply want to get more experienced with Automation via OLEDB. Also, I don't believe the board houses code of this nature, so I wanted to provide it.

Also note, do not even bother attempting this with a .123 file, it should work with .wk3, but MS has clearly stated they don't have the drivers or interest in interfacing .123 files, it simply isn't an option from them, you might be able to buy a 3rd party driver out there, but I don't know of one, nor am I interested in find out.

Regarding the code, in ADO .EOF is the most efficient way of determining if a recordset holds any information, RecordCount is reliable in ADO (not in DAO until you move to the end of the recordset), but it's bollox slow, because of it's emphasis on accuracy. Use it for dynamic returns only (I use here simply to use it, I will have dynamic returns in the long run). Also note, I use late binding as a preference, early binding is an option and the more efficient one, late binding can prove to have less maintenance issues associated with it.

Any thoughts regarding the code? Have a nice weekend everyone!

dk · Nov 1, 2003

Nate,

I had a quick look at the code. Is this a valid reference? :-

"Select * from [a:a3..a:a12]"

Anyway, I changed the reference to

strSql = "Select * from [Sheet1$A1:B10]" and it still wouldn't work with the WK4 file (error The Microsoft Jet database engine could not find the object 'A1:A10'....) . Strangely, the same code works with an Excel file using the SQL statement from above. Anyway, the only way I could get the code to work was to use name the range in the WK4 file and use this:-

strSql = "Select * from MyRange"

I'm not sure whether this is your issue but maybe. Can you get the A1 style reference to work? Is using a named range in the WK4 file an option?

I'm now off to Brisbane to watch England wipe the floor with Uruguay

. I will take a look at this thread when I get back.

Regards,
Dan

XL-Dennis · Nov 1, 2003

Hi Nate,

Hope it is alright with You

Following seems to work well:

Option Explicit

Sub test()
Dim LotusCn As ADODB.Connection
Dim rsLotus As ADODB.Recordset
Dim strSql As String

Set LotusCn = New ADODB.Connection
Set rsLotus = New ADODB.Recordset

LotusCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\Arbetsmaterial\Test11.WK4;" & _
"Extended Properties=Lotus WK4;Persist Security Info=False"

strSql = "SELECT * FROM [a:a3..a:b12]"

rsLotus.Open strSql, LotusCn

With Sheets(1).[a1]
.CurrentRegion.Clear
.CopyFromRecordset rsLotus
End With

LotusCn.Close
Set rsLotus = Nothing
Set LotusCn = Nothing
End Sub

as well as using late binding:

Option Explicit

Sub test()
Dim LotusCn As Object
Dim rsLotus As Object
Dim strSql As String

Set LotusCn = CreateObject("ADODB.Connection")
Set rsLotus = CreateObject("ADODB.Recordset")

LotusCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\Arbetsmaterial\Test11.WK4;" & _
"Extended Properties=Lotus WK4;Persist Security Info=False"

strSql = "SELECT * FROM [a:a3..a:b12]"

rsLotus.Open strSql, LotusCn

With Sheets(1).[a1]
.CurrentRegion.Clear
.CopyFromRecordset rsLotus
End With

LotusCn.Close
Set rsLotus = Nothing
Set LotusCn = Nothing
End Sub

What kind of error-message to You get?

Kind regards,
Dennis

NateO · Nov 2, 2003

Good day Gentlemen, thanks for having a look and responding!

Dan, I think this is the way, it looks goofy from Excel perspective, did you change a: in all instances, this is the sheet reference. I couldn't put a named range in if I wanted to. I have access to 123 files, but I no longer own the software (did 3 years ago). And I really didn't learn 123 to any extent, I'd become a fan of Excel at that point. Perhaps one of the reasons I was hired, to convert some massive 123 financial models to an Excel platform. Hope you enjoyed the game!

Dennis, hope you are well sir! Thanks for the code. As I mentioned, today, the code works very well. Friday night in a networked environment, not so hot. I'll test it again Monday and see what I get... Yes, I should have provided the error, it was of the most ambiguous nature (worst kind):

Run-time error "-2147467259(80004005)':
Unspecified error.

Is this what you got Dan? There's no description, if there was I would imagine it would read something like:

You done ****ed up. We [MS] don't know why or how, but guess again sucko.

I've seen this animal before with focus issues on Active X controls, but this is odd, focus is not the issue (I think!)... Works today, not Friday night. Same computer, same files, same code... Only difference: Non-networking environment.

Thanks again, I'll follow up once I get back to my LAN and see if this puppy fires the way it should. The file-locking is a concern, it was fine locally following a reboot, I'm curious to see if the LAN files were fine once I killed my connection... Obviously I can't be indefinitely locking up shared files on a consistent basis!

Cheers guys.

XL-Dennis · Nov 2, 2003

Hi Nate,

Life good in this part of the world

Run-time error "-2147467259(80004005)':
Unspecified error.

The built-in VBA-errorhandling cannot handle errors-related to the ADO Object Library so we need to explicit create ADO-errors objects as well as constants for different error-types.

Let me know if You need it in order to trace the error(s).

BTW, the interpretation of the error-message is absolutely correct

Kind regards,
Dennis

NateO · Nov 3, 2003

Hmmm, the code works perfectly today...

But, this is a good thing.

Glad I got the interpretation right!

The error trapping sounds interesting Dennis, share away!

XL-Dennis · Nov 3, 2003

Howdy Nate,

Glad that someone appreciate error-handling :wink:

Actually, this is a rather undocumented area so below You find a sample that show some properties. However I doubt it will qualify for the Hall of Shame

Option Explicit

Sub ADOErrors_OLEDBProvider()
Dim cn As ADODB.Connection
Dim stDB As String, stError As String
Dim i As Long

On Error GoTo Error_Handling

Set cn = New ADODB.Connection
stDB = ThisWorkbook.Path & "\" & "XLData1.mdb"

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

'More code here...

ExitHere:
cn.Close
Set cn = Nothing
Exit Sub

Error_Handling:
MsgBox "ADO Errors: " & CStr(cn.Errors.Count)

For i = 0 To cn.Errors.Count - 1
stError = ""
With cn.Errors(i)
stError = "Error " & CStr(i) & vbCrLf
stError = "Description " & .Description & vbCrLf
stError = "Number " & CStr(.Number) & vbCrLf
stError = "NativeError " & CStr(.NativeError) & vbCrLf
stError = "Source " & .Source & vbCrLf
stError = "SQLState " & .SqlState
MsgBox stError, vbExclamation, "ADO Errors"
End With
Next i

cn.Errors.Clear
Set cn = Nothing
Resume ExitHere
End Sub

In addition we can also use the event:

Private Sub cn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As
ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)

There also exist errorhandling for Recordset as well.

Kind regards,
Dennis

NateO · Nov 3, 2003

Very good then! Indeed, a description might be nice.

Thank-you sir.

XL-Dennis · Nov 3, 2003

Nate,

Yes, the description-property is very useful.

Another point is to add following to a procedure:

Code:

If Not Lotusrs Is Nothing Then
        rsLotus.Close
        Set Lotusrs = Nothing
End If
If Not Lotuscn Is Nothing Then
        Lotuscn.Close
        Set Lotuscn = Nothing
End If

In order to get a save journey as possible :wink:

Kind regards,
Dennis

Niodice · Sep 16, 2013

Hi all,

I Googled my doubt and got here. First of all gratings and thanks for your help in advance. Excuse my spelling as I am from Spain...

The fact is that I am now migrating all software from lotus 123 to Excel, but the clients database is in Lotus123 and has to be like that while migrating. So the new Excels should be able to call CODES.WK4 and give me name and address of the client just putting Client No in a cell.

So after reading this thread, I am trying to query a Lotus123 file to an Excel file. My specifications are:

Working in a Windows 7 64bits platform with MSOffice 2010 (no lotus123 in this machine). Lotus123 5.01 installed in a virtual machine with Windows XP.

I am not a VBA Expert but I know do know about it.

I tried this:
Sub test()
Dim LotusCn As Object
Dim rsLotus As Object
Dim strSql As String

Set LotusCn = CreateObject("ADODB.Connection")
Set rsLotus = CreateObject("ADODB.Recordset")

LotusCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=U:\CODES.WK4;" & _
"Extended Properties=Lotus WK4;Persist Security Info=False"

strSql = "SELECT * FROM [D:E17..D:G800]" ' I have Client No. in sheet D, cell E## and Client Names at D:G##. Ideally gives me back name just reading Client's No. from Range ("CLIENT_NO")

rsLotus.Open strSql, LotusCn 'Here comes the error

With Sheets(1).[a1]
.CurrentRegion.Clear
.CopyFromRecordset rsLotus
End With

LotusCn.Close
Set rsLotus = Nothing
Set LotusCn = Nothing
End Sub

But got the error:

"Run-Time error blablabla: The Microsoft jet database engine could not find the object 'D:E17..D:G800'. Make sure..."

Thank you very much.

Query a Lotus 123 File

NateO

Legend

Excel Facts

dk

MrExcel MVP

XL-Dennis

Well-known Member

NateO

Legend

XL-Dennis

Well-known Member

NateO

Legend

XL-Dennis

Well-known Member

NateO

Legend

XL-Dennis

Well-known Member

Niodice

New Member

Similar threads

Forum statistics

Share this page

Query a Lotus 123 File

Legend

Excel Facts

MrExcel MVP

Well-known Member

Legend

Well-known Member

Legend

Well-known Member

Legend

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock