Excel VBA to extract iSeries data

[jtemple]

I'm looking for examples of how to execute a query against the iSeries from an Excel VB script and update a column in each row. For example, I have a spreadsheet with item numbers and I need to add the item's description to another column in the sheet. And obviously I need to use the item number in the current row as the selection criteria in the query.

I've found a few examples of using ADO/ODBC in an Excel script but wondered if anyone has one they would be willing to share.

Thanks!

Jonas

[DeadManWalks]

Re: Excel VBA to extract iSeries data

Here is a simple one that does an upload.

[bryce4president]

Re: Excel VBA to extract iSeries data

eeewwww.....isn't the iseries supposed to eliminate the need for Excel?

One question Jonas...why not just do wrkqry, and save the query. Then you can rerun the query and it will have all your updated data? Or is this for people who like pictures of data?

[GLS400]

Re: Excel VBA to extract iSeries data

Hey DeadMan:

Very impressive.
I didn't know that could be done.
I guess I know what I'll be doing for the next few days.

Thanks!!!

GLS

[FaStOnE]

Re: Excel VBA to extract iSeries data

GLS... you're just a Code @#%@#%@#%@#%@#%! LMAO!!!

[GLS400]

Re: Excel VBA to extract iSeries data

Sure beats working for a living!!!

All the best
GLS

[littlepd]

Re: Excel VBA to extract iSeries data

This example uses a function to retrieve the G/L account decription. The chart of accounts is downloaded only once the first time you need it. Then each subsequent call to the function uses the filter method of the recordset object like you would do a chain in RPG. Except the file is local in memory.

[Dushan]

Re: Excel VBA to extract iSeries data

Hi DeadManWalks
I'm trying modify your example and use it for my need.

Could you please more explain next rows of your code or send me your db file MM4R4LIB/INSPMST, than I can compare it and find where is mine problem.

For RowCount = 9 To 16000
objRs.AddNew
'If the user did not enter values in all three fields, get cout
If Cells(RowCount, 1) = "" _
Or Cells(RowCount, 2) = "" Or _
Cells(RowCount, 3) = "" Then
Exit For
End If

BadField = "TRNDT"
ThisInteger = Cells(RowCount, 1).Text

objRs.Fields(3) = Cells(RowCount, 1).Text 'Shop Field

objRs.Fields(1) = UCase(Cells(RowCount, 2).Text) 'Item Field

BadField = "WKCTR"
ThisInteger = Cells(RowCount, 2).Text
objRs.Fields(3) = Cells(RowCount, 1).Text 'Quantity Field

objRs.Update
ThisCount = ThisCount + 1
' Start next iteration of RowCount loop.
Next RowCount

ThisCountA = CStr(ThisCount)
msg = "UpLoaded (" & ThisCountA & ") record(s). Use menu option to complete"
MsgBox msg, vbOKOnly
' Clear cells, exit with out save.
objConn.Close

Thanks for any help
Dushan