excel - Error "User-defined type not defined" -
i using below vba error "user-defined type not defined":
option explicit dim cn adodb.connection function connect(server string, _ database string) boolean set cn = new adodb.connection on error resume next cn ' create connecting string .connectionstring = "provider=sqloledb.1;" & _ "integrated security=sspi;" & _ "server=" & server & ";" & _ "database=" & database & ";" ' open connection .open end ' check connection state if cn.state = 0 connect = false else connect = true end if end function function query(sql string) dim rs adodb.recordset dim field adodb.field dim col long ' open recordset / run query set rs = new adodb.recordset rs.open sql, cn, adopenstatic, adlockreadonly, adcmdtext if rs.state col = 1 ' output column headings in first row each field in rs.fields cells(6, col) = field.name col = col + 1 next field ' output results in rest of worksheet cells(7, 1).copyfromrecordset rs set rs = nothing end if end function function disconnect() ' close connection cn.close end function private sub generate_click() dim sql string dim connected boolean dim server_name string dim database_name string dim allocation string sheets("lsp").select range("a6").select selection.currentregion.select selection.clearcontents ' our query sql = "select item_code,item_desc,location,quantity,lot_number,expiry_date " & _ "from pgm_invo " & _ "where system_date = '" & sheets("lsp").range("b1").value & "' " & _ "group segmentation_id,mpg" ' connect database server_name = sheets("master").range("d1").value database_name = sheets("master").range("g1").value connected = connect(server_name, database_name) if connected ' if connected run query , disconnect call query(sql) call disconnect else ' couldn't connect msgbox "could not connect!" end if end sub
kindly me error.
Comments
Post a Comment