OK, My eyes are getting tired from reading through EE. It's very informative with suggestions but I need a step by step to connect to our AS400.
I did get the DATA Menu>"Get External Data via ODBC" to work fine but it's not able to be used in a shared enviroment as this option is grey out Hence, the only option is "Transfer Data from ISeries". I'm looking for a macro that will log me in and refresh every 7 minutes.
Our server is: 10.1.1.2
File Library is HS#LIBR
File is HSPBEDFLOW
User ID: User
Password: Password
This is the manual way and I did try to record and it did not give me any lines to work with:
Go into Data>Data Transfer from ISeries. It asks for Transfer Request. I chose create new file press OK.
It gives me teh ISeries Address: 10.1.1.2. I press next and teh next windo comes up asking for Lib/File: HS#LIBR/HSPDEDFLOW then pres next. I then get Customize File Transfer..I just press next. In the next window it ask to save reuest to file. I press Finish without doing anything in the screen. It then ask to signon to ISeries with User ID and Password..put in the info and I get the data in A1.
Could one of the expertise assist please. I'm relatively new.
Regards,
Paul
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Set up the querytable as you outlined. Once you have it on the worksheet, switch to the VBEditor, then in the immediate window (Ctrl+G to open that if it's not visible) and type:
?Activesheet.Querytables(1
and press enter. This should output the connection string more or less as you need to use it with ADO.
Regards,
Rory
Hi
I use several methods.
1 - ADO to access the AS/400 and download.
2 - Create a Client access data transfer and load it from there.
It depends on the amount of info, the speed of the line and what you want to do with it.
here is the ado code
Sub DWNLD()
Set conn = CreateObject("adodb.connec
Set rcd = CreateObject("adodb.record
cnnstr = "DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=999.999.9
conn.Open cnnstr
Set rcd.ActiveConnection = conn
sqlstm = "SELECT * FROM LIBRARY.FILE"
rcd.Open sqlstm
Set flds = rcd.Fields
For i = 0 To flds.Count - 1
Cells(1, i + 1).Value = flds(i).Name
Next i
j = 2
Do While Not (rcd.EOF)
For i = 0 To flds.Count - 1
Cells(j, i + 1).Value = flds(i).Value
Next i
rcd.movenext
j = j + 1
Loop
Set rcd = Nothing
Set conn=Nothing
End Sub
Hi Daveslater,
I've tried your coding this a.m.
I gethe following mesage: Run Time Error -2147217900 (80040e14)
[IBM][Iseries Access ODBC Driver][DB2 UDB]SQL0104- Token, was not valid. Valid tokens: , FROM INTO.
Not sure what this means??!!
PS how would you go about the second option as a trial: the Client Access Data Transfer? What does that entails?
Hi Rory,
I tried you method and itgave me this in the window: ODBC; DSN-ISeries
Hi
for the timer look at this question.
http://www.experts-exchang
I could copy the code but then the person who wrote it would not get the credit.
Dave
Hi Rory,
I'm still interested in anything at this time. Thus far, yes daves coding takes awhile but it does the job even without the assigned worksheet. However, if it might be faster, I will need input from you as you already seen what I'm trying to do. I am not familiar with VB and I'm just cutting and pasting as well as doing research along the ways. As I mention first, there's a lot of info but without being able to apply it, it's a learning curve for me.
Dave,
The reason I asked for the specific worksheet is that, if I was on any sheet that is in the fore front, the data is transferred onto that sheet and "messes it up". Hence, I needed to "direct" the download of data into eg. sheet19.
I did find a way to refresh the data Dave. Thanks!
I think I saw another code that will only allow one user to update vs. other users under the shared environment. I believe that is the next step. I did post another question if anyone is interested. Thanks to all and Merry Christmas!!!!
This is teh code by Kusi 25 That I've found..I hope it will work. Probably give it a try on Thursday.
"To prevent from running the queries on all more than one machine, you could add little vba code to check the computername or username before running the queries
Create a module (not a class module):
Option Compare Database
Option Explicit
Public Declare Function GetComputerName _
Lib "Kernel32" Alias "GetComputerNameA" _
(ByVal sBuffer As String, lBufferSize As Long) As Long
Public Function GetComputer() As String
Dim sComputerName As String
Dim lSize As Long
Dim lLength As Long
sComputerName = String(16, " ")
lSize = Len(sComputerName)
lLength = GetComputerName(sComputerN
GetComputer = Left(sComputerName, lSize)
End Function
in your sub add the following code:
If GetComputer <> "MycomputerName" Then
Else
'Your query code here
End If"
credit to: Kusi
Thank Rory,
I've give it a try on Monday. Since I have you,
The module basroomchange or something similar... different computer that I'm using. At this time, it's not "updating the code PM,PF, M, or F when using Dave's code. It works on teh original version that you assisted still. How did you "assign" that module to kick in after refresh???
Thanks in advance!!
Paul
Not sure what you mean. The original code I dealt with was, as I recall, a Querytable and this is not, hence there is no refresh event to use.
As far as writing your data to the sheet goes, it is probably quicker to use:
Worksheets(1).Range("A1").
rather than writing out row by row or trying to create an array (unless you have a row limit to worry about)
Regards,
Rory
Hi
I was doing some surfing of the holidays.
This is some free software that will allow you to what you want.
http://www.enhanceddatasys
I will delete the other comments so people do not get confused.
Dave
Business Accounts
Answer for Membership
by: jocasio123Posted on 2007-12-21 at 12:07:36ID: 20516032
I'm not sure how to do that as a macro, but I think Once you have everything in A1, you can Select Data->Text to Columns and separate the columns that way.