|
midrange.com
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 [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
