Lotus 123 /dqri /dqi macro function to Excel 1

[cwinnyk]

Hello everyone. I have been instructed to convert some Lotus 123 R5 spreadsheets to Excel 2002 format. However, there's a command in Lotus to where I can't find an Excel equivilent. Here's the Lotus command and syntax

/dqi[input]~c[criteria]~o.[output]~eq

Is there an Excel command that does the same exact thing? I'm under instructions to keep the spreadsheets and macro procedures as similar as possible. Thanks in advance for anyone's help.

 

[xlbo]

Maybe if you told us what it did, we can help.....?????

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[cwinnyk]

my apoligies. Its a Database Query command. It operates similarly to functions like DCOUNT, except its return a table of values instead of a single value. Here is the exact macro code in Lotus 123:

/dqridbase~cce_1~o.{right 18}{end}{down}{up}~eq

/dqri is the database query command

dbase is the name of the range holding the data

~c tells the macro that the criteria is about to follow

ce_1 is the name of the range that holds the criteria. In this case, the criteria is 3 columns of data. It should be noted that one of the columns is using regular expressions/wildcards. For example, A412305 in dbase would match A4???0? in the ce_1, while A512305 would not.

~o. tells the macro that the following arguments define where to output the returned data.

.{right 18}{end}{down}{up} are the arguments that define the output range

~eq executes the query, I beleive.

 

[Zathras]

What you are describing sounds like Data/Filter/Advanced Filter... (from the menu). If you just record a macro, you should be able to modify the code.

For example, I just recorded the following:
[blue]

Option Explicit

Sub Macro1()

[green]

'
' Macro1 Macro
' Macro recorded 5/27/03 by Zathras
'

'

[/color][/code]
Range("database&quot.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("Criteria&quot, CopyToRange:=Range("I1&quot, Unique:=False
End Sub
[/code][/color]

 

[cwinnyk]

The advanced filter gets the rows I need, thank you.

There's still two issues, however.

1) It needs to copy the filtered data onto another sheet in the same workbook, which Excel is not letting me do because the destination sheet is different than the source sheet used to retreive the data.

2) I could manually copy the results from the source sheet to the destination sheet. Unfortunatly, the columns in the destination sheet are in a different order than the columns on the source sheet. The source sheet also has columns returned in the filter that are not to be placed on the destination sheet. The destination has column headers as the first row. So, I assume Lotus used these column headers in the /dqri command to figure out what data to copy over and where exactly to place it.

I'm sorry if this is confusing, let me know if you need me to clarify anything.

 

[cwinnyk]

I got it! Had to play around with the Macro code a bit. Apparently, you can get the advanced filter to copy the output like I need it through VBA. In case anyone was wondering, this is how I got it to work:

Application.GoTo Reference:="EL_1"
Range("DBASE&quot.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("CE_1&quot, CopyToRange:=Range (Selection, Selection.End(xlDown).Offset(-1, 18)), Unique:=False

Thank you so much for you help!