ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Use VB macro in excel 2007 to Iseries

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

  • Use VB macro in excel 2007 to Iseries

    Hi All

    does any one help me to coding the VB marco in excel 2007 to access Iseries ,
    everyday i need to use sql/400 to extract data then provide to user.
    I want to creat aform in excel embed sql/400 statement in coding , user will fill parameter they want to extract ,

    If any have the link please post to me.

    Thank you so much.

    Tintin.

  • #2
    Re: Use VB macro in excel 2007 to Iseries

    Please bear with me as I'm not by far a VBA expert...
    This is a short snippet I have at hand. It d/l all the DB/400 file col headings included using a VBA macro.
    • Paste it in excel using Alt+F11
    • Change the red lines below using your own values
    • Name the worksheet "Download"
    • Run the macro (F5 to run or F8 to debug)


    Code:
    Option Explicit
    
    '*** Download
    
    Private Sub DownLoad()
    Dim Con As New ADODB.Connection
    Dim Cmd As New ADODB.Command
    Dim rs As ADODB.Recordset
    
    Dim RowCount As Integer
    Dim colCount As Integer
    Dim text As String
    Dim Number As Long
    Dim val As Variant
    
    Con.Open "provider=IBMDA400;data source=[COLOR="Red"]Your_AS400_Name_Or_IP_Address[/COLOR]"
    Set Cmd.ActiveConnection = Con
    Cmd.CommandText = "SELECT * FROM [COLOR="Red"]MyLib.MyFile[/COLOR]"
    
    Set rs = Nothing
    Set rs = Cmd.Execute()
    Worksheets("Download").Activate
    
    RowCount = 10        ' Start line
    
    For colCount = 0 To rs.Fields.Count - 1  ' Start column = A
      Worksheets("Download").Cells(RowCount, colCount + 1).Value = rs.Fields(colCount).Name
    Next colCount
    
    While Not rs.EOF
        RowCount = RowCount + 1
        For colCount = 0 To rs.Fields.Count - 1
    
        If rs.Fields(colCount).ActualSize = -1 Then
            text = ""
        Else
            val = rs.Fields(colCount).Value
            If VarType(val) = vbNull Then
                text = ""
            Else
                text = val
            End If
        End If
    
        Worksheets("Download").Cells(RowCount, colCount + 1).Value = text
    
        Next colCount
        rs.MoveNext
    Wend
    
    Set rs = Nothing
    Con.Close
    End Sub
    I don't see however how the user will fill the parameters in ?
    Last edited by Mercury; July 21, 2010, 09:57 AM. Reason: english
    Philippe

    Comment


    • #3
      Re: Use VB macro in excel 2007 to Iseries

      Hi TinTin:

      check out this thread:

      http://www.code400.com/forum/showthr...ighlight=excel

      In particular DeadMan's attachments

      Best of Luck
      GLS

      < edit > wrong link < /edit >
      Last edited by GLS400; July 21, 2010, 07:30 AM.
      The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

      Comment


      • #4
        Re: Use VB macro in excel 2007 to Iseries

        HI All

        Thank you for all, i will try to test with the above source,
        @Mercury : User will fill to the sheet excel as @GL400 sent the link about Trail balance or upload process

        Thank youuuuu

        Tintin

        Comment


        • #5
          Re: Use VB macro in excel 2007 to Iseries

          Hey when I try to open the excel file from the DMW's attached zip mentioned above by GLS400, I am told that the folder is password protected with a crypting method not available in my area. What the heck does it mean ? Can you pls help ?
          Philippe

          Comment


          • #6
            Re: Use VB macro in excel 2007 to Iseries

            HI Philippe

            I just collect the information then study , I have not used VB marco so far I just remember in the old company some one made the form in excel file then user type parameter in there do extraction,

            Thank you
            Tintin

            Comment


            • #7
              Re: Use VB macro in excel 2007 to Iseries

              @mercury:

              This is probably way way way out in left field but.....
              I recall reading an article (after 9/11/2001) which said win zip and other related encryption programs could not be exported to foreign countries because of the security risk.....That has since been recinded but if you have an older version of win zip that may be the issue.

              Best of Luck
              GLS
              The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

              Comment


              • #8
                Re: Use VB macro in excel 2007 to Iseries

                The issue isn't winzip but excel. Not sure but the trouble comes likely from the Excel version I'm using (Excel 2000) since the error message comes up when activating the macros.
                Philippe

                Comment


                • #9
                  Re: Use VB macro in excel 2007 to Iseries

                  HI Mercury

                  Could you bug with me , I am not family with VB marco, this is the first time to do
                  The program happen error at the line below with msg : Run-time error 9,subscript out of range
                  so wwhat does it mean ?
                  Code:
                  Worksheets("Download").Activate
                  RowCount = 10        ' Start line
                  Thank you for your kindly help
                  Tintin


                  Originally posted by Mercury View Post
                  Please bear with me as I'm not by far a VBA expert...
                  This is a short snippet I have at hand. It d/l all the DB/400 file col headings included using a VBA macro.
                  • Paste it in excel using Alt+F11
                  • Change the red lines below using your own values
                  • Name the worksheet "Download"
                  • Run the macro (F5 to run or F8 to debug)


                  Code:
                  Option Explicit
                  
                  '*** Download
                  
                  Private Sub DownLoad()
                  Dim Con As New ADODB.Connection
                  Dim Cmd As New ADODB.Command
                  Dim rs As ADODB.Recordset
                  
                  Dim RowCount As Integer
                  Dim colCount As Integer
                  Dim text As String
                  Dim Number As Long
                  Dim val As Variant
                  
                  Con.Open "provider=IBMDA400;data source=[COLOR="Red"]Your_AS400_Name_Or_IP_Address[/COLOR]"
                  Set Cmd.ActiveConnection = Con
                  Cmd.CommandText = "SELECT * FROM [COLOR="Red"]MyLib.MyFile[/COLOR]"
                  
                  Set rs = Nothing
                  Set rs = Cmd.Execute()
                  Worksheets("Download").Activate
                  
                  RowCount = 10        ' Start line
                  
                  For colCount = 0 To rs.Fields.Count - 1  ' Start column = A
                    Worksheets("Download").Cells(RowCount, colCount + 1).Value = rs.Fields(colCount).Name
                  Next colCount
                  
                  While Not rs.EOF
                      RowCount = RowCount + 1
                      For colCount = 0 To rs.Fields.Count - 1
                  
                      If rs.Fields(colCount).ActualSize = -1 Then
                          text = ""
                      Else
                          val = rs.Fields(colCount).Value
                          If VarType(val) = vbNull Then
                              text = ""
                          Else
                              text = val
                          End If
                      End If
                  
                      Worksheets("Download").Cells(RowCount, colCount + 1).Value = text
                  
                      Next colCount
                      rs.MoveNext
                  Wend
                  
                  Set rs = Nothing
                  Con.Close
                  End Sub
                  I don't see however how the user will fill the parameters in ?

                  Comment


                  • #10
                    Re: Use VB macro in excel 2007 to Iseries

                    Have you created a worksheet named "Download" in the Excel file or folder like I said in my latter post ?

                    See attached thumbnails.
                    Attached Files
                    Last edited by Mercury; July 23, 2010, 08:36 AM. Reason: Thumbnails
                    Philippe

                    Comment


                    • #11
                      Re: Use VB macro in excel 2007 to Iseries

                      Hi Philippe

                      Thank you so much, the program run smoothly. It 's fine to me.
                      But somes files downloaded, the data converted to strange character below
                      and if I have link many files the error happen run-time erro '-2147467259(80004005) ? how to slove this problem.
                      does any one have experien with this erro ?

                      䁀䁀䁀䁀䁀Ăሀſ鈁䂟䁀䁀 䃔엣죃훕훓엉헣퓁썀䃖䃄    헥Ä 43452455
                      䁀䁀䁀䁀䁀Ăሀſ瀂䀯䁀䁀 짥쇕쇣䃙훔훣썀�훗쇙짣헖䁀 立   헥Ä 1479935157



                      Many thanks

                      Tintin

                      Comment


                      • #12
                        Re: Use VB macro in excel 2007 to Iseries

                        Tough to say with so few info.
                        What is the CCSID of files with "strange characters" in ? 65535 means do NOT convert to ASCII, data transfered as is, so you may get those "strange characters".
                        Is there any messages associated with the run-time error ?
                        Additionally try to query the Microsoft Knowledge Base
                        Philippe

                        Comment


                        • #13
                          Re: Use VB macro in excel 2007 to Iseries

                          HI Mercury

                          Please help me to understand this case, I have a simple sql as above but the result of cmd.commandText is false ,

                          I do not know why sql is false ? ( pls see file attached)


                          Here is my code , I just change some as the red highlighted :

                          Code:
                          '*** Download
                          
                          Private Sub DownLoad()
                          Dim Con As New ADODB.Connection
                          Dim Cmd As New ADODB.Command
                          Dim rs As ADODB.Recordset
                          
                          Dim RowCount As Integer
                          Dim colCount As Integer
                          Dim text As String
                          Dim Number As Long
                          Dim val As Variant
                          
                          Con.Open "provider=IBMDA400;data source=x.y.x.w"
                          Set Cmd.ActiveConnection = Con
                          Cmd.CommandText = "SELECT *from lib/file "
                          
                          
                          Set rs = Nothing
                          Set rs = Cmd.Execute()
                          Worksheets("Download").Activate
                          
                          RowCount = 10        ' Start line
                          
                          For colCount = 0 To rs.Fields.Count - 1  ' Start column = A
                            Worksheets("Download").Cells(RowCount, colCount + 1).Value = rs.Fields(colCount).Name
                          Next colCount
                          
                          While Not rs.EOF
                              RowCount = RowCount + 1
                              For colCount = 0 To rs.Fields.Count - 1
                          
                              If rs.Fields(colCount).ActualSize = -1 Then
                                  text = ""
                              Else
                                  val = rs.Fields(colCount).Value
                                  If VarType(val) = vbNull Then
                                      text = ""
                                  Else
                                      text = val
                                  End If
                              End If
                          
                              Worksheets("Download").Cells(RowCount, colCount + 1).Value = text
                          
                              Next colCount
                              rs.MoveNext
                          Wend
                          
                          Set rs = Nothing
                          Con.Close
                          End Sub
                          Attached Files

                          Comment


                          • #14
                            Re: Use VB macro in excel 2007 to Iseries

                            You need to insert a space after the asterisk in your select statement. It should read:

                            Code:
                            Cmd.CommandText = "SELECT * from lib/file "
                            "Time passes, but sometimes it beats the <crap> out of you as it goes."

                            Comment


                            • #15
                              Re: Use VB macro in excel 2007 to Iseries

                              Originally posted by littlepd View Post
                              You need to insert a space after the asterisk in your select statement. It should read:

                              Code:
                              Cmd.CommandText = "SELECT * from lib/file "
                              Hi Littlepd

                              I have modified as your instruction but the result as the same previous ,
                              May be , I could not access to data from excel , it's protected ?

                              Tintin

                              Comment

                              Working...
                              X