Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

TechTip: Automatically Load Data into Excel

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

  • TechTip: Automatically Load Data into Excel

    ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
    ** This thread discusses the Content article: TechTip: Automatically Load Data into Excel0

  • #2
    TechTip: Automatically Load Data into Excel

    ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
    Numbers came dowm perfect, but all text except the headings are a combination of boxes, Chinese, and "@" signs. Kind of looks like the CCSID box has not been checked, but verified the ODBC and it should be right. Any suggestions to making this work? The column headings came out perfect also....

    Comment


    • #3
      TechTip: Automatically Load Data into Excel

      ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
      I was able to get it to start downloading the data, but when the rowCount reaches 32,767, an overflow error occurs. I know that Excel sheets allow 65,534 rows. I was able to stop this error by changing rowCount to a Long variable type.

      Comment


      • #4
        TechTip: Automatically Load Data into Excel

        ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
        I havn't had that problem except in some situations where the CCSID 65535 needed to be converted. It sounds like you already have that covered. Does the file specify any particular character sets? Can you run the DSFFD command against the file? What CCSID is assigned?

        Comment


        • #5
          TechTip: Automatically Load Data into Excel

          ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
          Did a dspffd and found the text fields that came down correctly have a character id of 37. The fields not converting are id 65535. We have checked the ODBC source for the AS400 and it has the option to convert checked under the translation tab. is there somehwere else we need to enable the translation? Also, how does character id 37 get assigned? Included some of the info from dspffd:
          Code

          Comment


          • #6
            TechTip: Automatically Load Data into Excel

            ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
            Kevin, Thank you for share your code. but I get this message when I run the macro. compiler error, invalid outside procedure on, "provider=IBMDA400;data source=as400ipaddress;USER ID=myuserid;PASSWORD=mypwd;" Any ideas? Thank you in advance..

            Comment


            • #7
              TechTip: Automatically Load Data into Excel

              ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
              Have you configured your ODBC Data source in the Control Panel? In my case I configured my ODBC data source to point to our AS400 and gate the data source a name. It's that name that I used for my data source in the code. I didn't use the IP address because it is defined in the ODBC Data source.

              Comment


              • #8
                TechTip: Automatically Load Data into Excel

                ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
                I am getting the message Provider not found. Any help would be greatly appreciated.

                Comment


                • #9
                  TechTip: Automatically Load Data into Excel

                  ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
                  collette wrote: > I am getting the message Provider not found. Any help would be > greatly appreciated. I'm just guessing here, but is the Client Access ODBC driver loaded on the PC? Bill

                  Comment


                  • #10
                    TechTip: Automatically Load Data into Excel

                    ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
                    Use selective setup in Client Access to verify that the OLE DB Provider is installed. If that doesn't work - get back with me off the forum and I'll help you out. Take care, Kevin Kevin.forsythe@dmcconsulting.com

                    Comment


                    • #11
                      TechTip: Automatically Load Data into Excel

                      ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
                      The 65535 is the default for hex and in my experience, is typically seen in old "flat" program described files. Can you show me the raw format of the data in that column? (try DSPPFM)

                      Comment


                      • #12
                        TechTip: Automatically Load Data into Excel

                        ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
                        Automation Error Specified Procedure cannot be found The execution stops at the line which the following command resides Con.Open "provider=IBMDA400;data source=XXX.XX.X.X;USER ID=*********;PASSWORD=********;" Any suggestion for what to do? Thanks in advance. Abdul

                        Comment


                        • #13
                          TechTip: Automatically Load Data into Excel

                          ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
                          Does your statement look like the one below? If so then double check the references under the Tools pull down menu of the VB editor, and include the ADO Objects. Make sure you are up to date on MDAC from Microsoft. Make sure that you have the OLEDB drivers installed through Client Access - If you have all that and are still getting this error - Let me know. You can email me directly at kevin.forsythe@dmcconsulting.com I'll send you a copy of my working example that you can play with - or you can send me yours, and I'll take a look. Good luck! Kevin
                          Code

                          Comment


                          • #14
                            TechTip: Automatically Load Data into Excel

                            ** This thread discusses the article: TechTip: Automatically Load Data into Excel **
                            Does your statement look like the one below? If so then double check the references under the Tools pull down menu of the VB editor, and include the ADO Objects. Make sure you are up to date on MDAC from Microsoft. Make sure that you have the OLEDB drivers installed through Client Access - If you have all that and are still getting this error - Let me know. You can email me directly at kevin.forsythe@dmcconsulting.com I'll send you a copy of my working example that you can play with - or you can send me yours, and I'll take a look. Good luck! Kevin
                            Code

                            Comment

                            Working...
                            X