VBSCRIPT AND EXCEL MACRO

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

VBSCRIPT AND EXCEL MACRO

Logged in as: Guest

arrSession:exec spGetSession 2,2,58484

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> VBSCRIPT AND EXCEL MACRO
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1] 2 next > >>

Message

<< Older Topic Newer Topic >>

VBSCRIPT AND EXCEL MACRO - 3/27/2008 10:14:36 PM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

Hi all
I am trying to open an excel sheet through vbscript and run a macro written in it.
I have been successfull in opening the excel sheet and running the macro but i do get a runtime script error.
Here is the code that i have written

sub openexcel()
     Dim obj
     Set obj = CreateObject("Excel.application")
     obj.Workbooks.Open "C:/Final templates/MyExcel.xls"
     obj.visible=true
   On Error Resume Next
     obj.Run "TEST" 'runs the macro
     msgbox Err.Number & "         " & Err.Description
end sub

the error i get is unknown runtime error and error number is 40040

Another question
Can i return a value to the vbscript through the macro..?

Thanks

Post #: 1

RE: VBSCRIPT AND EXCEL MACRO - 3/30/2008 11:15:24 PM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

i unable to find the problem :(

(in reply to tools)

Post #: 2

RE: VBSCRIPT AND EXCEL MACRO - 3/31/2008 12:57:20 AM

ebgreen

Posts: 4972
Score: 31
Joined: 7/12/2005
Status: offline

Which line gives you the error?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to tools)

Post #: 3

RE: VBSCRIPT AND EXCEL MACRO - 3/31/2008 2:35:52 PM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

The following line gives me an error

obj.Run "TEST" 'runs the macro

(in reply to ebgreen)

Post #: 4

RE: VBSCRIPT AND EXCEL MACRO - 4/2/2008 2:22:15 PM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

(in reply to tools)

Post #: 5

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 12:18:13 AM

ebgreen

Posts: 4972
Score: 31
Joined: 7/12/2005
Status: offline

If the macro is held in the workbook then try this:

obj.Run "ThisWorkbook.TEST"

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to tools)

Post #: 6

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 12:26:56 AM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

Nopes This is not working :(
Now the macro is not running

(in reply to ebgreen)

Post #: 7

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 12:34:54 AM

ebgreen

Posts: 4972
Score: 31
Joined: 7/12/2005
Status: offline

"Now the macro is not running "

Does that mean that it was running before? Regardless, using the line that I posted along with the code that you posted ran a macro in my workbook just fine. I suspect that we need more information if it is not working for you.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to tools)

Post #: 8

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 12:38:33 AM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

The macro was running previously but its not wrking now.

(in reply to ebgreen)

Post #: 9

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 12:40:19 AM

ebgreen

Posts: 4972
Score: 31
Joined: 7/12/2005
Status: offline

Please post the exact code that you are running.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to tools)

Post #: 10

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 2:54:22 PM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

The code i am running is same as above. Ill paste it again

sub openexcel()
     Dim obj
     Set obj = CreateObject("Excel.application")
     obj.Workbooks.Open "C:/Final templates/MyExcel.xls"
     obj.visible=true
   On Error Resume Next
     obj.Run "TEST"
     msgbox Err.Number & "         " & Err.Description
end sub

In the TEST macro, I am calling a function which would perform validations on the excel sheet.
I wanted to upload the excel sheet so that u could have a look at the macro but i am unable to do so.

This code does not throw an error when the macro consists of only a Msgbox but when I call the function that I have written it throws an error at the
obj.Run "TEST" line.

Thanks

(in reply to ebgreen)

Post #: 11

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 5:03:07 PM

ehvbs

Posts: 2173
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi tools,

so show the VBA macro code.

Regards

ehvbs

(in reply to tools)

Post #: 12

RE: VBSCRIPT AND EXCEL MACRO - 4/3/2008 5:07:36 PM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

The code is too long to paste it here.

(in reply to ehvbs)

