Client Access to iSeries Automation

[Leslie]

I have installed an add-in for the transfer of data from my excel data to
iSeries. Every time I upload the data to iSeries, I have to select the data
range on the sheet and click "Data"->"Transfer Data To iSeries". As I have
several tables of data needed to upload in the sheet, I have to perform the
this procedure repeatedly. In order to save time, I'm trying to make use of
Macros to create a button with lines of code which automatically perform the
above action when I press the button. However, I'm not that familiar with
Excel Macro, I just couldn't find the add-in in the Object Browser and I
don't know the exact syntax to perform this function.
Can anybody help??

Thanks,
Leslie

[Bob Phillips]

Try turning on the macro recorder and do the steps manually, turn it off
and see what code you have.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> I have installed an add-in for the transfer of data from my excel data to
> iSeries. Every time I upload the data to iSeries, I have to select the
data
> range on the sheet and click "Data"->"Transfer Data To iSeries". As I have
> several tables of data needed to upload in the sheet, I have to perform
the
> this procedure repeatedly. In order to save time, I'm trying to make use
of
> Macros to create a button with lines of code which automatically perform
the
> above action when I press the button. However, I'm not that familiar with
> Excel Macro, I just couldn't find the add-in in the Object Browser and I
> don't know the exact syntax to perform this function.
> Can anybody help??
>
> Thanks,
> Leslie

[Leslie]

Bob,

Thanks for your reply. I tried to use the Macro recorder before. But it
didn't work out correctly. It only recorded down the first step of data range
selectioin I performed. For the next step, click "Data"->"Transfer Data To
iSeries", it had nothing mentioned about it. I wonder if it is because the
Macro recorder cannot record procedures that involves the use of add-in
features. I'm trying to see if I could get this done by manual coding. But
the thing is I even couldn't see the add-in in the Object Browser.

Thanks,
Leslie

"Bob Phillips" wrote:

> Try turning on the macro recorder and do the steps manually, turn it off
> and see what code you have.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > I have installed an add-in for the transfer of data from my excel data to
> > iSeries. Every time I upload the data to iSeries, I have to select the
> data
> > range on the sheet and click "Data"->"Transfer Data To iSeries". As I have
> > several tables of data needed to upload in the sheet, I have to perform
> the
> > this procedure repeatedly. In order to save time, I'm trying to make use
> of
> > Macros to create a button with lines of code which automatically perform
> the
> > above action when I press the button. However, I'm not that familiar with
> > Excel Macro, I just couldn't find the add-in in the Object Browser and I
> > don't know the exact syntax to perform this function.
> > Can anybody help??
> >
> > Thanks,
> > Leslie
>
>
>

[Bob Phillips]

Leslie,

I know nothing of the product, but I suggest that you contact the vendor and
see if it can be done. I did a quick Google on iSeries but there was nothing
that really answered your question.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thanks for your reply. I tried to use the Macro recorder before. But it
> didn't work out correctly. It only recorded down the first step of data
range
> selectioin I performed. For the next step, click "Data"->"Transfer Data To
> iSeries", it had nothing mentioned about it. I wonder if it is because the
> Macro recorder cannot record procedures that involves the use of add-in
> features. I'm trying to see if I could get this done by manual coding. But
> the thing is I even couldn't see the add-in in the Object Browser.
>
> Thanks,
> Leslie
>
> "Bob Phillips" wrote:
>
> > Try turning on the macro recorder and do the steps manually, turn it off
> > and see what code you have.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Leslie" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have installed an add-in for the transfer of data from my excel data
to
> > > iSeries. Every time I upload the data to iSeries, I have to select the
> > data
> > > range on the sheet and click "Data"->"Transfer Data To iSeries". As I
have
> > > several tables of data needed to upload in the sheet, I have to
perform
> > the
> > > this procedure repeatedly. In order to save time, I'm trying to make
use
> > of
> > > Macros to create a button with lines of code which automatically
perform
> > the
> > > above action when I press the button. However, I'm not that familiar
with
> > > Excel Macro, I just couldn't find the add-in in the Object Browser and
I
> > > don't know the exact syntax to perform this function.
> > > Can anybody help??
> > >
> > > Thanks,
> > > Leslie
> >
> >
> >

[Leslie]

Bob,

I will try to contact the vendor. Thanks for your help!
I really appreciate it.

Thanks,
Leslie

"Bob Phillips" wrote:

