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" <[email protected]>
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>


_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.