Post #: 13

RE: VBSCRIPT AND EXCEL MACRO - 4/7/2008 12:10:29 AM

ebgreen

Posts: 4972
Score: 31
Joined: 7/12/2005
Status: offline

Since it does not throw an error with a simple MsgBox and it does throw an error with your other code, then the problem would certainly appear to be in the macro not the VBScript. We can help you with that but not if you are not willing to post the code.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to tools)

Post #: 14

RE: VBSCRIPT AND EXCEL MACRO - 4/7/2008 2:21:50 PM

tools

Posts: 14
Score: 0
Joined: 3/27/2008
Status: offline

this is the code that i have written
this function is been called from the TEST macro which consists of a simple if else loop.
this function is further calling some other functions which i have not posted

Function FetchExcelData()
'
' FetchExcelData Macro
' Macro recorded 2/21/2008 by Amey M
'
'
  
  Dim gridcount, formcount
  Dim attnamegrid
  Dim attgridcol
  Dim attTypeGrid() As String
  Dim attBelongsToGrid
  Dim attgridrow() As Integer
  Dim attnameform() As String
  Dim attformcol() As String
  Dim attTypeForm() As String
  Dim attBelongsToForm
  Dim attformrow
  Dim objtype
  Dim refNameForm
  Dim refNameGrid
  Dim webparamform
  Dim webparamgrid
  Dim webmethdform
  Dim webmethdgrid
  Dim url1
  Dim namespce
  Dim gridform
  Dim gridformname
  Dim gridformcolloc
  Dim gridformrowloc
  Dim greaterdatevalidgrid
  Dim lesserdatevalidgrid
  Dim greaterdatevalidform
  Dim lesserdatevalidform
  Dim lockdate
  Dim fieldLengthGrid
  Dim fieldLengthForm
  Dim editable

gridcount = 0
  formcount = 0

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Code to fetch lockdate
  
  ' create an envelope tag for the SOAP message
  Dim env1
  Set env1 = CreateObject("pocketSOAP.Envelope.2")
  env1.EncodingStyle = ""
  env1.SetMethod "getLockDate", "http://excel.services.planning.applications/"
  Dim http1
  Set http1 = CreateObject("pocketSOAP.HTTPTransport")
  ' we need to set the SOAPAction header
  http1.SOAPAction = ""
 ' http.Authentication "282432", ""
 ' and serializes it out over the HTTP request
  http1.SetProxy "172.25.35.200", 9090
  http1.Send "http://172.25.35.200:8080/tools/services/ExcelWS", env1
  ' now, we need to parse the SOAP message we get as a response
  env1.Parse http1
  
    For Each params In env1.Parameters
      Set firstNode = params.Value
        Sheet2.Range("A1").Value = firstNode.Nodes.ItemByName("lockDate").Value
          lockdate = firstNode.Nodes.ItemByName("lockDate").Value
    Next
  
  
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ' code to fetch the attributes that are displayed in the excel sheet
  
  Dim env
  Set env = CreateObject("pocketSOAP.Envelope.2")
  env.EncodingStyle = ""
  env.SetMethod "getExcelData", "http://excel.services.planning.applications/"
  env.Parameters.Create "templateName", "Testcase"
 
  Dim http
  Set http = CreateObject("pocketSOAP.HTTPTransport")
  
  ' we need to set the SOAPAction header
  http.SOAPAction = ""
 ' http.Authentication "282432", ""

' and serializes it out over the HTTP request
  http.SetProxy "172.25.35.200", 9090
  http.Send "http://172.25.35.200:8080/tools/services/ExcelWS", env
  
  ' now, we need to parse the SOAP message we get as a response
  env.Parse http

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' code to display the attributes inside the excel sheet that are retrieved from the database

