Re: Data transfer question

You could write a Macro like the following. You will need to add the reference to IBM AS/400 iseries Access for Windows ActiveX Object and Microsoft ActiveX Data Objects 2.8 Library. I threw this together so it might have a mistake in it but it seemed to work.


Sub Macro1()
'
' Macro1 Macro
'
Set objConn = New ADODB.Connection
objConn.Open "Provider=IBMDA400;" & _
"Data Source=xxx.xxx.xxx.xxx;"


Set tableinfoRS = New ADODB.Recordset

With tableinfoRS
.ActiveConnection = objConn
.Source = "Select LABEL FROM QSYS2.SYSCOLUMNS WHERE TABLE_NAME = 'DSPOBJD'AND TABLE_SCHEMA = 'MYLIB'AND COLUMN_NAME IN ('ODLBNM', 'ODOBNM')"
.Open

lngOffset = 1
'If Not .EOF Then
'add field headers
With tableinfoRS
Do Until .EOF
Sheet1.Cells(1, lngOffset).Value = tableinfoRS("Label")
Sheet1.Cells(1, lngOffset).Font.Bold = True
lngOffset = lngOffset + 1
.MoveNext
Loop
End With
.Close
End With


Set objRS = New ADODB.Recordset
With objRS
.ActiveConnection = objConn
.Source = "SELECT ODLBNM, ODOBNM FROM MYLIB.DSPOBJD"
.Open
If Not .EOF Then
'dump the recordset to the spreadsheet
Sheet1.Cells(2, 1).CopyFromRecordset objRS
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: no records returned.", vbCritical
End If
.Close
End With
'
End Sub





message: 1
date: Thu, 23 Jun 2011 10:21:23 -0400
from: "Greg Wenzloff" <GWenzloff@xxxxxxxxxxx>
subject: [MAPICS-L] Data transfer question

I need to transfer the contents of a MAPICS file into an Excel
spreadsheet.
When I do this I get column headings such as ORDNO.

Is there something I can do to get different column headings other than
the field name.
Perhaps ORDER NUMBER.

I want to automate this and not go into Excel to adjust column heading
each time this transfer is run.

Thanks,
Greg



This e-mail transmission may contain confidential or legally privileged information that is intended only for the individual(s) or entity(ies) named in the e-mail address. If you are not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail is strictly prohibited. If you have received this e-mail transmission in error, please notify the sender immediately, so that Endo can arrange for proper delivery, and then please delete the message from your system. Thank you.
<Virus Scanned by Microsoft ForeFront Online Security>