> Leslie,
>
> I know nothing of the product, but I suggest that you contact the vendor and
> see if it can be done. I did a quick Google on iSeries but there was nothing
> that really answered your question.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > Thanks for your reply. I tried to use the Macro recorder before. But it
> > didn't work out correctly. It only recorded down the first step of data
> range
> > selectioin I performed. For the next step, click "Data"->"Transfer Data To
> > iSeries", it had nothing mentioned about it. I wonder if it is because the
> > Macro recorder cannot record procedures that involves the use of add-in
> > features. I'm trying to see if I could get this done by manual coding. But
> > the thing is I even couldn't see the add-in in the Object Browser.
> >
> > Thanks,
> > Leslie
> >
> > "Bob Phillips" wrote:
> >
> > > Try turning on the macro recorder and do the steps manually, turn it off
> > > and see what code you have.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Leslie" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have installed an add-in for the transfer of data from my excel data
> to
> > > > iSeries. Every time I upload the data to iSeries, I have to select the
> > > data
> > > > range on the sheet and click "Data"->"Transfer Data To iSeries". As I
> have
> > > > several tables of data needed to upload in the sheet, I have to
> perform
> > > the
> > > > this procedure repeatedly. In order to save time, I'm trying to make
> use
> > > of
> > > > Macros to create a button with lines of code which automatically
> perform
> > > the
> > > > above action when I press the button. However, I'm not that familiar
> with
> > > > Excel Macro, I just couldn't find the add-in in the Object Browser and
> I
> > > > don't know the exact syntax to perform this function.
> > > > Can anybody help??
> > > >
> > > > Thanks,
> > > > Leslie
> > >
> > >
> > >
>
>
>

[Tim Williams]

If the iSeries commands are on a toolbar you should be able to see the names
of the routines called by right-clicking anywhere on a toolbar and choosing
"customize". Then right-click on the iSeries button in question and see if
there is an "assign macro" option: this should give you a name you can use
in an "Application.Run..." call after you've selected each range.

Tim

--
Tim Williams
Palo Alto, CA


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thanks for your reply. I tried to use the Macro recorder before. But it
> didn't work out correctly. It only recorded down the first step of data
range
> selectioin I performed. For the next step, click "Data"->"Transfer Data To
> iSeries", it had nothing mentioned about it. I wonder if it is because the
> Macro recorder cannot record procedures that involves the use of add-in
> features. I'm trying to see if I could get this done by manual coding. But
> the thing is I even couldn't see the add-in in the Object Browser.
>
> Thanks,
> Leslie
>
> "Bob Phillips" wrote:
>
> > Try turning on the macro recorder and do the steps manually, turn it off
> > and see what code you have.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Leslie" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have installed an add-in for the transfer of data from my excel data
to
> > > iSeries. Every time I upload the data to iSeries, I have to select the
> > data
> > > range on the sheet and click "Data"->"Transfer Data To iSeries". As I
have
> > > several tables of data needed to upload in the sheet, I have to
perform
> > the
> > > this procedure repeatedly. In order to save time, I'm trying to make
use
> > of
> > > Macros to create a button with lines of code which automatically
perform
> > the
> > > above action when I press the button. However, I'm not that familiar
with
> > > Excel Macro, I just couldn't find the add-in in the Object Browser and
I
> > > don't know the exact syntax to perform this function.
> > > Can anybody help??
> > >
> > > Thanks,
> > > Leslie
> >
> >
> >

[Vic Eldridge]

Hi Leslie,

The following macro might give you somewhere to start. It selects 3
different ranges and clicks the toolbar button for each range. If your
tables are dynamic (ie they change in size from day to day) , post back with
more details on their size and postion. Also post back if you don't know how
to run the macro from a button.


Regards,
Vic Eldridge


Sub SendMultipleRangesToiSeries()