Dim nodecount
      nodecount = env.Parameters.Item(0).Nodes.count - 1
      Dim count
      count = 7
      Dim webcount
      webcount = 2
      Dim number
      number = 0
      Dim refcountform
      refcountform = 0
      Dim refcountgrid
      refcountgrid = 0
      Sheets("Excel Data").Select
      Sheets("Excel Data").Unprotect
      
      For j = 0 To nodecount
          
          Dim tagName As String
          tagName = env.Parameters.Item(0).Nodes.Item(j).Name
      
       Select Case tagName
      
          Case "attributes"
          
          If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value = "Reference" Then
          
              If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value = "Grid" Then
                  refcountgrid = refcountgrid + 1
              Else
                  refcountform = refcountform + 1
              End If
          
          Else
              Sheet2.Range("A" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeName").Value 'debug
              Sheet2.Range("B" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value 'debug
              Sheet2.Range("C" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value 'debug
              Sheet2.Range("D" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value 'debug
              Sheet2.Range("E" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value 'debug
              Sheet2.Range("F" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("belongsTo").Value 'debug
              Sheet2.Range("G" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("greaterdatevalidations").Value 'debug
              Sheet2.Range("H" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("lesserdatevalidations").Value 'debug
              Sheet2.Range("I" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("fieldLength").Value 'debug
              Sheet2.Range("J" + Trim(Str(count))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("editable").Value 'debug
              
              
              Range((env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value) + (env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value)).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeName").Value
            
              ' count the number of attributes present in grid and form
              If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value = "Grid" Then
                gridcount = gridcount + 1
              Else
                formcount = formcount + 1
              End If
         
              count = count + 1
              number = number + 1
          End If
          Case "webDetails"
      
              Sheet2.Range("A" + Trim(Str(webcount))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("namespace").Value 'debug
              Sheet2.Range("B" + Trim(Str(webcount))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("url").Value 'debug
              Sheet2.Range("C" + Trim(Str(webcount))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("webMethod").Value 'debug
              Sheet2.Range("D" + Trim(Str(webcount))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("webParameters").Value 'debug
              Sheet2.Range("E" + Trim(Str(webcount))).Value = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value 'debug
              webcount = webcount + 1
      
      End Select
      
      Next
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
  ' Store the data retrieved from the database into variables
  
  
   If gridcount <> 0 Then
   ' define arrays for attributes that belong to the grid
      ReDim attnamegrid(gridcount - 1)
      ReDim attgridcol(gridcount - 1)
      ReDim attTypeGrid(gridcount - 1)
      ReDim attBelongsToGrid(gridcount - 1)
      ReDim attgridrow(gridcount - 1)
      ReDim greaterdatevalidgrid(gridcount - 1)
      ReDim lesserdatevalidgrid(gridcount - 1)
      ReDim fieldLengthGrid(gridcount - 1)
      
   End If
  
    If formcount <> 0 Then
     ' define arrays for attributes that belong to the form
      ReDim attnameform(formcount - 1)
      ReDim attformcol(formcount - 1)
      ReDim attTypeForm(formcount - 1)
      ReDim attformrow(formcount - 1)
      ReDim attBelongsToForm(formcount - 1)
      ReDim greaterdatevalidform(formcount - 1)
      ReDim lesserdatevalidform(formcount - 1)
      ReDim fieldLengthForm(formcount - 1)
    End If

ReDim objtype(number - 1)
  
  If refcountform <> 0 Then
  ' define arrays for reference elements that belong to form
  ReDim refNameForm(refcountform - 1)
  
  End If
  
  If refcountgrid <> 0 Then
  ' define arrays for reference elements that belong to grid
  ReDim refNameGrid(refcountgrid - 1)
  
  End If

Dim no
  no = 0
  Dim formno
  formno = 0
  Dim gridno
  gridno = 0
  Dim refformno
  refformno = 0
  Dim refgridno
  refgridno = 0
  
' iterate over each node and check the tagname whether it belongs to attributes or webdetails
  For j = 0 To nodecount
          
      tagName = env.Parameters.Item(0).Nodes.Item(j).Name
           Select Case tagName
      
              Case "attributes"
               ' if the tagname is attributes then check whether that attribute belongs to Grid or Form
               ' if it belongs to grid then check the type of attribute i.e. if it is a reference element or not
               ' and accordingly save the data inside arrays
                  If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value = "Grid" Then
                  
                      If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value = "Reference" Then
                      
                              refNameGrid(refgridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeName").Value
                              refgridno = refgridno + 1
                      
                      Else
                  
                         attnamegrid(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeName").Value
                         attgridcol(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value
                         attTypeGrid(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value
                         attBelongsToGrid(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("belongsTo").Value
                         attgridrow(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value
                         greaterdatevalidgrid(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("greaterdatevalidations").Value
                         lesserdatevalidgrid(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("lesserdatevalidations").Value
                         fieldLengthGrid(gridno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("fieldLength").Value
                         
                         
                        If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("belongsTo").Value = " " Then
                         attcolrow = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value
                        End If
                         
                         ' saving only the attributes that belong to the grid and form and not of type reference
                         objtype(no) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value
                         gridno = gridno + 1
                         no = no + 1
                      
                      End If
                  Else
                      ' similarly for the form
                      If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value = "Reference" Then
                      
                          refNameForm(refformno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeName").Value
                          refformno = refformno + 1
                      
                      Else
                          attnameform(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeName").Value
                          attformcol(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value
                          attTypeForm(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value
                          attformrow(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value
                          attBelongsToForm(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("belongsTo").Value
                          greaterdatevalidform(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("greaterdatevalidations").Value
                          lesserdatevalidform(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("lesserdatevalidations").Value
                          fieldLengthForm(formno) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("fieldLength").Value
                          objtype(no) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value
                          
                          formno = formno + 1
                          no = no + 1
                      End If
                  
                  End If
                 
                                 
              Case "webDetails"
              ' if the tagname is webDetails then check its type and save the webparameters name and webmethod name
                  If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value = "Grid" Then
          
                      webparamgrid = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("webParameters").Value
                      webmethdgrid = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("webMethod").Value
          
                  Else
                      
                      webparamform = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("webParameters").Value
                      webmethdform = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("webMethod").Value
                  
                  End If
                  
                  ' save the url to be fired while saving and the namespace
                  url1 = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("url").Value
                  namespce = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("namespace").Value
                  
          End Select
          
  Next

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' save attributes that are common to both grid as well as form
gridform = 0

Dim b As Integer
b = 0
 
  For j = 0 To nodecount
      tagName = env.Parameters.Item(0).Nodes.Item(j).Name
           Select Case tagName
              Case "attributes"
                  If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value = "Grid" Then
                      If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value = "Reference" Then
                      Else
                          b = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value
                           If b < attcolrow Then
                               gridform = gridform + 1
                           End If
                        End If
                  End If
              Case "webDetails"
            End Select
  Next
  
If (gridform > 0) Then
' define arrays for attributes that belong to both form as well as grid
ReDim gridformname(gridform - 1)
ReDim gridformcolloc(gridform - 1)
ReDim gridformrowloc(gridform - 1)

End If

Dim gfc
gfc = 0

' save data for the attributes that are common to form and grid
   For j = 0 To nodecount
      tagName = env.Parameters.Item(0).Nodes.Item(j).Name
           Select Case tagName
              Case "attributes"
                  If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("type").Value = "Grid" Then
                      If env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeType").Value = "Reference" Then
                      Else
                          b = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value
                           ' assuming that form is always displayed above grid
                           If b < attcolrow Then
                              gridformname(gfc) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributeName").Value
                              gridformcolloc(gfc) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value
                              gridformrowloc(gfc) = env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value
                              gfc = gfc + 1
                           End If
                        End If
                  End If
              Case "webDetails"
            End Select
  Next
  
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' following code will fetch the data from the excel sheet and do validations on it
' and then if all validations are successful then the data would be sent to the database for saving

Dim envgrid
Set envgrid = CreateObject("pocketSOAP.Envelope.11")
envgrid.EncodingStyle = ""
envgrid.SetMethod webmethdgrid, url1
Sheet1.Activate
ActiveSheet.Unprotect
Range("A1:IV65536").Locked = False
 
Dim chk
chk = 0

Dim gridstatus
gridstatus = 0

Dim formstatus
formstatus = 0

Dim gridformstatus
gridformstatus = 0

Dim gno
Dim fno
gno = 0
fno = 0

Dim fieldlength

Dim isvalid
isvalid = 0
Dim valdate
Dim negative
Dim alphanum

Dim rowloc
rowloc = attcolrow + 1

Dim refnoform
refnoform = 0
 
Dim refnogrid
refnogrid = 0
 
Dim envform
Set envform = CreateObject("pocketSOAP.Envelope.11")
envform.EncodingStyle = ""
envform.SetMethod webmethdform, url1
gfc = 0

For Each Parameter In objtype
' check the type if its grid then create the grid xml and send the xml to the webservice and set a status so that the xml gets created only once for grid and once for form

If objtype(chk) = "Grid" And gridstatus = 0 Then

' chk if the grid contains any reference elements if no then iterate in a do while loop and create the xml

If refcountgrid = 0 Then

Do While (Sheet1.Range("B" + Trim(Str(rowloc))).Text) <> ""
           Set actiongrid = envgrid.Parameters.Create(webparamgrid, "")
             
             ' iterate over all the attributes of the grid
             For Each param In attnamegrid
                  ' iterate over all the elements that belong to grid as well as form
                  For j = 0 To gridform - 1
                      
                      If attnamegrid(gno) = gridformname(j) Then
                      ' validate the data depending  on the type of that field and set the gridformstatus so that this attribute is not added twice in the xml
                              If attTypeGrid(gno) = "date" Then
                                  valdate = Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value
                                      
                                      If (CheckDateFormat(valdate)) Then
                                                         isvalid = 1
                                                         GoTo Invalid
                                      End If
                                   
                                    If greaterdatevalidgrid(gno) <> "" Then
                                                  
                                                  If (greaterdatevalidations(greaterdatevalidgrid(gno), rowloc, valdate, attnamegrid, attgridcol, attnamegrid(gno), lockdate)) Then
                                                      isvalid = 1
                                                      GoTo Invalid
                                                  Else
                                                  actiongrid.Nodes.Create gridformname(j), Format(CDate(Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value), "yyyy-mm-dd")
                                                  End If
                                    ElseIf lesserdatevalidgrid(gno) <> "" Then
                                                
                                              If (lesserdatevalidations(lesserdatevalidgrid(gno), rowloc, valdate, attnamegrid, attgridcol, attnamegrid(gno), lockdate)) Then
                                                  isvalid = 1
                                                  GoTo Invalid
                                              Else
                                              actiongrid.Nodes.Create gridformname(j), Format(CDate(Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value), "yyyy-mm-dd")
                                             End If
                                          
                                    Else
                                              actiongrid.Nodes.Create gridformname(j), Format(CDate(Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value), "yyyy-mm-dd")
                                    End If
                                
                             
                              ElseIf attTypeGrid(gno) = "Number" Then
                                  negative = Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value
                                  If NumberValidation(negative, gridformname(j)) = True Then
                                              isvalid = 1
                                              GoTo Invalid
                                              
                                  Else
                                              actiongrid.Nodes.Create gridformname(j), Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value
                                  End If
                              ElseIf attTypeGrid(gno) = "alphanumeric" Then
                                  alphanum = Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value
                                      If (AlphaNumericValidation(alphanum, gridformname(j))) Then
                                              isvalid = 1
                                              GoTo Invalid
                                             
                                      Else
                                              actiongrid.Nodes.Create gridformname(j), Sheet1.Range(Chr(Asc(gridformcolloc(j)) + 1) + Trim(Str(gridformrowloc(j)))).Value
        &nb