|
You can do this using Excel's VBA macro function if you have a ODBC driver like the one Client Access Loads by default. You do not need VB at all. You do need the VB DAO loaded. - see Microsoft for how to accomplish that. 1. Configure a DSN - Control Panel/Data Sources or Control Panel/Administrative Tools/Data Sources . Add a new system DSN, Select the Client Access ODBC Driver, Give it a Name (must match the connection string in macro - I used "PowerVision"), Select your system, and on the translation tab check the convert CCSID box. 2. Get into the VBA editor in Excel (Tools/Macro/Visual Basic Editor) 3. Select the DAO as a reference so you can use those objects in the VBA macro - (Tools/References) Check "Microsoft ActiveX Data Objects 2.1 Library" or whatever version you have loaded. 4. Create a new Module (Insert/Module) 5. Paste the following sample code which gets your Unit of Measure file from Mapics. (Just an example). *************************************************************** Sub getUOMs() Dim rs As New ADODB.Recordset Dim conn As New ADODB.Connection Dim sConnect As String Dim sSQL As String Dim rowcount As Integer Worksheets("Sheet1").Range("A1").Select sConnect = "Provider=MSDASQL.1;Connect Timeout=15;Extended Properties='DSN=PowerVision;UID=;PWD=;';Locale Identifier=1033" Set conn = New ADODB.Connection conn.Open sConnect Set rs = New ADODB.Recordset sSQL = "Select D1CQCD, D1A5TX FROM AMFLIB6.MBD1REP" rs.Open sSQL, conn, adOpenForwardOnly, adLockReadOnly If Not rs.EOF Then rs.MoveFirst rowcount = 1 Do While Not rs.EOF rowcount = rowcount + 1 Selection.Offset(rowcount, 0).Range("a1").Value = rs!D1CQCD Selection.Offset(rowcount, 1).Range("a1").Value = rs!D1A5TX If Not rs.EOF Then rs.MoveNext Loop MsgBox "Done" End Sub **************************************************************** 6. Run the Macro You can retrieve any iSeries data into Excel this way. Allan Schmit Sr. Systems Analyst Weir Slurry TM North America |---------+------------------------------> | | Memo From: | | | Chris.Kosieracki@Hi| | | ghJump.com | | | Sent by: | | | mapics-l-bounces@mi| | | drange.com | | | | | | | | | Sent: | | | 01/16/2004 09:23 AM| | | Please respond to | | | MAPICS ERP System | | | Discussion | | | | |---------+------------------------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| | | | From midrange.com | | To: mapics-l@xxxxxxxxxxxx | | cc: | | Subject: RE: Purchase Requisitions and EXCEL | >--------------------------------------------------------------------------------------------------------------------------------------------------| We've used a variety of file transfer and query tools to build functionality such as this. My personal favorite is Crystal reports. It allows you to build a report that matches pretty much all of text and data portions of the spreadsheet and provides export directly into Excel and several other formats. It also allows you to preview the data before the export, if desired. -----Original Message----- From: mapics-l-bounces@xxxxxxxxxxxx [mailto:mapics-l-bounces@xxxxxxxxxxxx] On Behalf Of Baldwin, Wayne (ALIE) Sent: Friday, January 16, 2004 9:13 AM To: mapics-l@xxxxxxxxxxxx Subject: Purchase Requisitions and EXCEL > We would like to pass some requisition fields as parameters to an Excel spreadsheet. Does anyone have a sample VB program which takes the parameters and places in Excel? We are not a VB shop but I do have VB software and with a simple skeleton program I could probably figure it out. and although I can get the User definition to collect the fields I need -still can't actually place data in Excel. > > > I don't want to use the copy to clipboard as it takes all the column headings/lines/spacing with it each time! > Any suggestions would be greatly appreciated. > > > > Wayne R. Baldwin > Senior Programmer Analyst > Smiths Aerospace Electronic Systems - Long Island > 1000 MacArthur Memorial Highway > Bohemia, NY 11716 > T: (631) 467 - 5500 Ext 258 > F: (631) 467 - 5943 > ****************************************** The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely for the use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of the company. The recipient should check this e-mail and any attachments for the presence of viruses. The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. ****************************************** _______________________________________________ This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/mapics-l or email: MAPICS-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/mapics-l. _______________________________________________ This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/mapics-l or email: MAPICS-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/mapics-l. The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage of any kind.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact copyright@midrange.com.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.