AS400 transfer diredtly to xl sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RobWN - 25 Jan 2008 18:31 GMT

I use the addin to transfer records directly to a worksheet.
Is there anyway to automate this procedure?

Currently my macros will set up a sheet and advise the user of the directory where the DTF is
stored.
I'd rather have this as an automatic procedure i.e.. upon calling the routine for the download
have the DTF execute automatically.
I've tried creating a BAT file but the output is display only vs. loading direct to the sheet.
.

Signature

Regards
Rob

Reply to this Message

Nick Hodge - 26 Jan 2008 11:20 GMT

Rob

In Excel I do this all the time, but never use a macro to get the data. I
simply get the data from Excel using Data>Import External Data>New database
query. Using the client Access ODBC driver I set up the data feed and then
pass the model to the team with a right click>refresh instruction. If there
is any post processing to be done I set up either a button or a change
event.

It is in fact rare that we use data table, we get it to pivot tables, this
removes many of the further needs for post-processing. granted, we needed to
train users, but they love the 'what-if' solutions in a pivot table.

May not answer the question, but hopefully gives some food for thought

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
web: www.excelusergroup.org

> I use the addin to transfer records directly to a worksheet.
> Is there anyway to automate this procedure?
[quoted text clipped - 6 lines]
> direct to the sheet.
> .

Reply to this Message

RobWN - 26 Jan 2008 15:26 GMT

Thanks Nick;

While it certainly will give me food for thought I'm looking for something simpler (for me at
least).

The download of the data is one step in a multi step process.

I have a button that updates the DTF "Where" parameters and then simply creates a new sheet in
the workbook and advises the user where the DTF is located. The user then uses the iSeries
Navigator transfer function "Transfer Data from iSeries" to load the required records to the
sheet and carries on.
While this process works well I was hoping for some "magic" to relieve the user of navigating to
the DTF.

I would not profess to being an expert and after Googling my eyes out I come close but can't
seem to find what I'm after (probably not asking the right question!).

Again, thanks.

Signature

Regards
Rob

> Rob
>
[quoted text clipped - 20 lines]
>> sheet.
>> .

Reply to this Message

Nick Hodge - 26 Jan 2008 20:56 GMT

Rob

Without wishing to send this off in a different direction, you can set the
'Where' clauses on a worksheet, so the users just change a date range for
example and the data just changes.

I have a post here that may help describe this

http://excelusergroup.org/blogs/nickhodge/archive/2008/01/24/importing-data-with
-parameters-odbc.aspx

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk

> Thanks Nick;
>
[quoted text clipped - 42 lines]
>>> loading direct to the sheet.
>>> .

Reply to this Message

RobWN - 27 Jan 2008 03:49 GMT

Nick;
Your not sending this off in a different direction but, rather, making me think.

I have to note that while I'm not new at using VBA I am a neophyte at using the iSeries and
importing records as our organization just started using this technology. So I am in a learning
phase right now.

From what I can gather by surfing around, as well as your link, is that I should be able to set
up my own access to the iSeries(?).

I'd be interested in a "snippet" of code that would get me started-everything I've read is a
little beyond me, at this point..

Would you happen to have any links to a "Importing data into Excel from a DB2 database on an
iSeries using VBA - for dummies"?

Anyway, thanks for the interest.

Signature

Regards
Rob

> Rob
>
[quoted text clipped - 47 lines]
>>>> sheet.
>>>> .

Reply to this Message

Nick Hodge - 27 Jan 2008 16:32 GMT

Rob

Why would you want VBA? You can do this just using the menu options. I would
record yourself doing it and if you are lucky, (I'm not able to test
currently as I am remote), you'll get most of what you need, all you require
is the server name you use to access the iSeries. I'm in the UK and our
iSeries is in the US so we use an address like an internet URL.

I would also take a look at IBM's site as they are pretty good in this
regard, but as I say, be cautious about whether you actually need VBA

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk

> Nick;
> Your not sending this off in a different direction but, rather, making me
[quoted text clipped - 70 lines]
>>>>> loading direct to the sheet.
>>>>> .

Reply to this Message

RobWN - 27 Jan 2008 16:53 GMT

Nick;

As I noted, the downloading of the iSeries data is just one step in the process, so yes, I need
VBA.
All I'm looking for is a method to automate the DL step without the user having to locate and
execute the DTF.

I'll keep looking/playing.

Thanks for the help.

Signature

Regards
Rob

> Rob
>
[quoted text clipped - 73 lines]
>>>>>> sheet.
>>>>>> .

Reply to this Message