Range("A1:C10").Select
Application.CommandBars("Data").Controls("Transfer Data To
iSeries").Execute

Range("E1:H22").Select
Application.CommandBars("Data").Controls("Transfer Data To
iSeries").Execute

Range("J1:P13").Select
Application.CommandBars("Data").Controls("Transfer Data To
iSeries").Execute

End Sub



"Leslie" wrote:

> I have installed an add-in for the transfer of data from my excel data to
> iSeries. Every time I upload the data to iSeries, I have to select the data
> range on the sheet and click "Data"->"Transfer Data To iSeries". As I have
> several tables of data needed to upload in the sheet, I have to perform the
> this procedure repeatedly. In order to save time, I'm trying to make use of
> Macros to create a button with lines of code which automatically perform the
> above action when I press the button. However, I'm not that familiar with
> Excel Macro, I just couldn't find the add-in in the Object Browser and I
> don't know the exact syntax to perform this function.
> Can anybody help??
>
> Thanks,
> Leslie

[Leslie]

Tim,

Thanks for your reply. Your method works well. I successfully called out the
dialog box. But I would like to automate two more steps to complete the
transfer of data to iSeries. After the dialog box came out, I have to do two
things:

1. I have to click "Browse" to browse for the predefined Request template
file I made before to use it to connect to the iSeries.

2. I have to click "OK" to run the transfer.

Is there any way for me to pass parameters to automate these two steps so
that the user don't have to do anything for each request??

Thanks,
Leslie

"Tim Williams" wrote:

> If the iSeries commands are on a toolbar you should be able to see the names
> of the routines called by right-clicking anywhere on a toolbar and choosing
> "customize". Then right-click on the iSeries button in question and see if
> there is an "assign macro" option: this should give you a name you can use
> in an "Application.Run..." call after you've selected each range.
>
> Tim
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > Thanks for your reply. I tried to use the Macro recorder before. But it
> > didn't work out correctly. It only recorded down the first step of data
> range
> > selectioin I performed. For the next step, click "Data"->"Transfer Data To
> > iSeries", it had nothing mentioned about it. I wonder if it is because the
> > Macro recorder cannot record procedures that involves the use of add-in
> > features. I'm trying to see if I could get this done by manual coding. But
> > the thing is I even couldn't see the add-in in the Object Browser.
> >
> > Thanks,
> > Leslie
> >
> > "Bob Phillips" wrote:
> >
> > > Try turning on the macro recorder and do the steps manually, turn it off
> > > and see what code you have.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Leslie" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have installed an add-in for the transfer of data from my excel data
> to
> > > > iSeries. Every time I upload the data to iSeries, I have to select the
> > > data
> > > > range on the sheet and click "Data"->"Transfer Data To iSeries". As I
> have
> > > > several tables of data needed to upload in the sheet, I have to
> perform
> > > the
> > > > this procedure repeatedly. In order to save time, I'm trying to make
> use
> > > of
> > > > Macros to create a button with lines of code which automatically
> perform
> > > the
> > > > above action when I press the button. However, I'm not that familiar
> with
> > > > Excel Macro, I just couldn't find the add-in in the Object Browser and
> I
> > > > don't know the exact syntax to perform this function.
> > > > Can anybody help??
> > > >
> > > > Thanks,
> > > > Leslie
> > >
> > >
> > >
>
>
>

[Leslie]

Vic,

Thanks a lot for your kind reply!
I successfully called the dialog box out. But I would like to automate two
more steps to complete the transfer of data to iSeries. After the dialog box
came out, I have to do two things:

1. I have to click "Browse" to browse for the predefined Request template
file I made before to use it to connect to the iSeries.

2. I have to click "OK" to run the transfer.

Is there any way for me to pass parameters to automate these two steps so
that the user don't have to do anything for each request??

Thanks,
Leslie


"Vic Eldridge" wrote:

> Hi Leslie,
>
> The following macro might give you somewhere to start. It selects 3
> different ranges and clicks the toolbar button for each range. If your
> tables are dynamic (ie they change in size from day to day) , post back with
> more details on their size and postion. Also post back if you don't know how
> to run the macro from a button.
>
>
> Regards,
> Vic Eldridge
>
>
> Sub SendMultipleRangesToiSeries()
>
> Range("A1:C10").Select
> Application.CommandBars("Data").Controls("Transfer Data To
> iSeries").Execute
>
> Range("E1:H22").Select
> Application.CommandBars("Data").Controls("Transfer Data To
> iSeries").Execute
>
> Range("J1:P13").Select
> Application.CommandBars("Data").Controls("Transfer Data To
> iSeries").Execute
>
> End Sub
>
>
>
> "Leslie" wrote:
>
> > I have installed an add-in for the transfer of data from my excel data to
> > iSeries. Every time I upload the data to iSeries, I have to select the data
> > range on the sheet and click "Data"->"Transfer Data To iSeries". As I have
> > several tables of data needed to upload in the sheet, I have to perform the
> > this procedure repeatedly. In order to save time, I'm trying to make use of
> > Macros to create a button with lines of code which automatically perform the
> > above action when I press the button. However, I'm not that familiar with
> > Excel Macro, I just couldn't find the add-in in the Object Browser and I
> > don't know the exact syntax to perform this function.
> > Can anybody help??
> >
> > Thanks,
> > Leslie

[Leslie]

Hi Vic,

Thanks a lot for your reply!
I'm able to call out the dialog box using your method. But I would like to
automate two more steps to complete the transfer of data to iSeries. After
the dialog box came out, I have to do two things:

1. I have to click "Browse" to browse for the predefined Request template
file I made before to use it to connect to the iSeries.

2. I have to click "OK" to run the transfer.

Is there any way for me to pass parameters to automate these two steps so
that the user don't have to do anything for each request??

Thanks,
Leslie


"Vic Eldridge" wrote:

> Hi Leslie,
>
> The following macro might give you somewhere to start. It selects 3
> different ranges and clicks the toolbar button for each range. If your
> tables are dynamic (ie they change in size from day to day) , post back with
> more details on their size and postion. Also post back if you don't know how
> to run the macro from a button.
>
>
> Regards,
> Vic Eldridge
>
>
> Sub SendMultipleRangesToiSeries()
>
> Range("A1:C10").Select
> Application.CommandBars("Data").Controls("Transfer Data To
> iSeries").Execute
>
> Range("E1:H22").Select
> Application.CommandBars("Data").Controls("Transfer Data To
> iSeries").Execute
>
> Range("J1:P13").Select
> Application.CommandBars("Data").Controls("Transfer Data To
> iSeries").Execute
>
> End Sub
>
>
>
> "Leslie" wrote:
>
> > I have installed an add-in for the transfer of data from my excel data to
> > iSeries. Every time I upload the data to iSeries, I have to select the data
> > range on the sheet and click "Data"->"Transfer Data To iSeries". As I have
> > several tables of data needed to upload in the sheet, I have to perform the
> > this procedure repeatedly. In order to save time, I'm trying to make use of
> > Macros to create a button with lines of code which automatically perform the
> > above action when I press the button. However, I'm not that familiar with
> > Excel Macro, I just couldn't find the add-in in the Object Browser and I
> > don't know the exact syntax to perform this function.
> > Can anybody help??
> >
> > Thanks,
> > Leslie

[Tim Williams]

It's likely that your only approach may be SendKeys. It's not recommended
in cases where an alternative method is available, but in your case it might
be the only solution.

Check out the Help docs on SendKeys. Might be tricky to assign the file
though, unless there's a direct "text" input where the filename/path can be
typed.

Tim.

--
Tim Williams
Palo Alto, CA


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> Tim,
>
> Thanks for your reply. Your method works well. I successfully called out
the
> dialog box. But I would like to automate two more steps to complete the
> transfer of data to iSeries. After the dialog box came out, I have to do
two
> things:
>
> 1. I have to click "Browse" to browse for the predefined Request template
> file I made before to use it to connect to the iSeries.
>
> 2. I have to click "OK" to run the transfer.
>
> Is there any way for me to pass parameters to automate these two steps so
> that the user don't have to do anything for each request??
>
> Thanks,
> Leslie
>
> "Tim Williams" wrote:
>
> > If the iSeries commands are on a toolbar you should be able to see the
names
> > of the routines called by right-clicking anywhere on a toolbar and
choosing
> > "customize". Then right-click on the iSeries button in question and see
if
> > there is an "assign macro" option: this should give you a name you can
use
> > in an "Application.Run..." call after you've selected each range.
> >
> > Tim
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "Leslie" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob,
> > >
> > > Thanks for your reply. I tried to use the Macro recorder before. But
it
> > > didn't work out correctly. It only recorded down the first step of
data
> > range
> > > selectioin I performed. For the next step, click "Data"->"Transfer
Data To
> > > iSeries", it had nothing mentioned about it. I wonder if it is because
the
> > > Macro recorder cannot record procedures that involves the use of
add-in
> > > features. I'm trying to see if I could get this done by manual coding.
But
> > > the thing is I even couldn't see the add-in in the Object Browser.
> > >
> > > Thanks,
> > > Leslie
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Try turning on the macro recorder and do the steps manually, turn it
off
> > > > and see what code you have.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Leslie" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have installed an add-in for the transfer of data from my excel
data
> > to
> > > > > iSeries. Every time I upload the data to iSeries, I have to select
the
> > > > data
> > > > > range on the sheet and click "Data"->"Transfer Data To iSeries".
As I
> > have
> > > > > several tables of data needed to upload in the sheet, I have to
> > perform
> > > > the
> > > > > this procedure repeatedly. In order to save time, I'm trying to
make
> > use
> > > > of
> > > > > Macros to create a button with lines of code which automatically
> > perform
> > > > the
> > > > > above action when I press the button. However, I'm not that
familiar
> > with
> > > > > Excel Macro, I just couldn't find the add-in in the Object Browser
and
> > I
> > > > > don't know the exact syntax to perform this function.
> > > > > Can anybody help??
> > > > >
> > > > > Thanks,
> > > > > Leslie
> > > >
> > > >
> > > >
> >
> >
> >

[Jim Palmer]

Hi Tim and Leslie

Your message thread has been of great assitance to me.

The only difference I am transferring data from the iSeries instead of to it.

You helped me to discover how to open the dialog box via a macro.

Application.Run "fShowTToDialog"

I too would like to somehow pass the name of the file to open into the dialog box without using browse and pressing ok.

The data transfer information is saved in a file with the path
"C:\program files\ibm\client access\transfer.dtf"

Any assistance would be appreciated.

Jim Palmer