Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Data Transfer Between AS/400 and Excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Data Transfer Between AS/400 and Excel

    Robert wrote: "...make transfers between the AS/400 and Excel a one step process?" We just FTP space delimited files down and open with Excel. Is that type of file open and automatic conversion enough of a burden to create an Excel file outside of Excel so their first open is a little easier? Ralph

  • #2
    Data Transfer Between AS/400 and Excel

    Robert, If your users can map a drive to the IFS you could use cpytoimpf to create a .csv file on the IFS that your users could then view or drag and drop to their pc's. You could also include meta data if you had the need.

    Comment


    • #3
      Data Transfer Between AS/400 and Excel

      Robert, First off, no need to apologize about the length, it wasn't overly long at all. Downloading data from the AS400 to Excel is simple. It is easily done utilizing the MS Query package. This package is not installed with office using any default install. You must explicitly tell it to install. Once installed you will only need to set up an ODBC data source in order to access your AS400 data. This now allows you to directly download data into the spreadsheet, no intervening table download and import first. Bill "Robert Nipp" wrote in message news:4e6807dd.-1@WebX.WawyahGHajS... Now for the questions. Are macros in the Excel spreadsheets, RTOPCB, RFROMPCB, etc. the best ways to automate transfers these days? Has anyone found a way to completely (or as close as possible) make transfers between the AS/400 and Excel a one step process? Can anyone tell me where I could find David Mayle's code for Uploader? We are on OS/400 V4R5M0 and CAE.

      Comment


      • #4
        Data Transfer Between AS/400 and Excel

        I created an Excel spread sheet that pulls multiple record sets from the AS/400 based on a value entered into a cell. As far as the users are concerned it is an Excel application only. They don't have to know where the data comes from and they don't require any skills other than Excel. The learning curve was somewhat difficult (about 2-3 days) but once you learn a minimum amount of VBA the downloads are very easy to create, change and manipulate from either the AS/400-stored procedure side or the Excel side. I think it is much easier and less cumbersome than MS Query. If you are interested let me know via a reply and I will put together a version that runs against the QIWS/QCUSTCDT file. My technical environment is CAE 4.4, OS/400 V4R5, Office 2000, MS ADO 2.5. I haven't had a need for upload so I can't offer any advice on that.

        Comment


        • #5
          Data Transfer Between AS/400 and Excel

          Let me start by apologizing for the size of this post. We have recently started trying to automate most of our file transfers between the AS/400 and the PCs. I especially need help transferring data between the AS/400 and Excel spreadsheets. I followed the thread that David Abramowitz started last week and tried some of the suggestions given to him. I tried to use RTOPCB in a macro to automate the transfer of data from the AS/400 to an Excel spreadsheet. When I run the macro, I get the message: "Download to display is not supported in batch mode". A few months ago I bought and scanned Brian Singleton's book "The OS/400 and Microsoft Office 2000 Integration Handbook" (Second edition). He presents a neat macro that uses SQL to transfer data from an AS/400 to Excel, but to this point I have not been able to get it to work. That's probably due to my inexperience with VBA. The book also contains a section ,written by David Mayle, that talks about automatically uploading data from Excel to the AS/400. David calls it Uploader. I tried to find the code for Uploader on the Midrange Computing web site as the book suggested, but I could not find it. Now for the questions. Are macros in the Excel spreadsheets, RTOPCB, RFROMPCB, etc. the best ways to automate transfers these days? Has anyone found a way to completely (or as close as possible) make transfers between the AS/400 and Excel a one step process? Can anyone tell me where I could find David Mayle's code for Uploader? We are on OS/400 V4R5M0 and CAE. All suggestions will be appreciated and hopefully future posts will not be as lengthy.

          Comment


          • #6
            Data Transfer Between AS/400 and Excel

            Hello Mark, How did you do the extraction from the AS/400 into excel only by the value of a cell. I have cells A5....A10 and I will be entering a part number; how do I make the description to that part number appear on the cell next to it extracting the information from the AS/400 Thanks Jaime If you would like to see my forms, please let me know.

            Comment


            • #7
              Data Transfer Between AS/400 and Excel

              Jaime, > I have cells A5....A10 and I will be entering a part number; how do I make the description to that part number appear on the cell next to it extracting the information from the AS/400 What you are talking about can be handled via a parameterized query. MS Query is an add-on to the Office suite. For most versions, by default it is not loaded, it must be loaded by rerunning setup. You create the query by using Data/External Data/Create New Query. See my other note to you about other requirements. Bill

              Comment

              Working...
              X