Home > IT Answers > Microsoft Windows > Download AS400 Physical File to Excel Using VBA
Martyjw
5 pts.
 Download AS400 Physical File to Excel Using VBA
Access, AS/400, excel, VBA
I am in the process of developing an Access database with the intention of using it to control the download of AS/400 physical files into Excel.

I am fine developing the Access / Excel side of the data, however I ahve no experience with the AS/400 side of things.

The access database will contain information such as file names, libraries, server names etc.

Can someone please point me in the direction of some resources that will help me out. Thanks



Software/Hardware used:
AS400, Excel 2010, Access 2010, VBA
ASKED: November 30, 2011  9:55 AM
UPDATED: March 17, 2012  6:42 AM
RELATED QUESTIONS
Answer Wiki:
Hi, If you are using client access, you can add the reference to IBM AS400 Client Access Express ActiveX Object Library. With that you should be able to us VBA to access AS400 and Download the data. Below is a Visual Basic Script that i use to test the download from AS400. Hope it helps. ** START System = Wscript.Arguments.item(0) ASlib = Wscript.Arguments.item(1) ASFile = Wscript.Arguments.item(2) ASMbr = Wscript.Arguments.item(3) PCFolder = Wscript.Arguments.item(4) PCTYpe = Wscript.Arguments.item(5) ASLoc = ASlib & "/" & ASFile & "(" & ASMbr & ")" Set AS400 = CreateObject("cwbx.DatabaseTransfer") AS400.UserID = "PASS" AS400.Password = "PASS" AS400.Download System,ASLoc,PCFolder, PCType If AS400.Errors.Count = 0 Then WScript.Echo "File successfully transferred." Else WScript.Echo "ERROR: Transfer Not Successfully." End If WScript.Quit **END
Last Wiki Answer Submitted:  December 1, 2011  12:23 am  by  tedsoh   210 pts.
All Answer Wiki Contributors:  tedsoh   210 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

It ‘sounds’ like you’re trying to replicate portions of the system database catalog into Access, though I’m not clear what is meant by “servers” (nor what you might need the info for in Access).

Much of what you seem to want could be retrieved via ODBC (or your preferred RDBMS access method) out of the SYSIBM schema. For example, to select a list of database schemas (libraries) and their owners, you might select like this: 

SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER
   FROM SYSIBM.SCHEMATA;

To select an overview of files in schema ‘MYSCHEMA’, you might select like this: 

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, IS_INSERTABLE_INTO
   FROM SYSIBM.TABLES
   WHERE TABLE_SCHEMA = 'MYSCHEMA';

In short, you might review data that is available out of the database catalog to see how much you can get relatively directly. After that, there might be details you can post back here to ask about.

Tom

TomLiotta
 110,135 pts.
 

Hi,
I had create a sample Excel VBA.

Email me your Email address and i will send you the sample file.
My email is tsoh@minebea.com.sg

tedsoh
 210 pts.
 

Below is a link to anAS400 disk usage in Excel project file. You can use it to list all iSeries objects and disk size in Excel. You can even view the first 1,000 records of any PF or LF.
http://db.tt/MxkZOEd8

Require : VBA, SQL, Excel, Client Access

Craig Hatmaker
 1,495 pts.