Download AS400 Physical File to Excel Using VBA

5 pts.
Microsoft Access
Microsoft Excel
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

Answer Wiki

Thanks. We'll let you know when a new response is added.


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.

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.”
WScript.Echo “ERROR: Transfer Not Successfully.”
End If


Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TomLiotta
    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:
    To select an overview of files in schema 'MYSCHEMA', you might select like this:
    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
    125,585 pointsBadges:
  • tedsoh
    Hi, I had create a sample Excel VBA. Email me your Email address and i will send you the sample file. My email is
    210 pointsBadges:
  • Craig Hatmaker
    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. Require : VBA, SQL, Excel, Client Access
    1,855 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: