Solved

Refreshing Queries of ISERIES/AS400 Through Excel Macro

Posted on 2009-07-16
19
2,447 Views
Last Modified: 2013-12-06
Hi,

I have the following code, Basically what i'm trying to do i have already a query defined which generates my output file in iseries/as400 which i further go ahead and import into excel and run my reports.

I'm Clear about how to do the coding for the second part that is importing data from ISERIES/AS400 into excel, However im struggleing in the first part of refreshing the query with already set parameters which i have defined.

So now lets say my library name is abc where the query is saved and it's called LIVECT, However when i use the following code, It gives me an error saying -

Run-time error '-2147217900 (80040e14)'
[IBM] [ISeries Access ODBC DRIVER][DB2 UDB]SQL0104 - TOKEN ABC was not valid. Valid Tokens: ( INTO USING.

Also i'm not sure whether this is the right code to refresh the query since i found the code on the net only.

All i need is the command to refresh it, rest i can take it forward from there.

The line on which it throws me above error is this one:-

"cmnd.Execute Rcds"

Saurabh
Sub rrefresh()

CONN.Open "DSN=IBMDA400;UID=user;PWD=password"

MsgBox CONN.State
 

Set cmnd.ActiveConnection = CONN

cmnd.CommandText = "{CALL " + "RUNQRY ABC/LIVECT" + ".RMTSTRT}"

cmnd.Execute Rcds

CONN.Close

       Set CONN = Nothing

       Set cmnd = Nothing

End Sub

Open in new window

0
Comment
Question by:Saurabh Singh Teotia
  • 10
  • 8
19 Comments
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
Comment Utility
You can use this AS400RunCommand function and pass it the user name, pass word, system (database)  name, and the command you want to run.
-Chuck
'==========================================================

'  requires a reference to:

'    Microsoft ActiveX Data Objects 2.5(or later) Library

'==========================================================

Public Function AS400RunCommand( _

  ByVal strUserName As String, _

  ByVal strPassword As String, _

  ByVal strSystem As String, _

  ByVal strCmd As String) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400RunCommand = False

    Dim AS400Conn As New ADODB.Connection

    Dim AS400Pgm As New ADODB.Command

    ' open a connection to the AS400

    AS400Conn.Open "Provider=IBMDA400;Data Source=" & strSystem  & ";User ID=" & _

        strUserName & ";Password=" & strPassword & ";"

    Set AS400Pgm.ActiveConnection = AS400Conn

    ' create the command text

    AS400Pgm.CommandText = "CALL QSYS.QCMDEXC('" & strCmd & "', " & _

        Format(Len(strCmd), "0000000000") & ".00000)"

    ' execute the command

    AS400Pgm.Execute

    ' set the final state of the function

    AS400RunCommand = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400RunCommand Failed"

    Resume ExitHere

End Function

Open in new window

0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Hi Chuck,
Thanks for quick response, Consider me a noob when comes to AS400 as im still learning it, so basically in the function that you given to me, i understand the username and password peace. System will be my database name which is -->"IBMDA400" or my library name which is "abc".
Also for executing of command which is basically i want to run my query which i already have setup and stored in library. So you need to help me with the syntax for the same as i got no idea about how to write that in vb.
Saurabh
0
 
LVL 16

Assisted Solution

by:Chuck Wood
Chuck Wood earned 500 total points
Comment Utility
Saurabh,
IBMDA400 is the Provider or the way that we connect to the AS/400.
I am sorry, I should not have said the system is the database. The system is what IBM folks call an LPAR and SQL Server folks call a server.
abc is what Access and SQL Server folks call a database and IBM folks call a library.
If you want to run the LIVEACT query int the ABC database, you can use the code in the snippet.
-Chuck
AS400RunCommand "username", "password", "system", "RUNQRY ABC/LIVECT"

Open in new window

0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Chuck,
I modified your code a bit since it was giving me an error in connecting, And now im able to connect, However it throws a weird error at this line:-
AS400Pgm.Execute
Error which it gave to me:-
Run-time error '-2147467259 (80004005)':
[IBM][iSeries Access ODBC Driver][DB2 UDB]QRY5058 - File LIVECT in abc was not replaced.
The code what I'm using is this.
Saurabh

Public Function AS400RunCommand( _

  ByVal strUserName As String, _

  ByVal strPassword As String, _

  ByVal strCmd As String) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400RunCommand = False

    Dim AS400Conn As New ADODB.Connection

    Dim AS400Pgm As New ADODB.Command

    ' open a connection to the AS400

    AS400Conn.Open "DSN=IBMDA400;UID=" & strUserName & ";PWD=" & strPassword
 

    Set AS400Pgm.ActiveConnection = AS400Conn

    ' create the command text

    AS400Pgm.CommandText = "CALL QSYS.QCMDEXC('" & strCmd & "', " & _

        Format(Len(strCmd), "0000000000") & ".00000)"

    ' execute the command

    AS400Pgm.Execute

    ' set the final state of the function

    AS400RunCommand = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & Err.Number & vbNewLine & _

        "Description:" & Err.Description & vbNewLine & _

        "From:" & Err.Source, vbExclamation, _

        "AS400RunCommand Failed"

    Resume ExitHere

End Function

 

Sub abc()

AS400RunCommand "USERNAME", "PASSWORD", "RUNQRY ABC/LIVECT"
 

End Sub

Open in new window

0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Also just to mention that in my original query that i created i'm generating a file and saving it in the library which im further importing by using of ODBC Connections, Has that got to do anything with this error?
 
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Is LIVECT the query name or the name of the file the query creates?
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Both, The file name is also called LIVECT and the query as well called the LIVECT, However if you want i can create file with different name as that is totally dependent upon me.
Saurabh
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
I don't think that matters. What happens if you run the query manually?
-Chuck
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Chuck,
Give me few minutes, I'm testing it, Will post back my results, I'm able to succesfully run the query.
Saurabh...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Chuck,
You rock, My original defination was messy, I corrected and i'm able to run it now, The code works excellent, However, I just need your help in understanding this line.
AS400Pgm.CommandText = "CALL QSYS.QCMDEXC('" & strCmd & "', " & _
        Format(Len(strCmd), "0000000000") & ".00000)"

What are the parameters of this line and how do you define in?
Saurabh...
0
 
LVL 16

Assisted Solution

by:Chuck Wood
Chuck Wood earned 500 total points
Comment Utility
Saurabh,
I am not real sure, I got this from another solution. I think that QSYS is a system library in the DB2 database management program on the AS/400 and that QCMDEXC is a command (CMD) execution (EXC) program in that library. The rest of it must be formatiing required by the QCMDEXC program. That is my best guess, I am really not an IBM guy. I just have to get data out of our AS/400 to use in Access and Excel so I find little nuggets like this and adapt them to use in my VBA modules.
-Chuck
0
 
LVL 59

Author Closing Comment

by:Saurabh Singh Teotia
Comment Utility
Chuck,
Appreciate it, You don't know what you have done for me, It has opened a new pandora box for me to work on. Also by any chance if you have idea about how to define query through macro, Like in this case i'm refering to my query which is predefined. I can go ahead and ask a new question about it.

Again Thanks for all your help, I OWE you one. Anytime if i can do anything for you do let me know i will be more then happy.

Saurabh
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Saurabh,
You are very welcome. I do not have a way to define a DB2 query through VBA but I have recently discovered a way to run the equivalent to a query using IBM Client Access/iSeries Access for Windows and importing the result into Access. I intend to port this solution to Excel as soon as I get the time. Do you use IBM Client Access or iSeries Client Access to do Data Transfers?
-Chuck
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Chuck,
Neither of those, We have established ODBC driver and since i have fair knowledge of excel so post this i use the ODBC driver to get the file that i generated in the ISERIES into my excel and since i'm using excel 2007 so it easily supports data which has rows more then 65536.
If you need my help in setting up that, Let me know i can help you with that.
Saurabh
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Saurabh,
I having been using ADODB and the IBMDA400 Provider for awhile now and found that it is simpler and often faster than ODBC, which I used for years before finding the IBMDA400 Provider. I used DSN before ODBC but it is such a pain to set up on user's computers, I transitioned to ODBC and now ADODB with IBMDA400. Here is a VBA module I use for interacting with the AS/400. The AS400GetData, AS400GetDataAndFields, AS400GetDataFieldsTypes, AS400RunCommand, and AS400UpdateData (for updates and inserts) are the most useful functions. You can use the AS400TestConnection to test if you have all the right elements (user name, password, and system) to connect to your AS/400 and it will evan print out the properties of your connection in the Immediate window.
Good luck with your project.
-Chuck
Attribute VB_Name = "basAS400"

Option Explicit

'==========================================================

' version 1.0.3  5/7/2009

'==========================================================

'  requires a reference to:

'    Microsoft ActiveX Data Objects 2.5(or later) Library

'==========================================================

Public Function AS400ConvertTypesToAccess(ByRef avarTypes() As Variant, _

  avarData() As Variant) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400ConvertTypesToAccess = False

    Dim lngRow As Long

    ' loop through the rows

    For lngRow = 0 To UBound(avarTypes, 1)

        ' if the type is formated as an AS/400 number,

        If avarTypes(lngRow) = "131" Then

            ' if the number is larger than an integer

            If CDbl(avarData(lngRow, 0)) > 32767 Then

                ' change the type to double

                avarTypes(lngRow) = "DOUBLE"

            ElseIf InStr(1, CStr(avarData(lngRow, 0)), ".") > 0 Then

                ' change the type to double

                avarTypes(lngRow) = "DOUBLE"

            Else

                ' change the type to number

                avarTypes(lngRow) = "NUMBER"

            End If

        ' else, if the type is formatted as an AS/400 date,

        ElseIf avarTypes(lngRow) = "133" Or avarTypes(lngRow) = "134" Then

            ' change the type to date

            avarTypes(lngRow) = "DATE"

        ' else, if the type is formatted as AS/400 text,

        ElseIf avarTypes(lngRow) = "129" Then

            ' change the type to text

            avarTypes(lngRow) = "TEXT"

        End If

    Next lngRow

    ' set the final state of the function

    AS400ConvertTypesToAccess = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400ConvertTypesToAccess Failed"

    Resume ExitHere

End Function
 

Public Function AS400CreateMemberAlias(ByVal strUserName As String, ByVal strPassword As String, _

  ByVal strLibrary As String, ByVal strFileTable As String, ByVal strMember As String, _

  ByVal strAlias As String, ByVal strSystem As String) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400CreateMemberAlias = False

    ' if the library has a dot in the name,

    If InStr(1, strLibrary, ".") > 0 Then

        ' strip any double quotes

        strLibrary = Replace(strLibrary, """", "")

        ' add double quotes around it

        strLibrary = """" & strLibrary & """"

    End If

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection, strSQL As String

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' create the alias

    strSQL = "CREATE ALIAS " & strLibrary & "." & strAlias & " FOR " & _

        strLibrary & "." & strFileTable & "(" & strMember & ")"

    cnn.Execute strSQL

    ' set the final state of the function

    AS400CreateMemberAlias = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400CreateMemberAlias Failed"

    Resume ExitHere

End Function
 

Public Function AS400CreateTable(ByVal strTableName As String, ByVal strColumns As String, _

  ByVal strUserName As String, ByVal strPassword As String, ByVal strLibrary As String, _

  ByVal strSystem As String) As Boolean

'===== Sample Code ========================================================

'    Dim blnSuccess As Boolean

'    blnSuccess = AS400CreateTable("SVCTYPE", _

'        "SVCODE CHARACTER(3), SVCDESC CHARACTER(30), CUSTOMER# NUMERIC(7)", _

'        "USERID", "PASSWORD", "PACIFIC")

'    If blnSuccess Then

'        MsgBox "The table was created", vbInformation, "Success"

'    Else

'        MsgBox "The table was NOT created", vbExclamation, "Failed"

'    End If

'==========================================================================

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400CreateTable = False

    ' if the table name is too long for WRKQRY,

    If Len(strTableName) > 10 Then

        ' warn the user and exit

        MsgBox "The table name " & strTableName & _

            " is too long (more than 10 characters)", _

            vbExclamation, "Cannot Create Table"

        GoTo ExitHere

    End If

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' set up the create table sql statement

    Dim strSQL As String

    strSQL = "CREATE TABLE """ & strLibrary & """." & strTableName & "(" & strColumns & ")"

    ' execute the create table

    cnn.Execute strSQL

    ' set the final state of the function

    AS400CreateTable = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400CreateTable Failed"

    Resume ExitHere

End Function
 

Public Function AS400DeleteMemberAlias(ByVal strUserName As String, ByVal strPassword As String, _

  ByVal strLibrary As String, ByVal strAlias As String, ByVal strSystem As String) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400DeleteMemberAlias = False

    ' if the library has a dot in the name,

    If InStr(1, strLibrary, ".") > 0 Then

        ' strip any double quotes

        strLibrary = Replace(strLibrary, """", "")

        ' add double quotes around it

        strLibrary = """" & strLibrary & """"

    End If

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection, strSQL As String

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' delete the alias

    strSQL = "DROP ALIAS " & strLibrary & """." & strAlias

    cnn.Execute strSQL

    ' set the final state of the function

    AS400DeleteMemberAlias = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400DeleteMemberAlias Failed"

    Resume ExitHere

End Function
 

Public Function AS400GetData(ByVal strUserName As String, _

  ByVal strPassword As String, ByVal strSQL As String, _

  ByVal strSystem As String, ByRef avarData() As Variant) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400GetData = False

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' using a recordset, get the data and field names

    Dim rst As New ADODB.Recordset

    With rst

        ' open the recordset

        .Open strSQL, cnn

        ' if data was found,

        If Not .EOF Then

            ' get the data

            avarData = .GetRows

            ' eliminate leading and trailing spaces in the data elements

            AS400TrimData avarData

            ' indicate data was retrieved

            AS400GetData = True

        End If

        .Close

    End With

    ' close the connection

    cnn.Close

    ' clean up

    Set rst = Nothing

    Set cnn = Nothing

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400GetData Failed"

    Resume ExitHere

End Function
 

Public Function AS400GetDataAndFields(ByVal strUserName As String, _

  ByVal strPassword As String, ByVal strSQL As String, _

  ByVal strSystem As String, ByRef avarData() As Variant, _

  avarFields() As Variant) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400GetDataAndFields = False

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' using a recordset, get the data and field names

    Dim rst As New ADODB.Recordset, intCol As Integer

    With rst

        ' open the recordset

        .Open strSQL, cnn

        ' if data was found,

        If Not .EOF Then

            ' get the data

            avarData = .GetRows

            ' eliminate leading and trailing spaces in the data elements

            AS400TrimData avarData

            ' set the names array to the number of fields

            ReDim avarFields(UBound(avarData, 1))

            ' loop through the fields

            For intCol = 0 To UBound(avarData, 1)

                ' set the field name in the array to the name of this field

                avarFields(intCol) = .Fields(intCol).Name

            Next intCol

            ' indicate data was retrieved

            AS400GetDataAndFields = True

        End If

        .Close

    End With

    ' close the connection

    cnn.Close

    ' clean up

    Set rst = Nothing

    Set cnn = Nothing

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400GetDataAndFields Failed"

    Resume ExitHere

End Function
 

Public Function AS400GetDataFieldsTypes(ByVal strUserName As String, _

  ByVal strPassword As String, ByVal strSQL As String, _

  ByVal strSystem As String, ByRef avarData() As Variant, _

  avarFields() As Variant, avarTypes() As Variant) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400GetDataFieldsTypes = False

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' using a recordset, get the data and field names

    Dim rst As New ADODB.Recordset, intCol As Integer

    With rst

        ' open the recordset

        .Open strSQL, cnn

        ' if data was found,

        If Not .EOF Then

            ' get the data

            avarData = .GetRows

            ' eliminate leading and trailing spaces in the data elements

            AS400TrimData avarData

            ' set the field names array to the number of fields

            ReDim avarFields(UBound(avarData, 1))

            ' set the field types array to the number of fields

            ReDim avarTypes(UBound(avarData, 1))

            ' loop through the fields

            For intCol = 0 To UBound(avarData, 1)

                ' set the field name in the array to the name of this field

                avarFields(intCol) = .Fields(intCol).Name

                ' set the field type in the array to the type of this field

                avarTypes(intCol) = .Fields(intCol).Type

            Next intCol

            ' indicate data was retrieved

            AS400GetDataFieldsTypes = True

        End If

        .Close

    End With

    ' close the connection

    cnn.Close

    ' clean up

    Set rst = Nothing

    Set cnn = Nothing

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400GetDataFieldsTypes Failed"

    Resume ExitHere

End Function
 

Public Function AS400RunCommand(ByVal strUserName As String, _

  ByVal strPassword As String, _

  ByVal strCmd As String) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400RunCommand = False

    Dim AS400Conn As New ADODB.Connection

    Dim AS400Pgm As New ADODB.Command

    ' open a connection to the AS400

    AS400Conn.Open "Provider=IBMDA400;Data Source=PACIFIC;User ID=" & _

        strUserName & ";Password=" & strPassword & ";"

    Set AS400Pgm.ActiveConnection = AS400Conn

    ' create the command text

    AS400Pgm.CommandText = "CALL QSYS.QCMDEXC('" & strCmd & "', " & _

        Format(Len(strCmd), "0000000000") & ".00000)"

    ' execute the command

    AS400Pgm.Execute

    ' set the final state of the function

    AS400RunCommand = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400RunCommand Failed"

    Resume ExitHere

End Function
 

Public Function AS400UpdateData(ByVal strSQL As String, _

  ByVal strUserName As String, ByVal strPassword As String, _

  ByVal strSystem As String) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400UpdateData = False

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' execute the update

    cnn.Execute strSQL

    ' clean up

    Set cnn = Nothing

    ' set the final state of the function

    AS400UpdateData = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400UpdateData Failed"

    Resume ExitHere

End Function
 

Public Function AS400TestConnection(ByVal strUserName As String, _

  ByVal strPassword As String, ByVal strSystem As String) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400TestConnection = False

    Dim pro As ADODB.Property, strProperties As String

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' get each property and add it to the string

    For Each pro In cnn.Properties

      strProperties = strProperties & vbCr & _

        pro.Name & ": " & pro.Value

    Next

    ' print the properties string

    Debug.Print strProperties

    ' clean up

    cnn.Close

    Set pro = Nothing

    Set cnn = Nothing

    ' set the final state of the function

    AS400TestConnection = True

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400TestConnection Failed"

    Resume ExitHere

End Function
 

Private Sub AS400TrimData(ByRef avarData() As Variant)

' set up error handling

On Error GoTo ErrorHandler

    Dim lngRow As Long, intCol As Integer

    ' loop through the array rows

    For lngRow = 0 To UBound(avarData, 2)

        ' loop through the array columns

        For intCol = 0 To UBound(avarData, 1)

            ' trim this data array element

            avarData(intCol, lngRow) = Trim(avarData(intCol, lngRow))

        Next intCol

    Next lngRow

ExitHere:

    Exit Sub

ErrorHandler:

    MsgBox "Error:" & err.Number & vbNewLine & _

        "Description:" & err.Description & vbNewLine & _

        "From:" & err.Source, vbExclamation, _

        "AS400TrimData Failed"

    Resume ExitHere

End Sub

Open in new window

0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Chuck,
Ahh thanks for all the information now this again going to help on what i'm working on, Basically im working on setting up real-time reports in excel completely automated with minimum manual intervention and i think i can do it completely now.
Saurabh
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
Again, Thanks for all your help, You made my day.
Saurabh
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Saurabh,
You are welcome.
-Chuck
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Saurabh.

Your RUNQRY command is failing.  Looks like you are using defaults for the output file (you defined it in WRKQRY).  WRKQRY, Select Output Type and Output Form, and look at the options:  you probably have "New file" selected.  Hence the error.

Several ways to fix:

Delete the output file before you run the query, and make sure the query creates a new outfile each time.
 On your RUNQRY command by specifying the OUTFILE parameter with the *RPLFILE option.
In your query definition, change your output option to "Replace file"

- Gary Patterson

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now