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;"
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" <email@example.com> wrote in message
> 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
> 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
> Pse help