f



Automatic transfer AS400 -> Excel

Hello

I am trying to transfer data from an AS400 system to a Excel sheet,
Now this is done manually by cliking the add-in from Client Access named 
CWBTFXLA.XLL
This works but the data has to be formatted afterwards,
I want to make the transfer automatic via VBA en then run some code on it.

The formating code works but i can't get the automatic transfer to work.

Mind you, I'am a noob on the AS400 and do not have access to the command 
prompt

Pse help
0
warzel (1)
1/17/2005 4:53:48 PM
comp.sys.ibm.as400.misc 9219 articles. 4 followers. Post Follow

2 Replies
1373 Views

Similar Articles

[PageSpeed] 20

Add a module to your project in the Visual Basic Editor
Add a reference to ado to your project on the vb editor (tools - 
references - microsoft activex data objects)
Add a sub procedure to to your module called Auto_Open
Forget using the client access add in - usa vba code to get the data

here is a sample

 Public Sub Auto_Open()
Dim rst As ADODB.Recordset
Dim strSQL As String


strSQL = "Select * from mylib.myfile"

Set rst = New ADODB.Recordset

' xxx =  your as/400 ip address

rst.open strSQL, "Provider=IBMDA400;Data Source=XXX.XXX.XXX.XXX;"

Range("A2").CopyFromRecordset  rst


End Sub

this is for office 200 and higher
whenever the workbook is opened, this Auto_Open procedure will run
this will fill the worksheet starting in cell A2 with the data  You will 
have to add column headers.


"Warzel" <warzel@toegang.voor.iedereen.nedeland> wrote in message 
news:41ebeda1$0$6221$e4fe514c@news.xs4all.nl...
> Hello
>
> I am trying to transfer data from an AS400 system to a Excel sheet,
> Now this is done manually by cliking the add-in from Client Access named 
> CWBTFXLA.XLL
> This works but the data has to be formatted afterwards,
> I want to make the transfer automatic via VBA en then run some code on it.
>
> The formating code works but i can't get the automatic transfer to work.
>
> Mind you, I'am a noob on the AS400 and do not have access to the command 
> prompt
>
> Pse help 


0
rmoore7378 (13)
1/17/2005 5:23:16 PM
Try from exel Data-import external data new database qry adn chosse db2
i think from here you know what to do 

best regards 

Leonel

0
1/17/2005 7:40:28 PM
Reply: