Download AS400 Physical File to Excel Using VBA

5 pts.
Tags:
AS/400
Microsoft Access
Microsoft 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

Answer Wiki

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

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

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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:
    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
    125,585 pointsBadges:
    report
  • 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 tsoh@minebea.com.sg
    210 pointsBadges:
    report
  • 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. http://db.tt/MxkZOEd8 Require : VBA, SQL, Excel, Client Access
    1,760 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following