Upload Excel-files in AS400 with VBA

[JPDO]

Hello,
I have to upload different Excel-files in AS400.
I can not use the Excel add-in to transfer data to AS400.
I have only a Client Acess option to connect to AS400( V5R3).
How can i create a VBA-macro that :
1- open an xls-file on the C-drive
2- make a connection to AS400 (prompting for UserId + Password)
3- if file on AS400 not exists, create the PF based on the Xls-file
If file on AS400 exists, replace the content of it
4- transfer the content of the xls-file to the PF on AS400

Can someone point me out how to do this ?

Thanks for any reply.

[arrow483]

Re: Upload Excel-files in AS400 with VBA

Have you looked at using CPYFRMIMPF command ?

[astech]

Re: Upload Excel-files in AS400 with VBA

Check also:

http://www.vrtech.biz/ExcelIt.htm

[Denny]

Re: Upload Excel-files in AS400 with VBA

Here is a model that I picked up somewhere on the web. I have been playing with a copy of it and have got it to work on my system.

Hummm. Looks like this site will not allow me to upload a .xls.

[Denny]

Re: Upload Excel-files in AS400 with VBA

Lets try again.

Also should have read your request more carefully. This is an Excel VBA program, so it will not work as a CA macro.

Take a look at "Use PCOMM Scripts to Dynamically Build a Spreadsheet, Part 2.

http://www.itjungle.com/fhg/fhg052108-story01.html

[JPDO]

Re: Upload Excel-files in AS400 with VBA

THanks Arrow483 and Denny for your responses

I first like to know HOW i can set up from VBA Excel a connection with the AS400-system.
What have i to do on AS400-side and what have i to do on the Excel-side ?

Thks

[DeadManWalks]

Re: Upload Excel-files in AS400 with VBA

Hey JPDO, look at the article and the txt file. It has it all.

[JPDO]

Re: Upload Excel-files in AS400 with VBA

HI,
What means "setting up a System-DSN" ?

[littlepd]

Re: Upload Excel-files in AS400 with VBA

That's ODBC. If I were you, I would use OLE DB instead of ODBC. Can you get your hands on the iSeries Access for Windows installation CD? You should install the OLE DB driver that comes with that.

[JPDO]

Re: Upload Excel-files in AS400 with VBA

Thanks for your answer, Littlepd.
Can i check anywhere if the OLE DB-driver is already installed ?
Using VB-scripts make troubles for me: when I will start the script-code i can not give up where those code is located. In can simply not key-in something and i think those things are forbidden on the system.
So I first need to know what's necessary on the I-series before i start to code in VBA.

[littlepd]

Re: Upload Excel-files in AS400 with VBA

So I first need to know what's necessary on the I-series before i start to code in VBA.

Nothing (I think)

The database and a TCP/IP is all you should need to enable remote OLE DB access.

For more info, see "A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support".