Here is sample VBA code from the SampleVBA worksheet of SAMPVBA.XLS.
Declarations and other Visual Basic lines of code must appear on one line each. Where you see wrapped lines of code in this printed sample, assume that they should appear on one line, as they do in the sample file. |
'VBA Declaration Section. See ESSXLVBA.TXT for a list of all VBA declarations. Declare Function EssVGetHctxFromSheet Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal userName As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long Declare Function EssMenuVLock Lib "ESSEXCLN.XLL" () As Long Declare Function EssVSendData Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant) As Long Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long, ByVal globalOption As Variant) As Long Declare Function EssVGetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long) As Variant 'VB Declaration Section. See ESB32.BAS for a list of Visual Basic declarations. Declare Function EsbExport Lib "ESBAPIN.DLL" (ByVal hCtx As Long, ByVal AppName As String, ByVal DbName As String, ByVal FilePath As String, ByVal Level As Integer, ByVal isColumns As Integer) As Long Declare Function EsbGetProcessState Lib "ESBAPIN.DLL" (ByVal hCtx As Long, ProcState As ESB_PROCSTATE_T) As Long Declare Function EsbListLocks Lib "ESBAPIN.DLL" (ByVal hCtx As Long, ByVal AppName As String, ByVal DbName As String, pItems As Integer) As Long Declare Function EsbGetNextItem Lib "ESBAPIN.DLL" (ByVal hCtx As Long, ByVal dType As Integer, pItem As Any) As Long Dim hCtx As Long Dim sts As Long Dim AppName As String Dim DbName As String Dim PathName As String Dim Level As Integer Dim Columns As Integer Dim Count As Integer Dim X As Long Const ESB_DATA_INPUT = 3 Const ESB_STATE_DONE = 0 ' No process, or process complete Const ESB_STATE_INPROGRESS = 1 ' Async process is in progress Const ESB_LOCKINFO_TYPE = ' ESB_LOCKINFO_T (ESBListLocks) Const ESB_USERNAMELEN = 30 ' Max length of a user group Const ESB_NO = 0 Type ESB_PROCSTATE_T Action As Integer ' current process action State As Integer ' current process state Reserved1 As Integer ' reserved for future use Reserved2 As Long ' reserved for future use Reserved3 As Long ' reserved for future use End Type Type ESB_LOCKINFO_T LoginId As Long ' user identification tag Time As Long ' maximum time held (in seconds) nLocks As Integer ' number of block locks held userName As String * ESB_USERNAMELEN ' user/group name End Type Dim ProcState As ESB_PROCSTATE_T Dim LockInfo As ESB_LOCKINFO_T Sub SendData() 'Connect to each sheet of the book 'Select each sheet 'Lock and Send 'Check hCtx, a non-zero value indicates the sheet is connected. If it is zero, Connect. hCtx = EssVGetHctxFromSheet("[SAMPVBA.XLS]Jan") If hCtx = 0 Then X = EssVConnect("[SAMPVBA.XLS]Jan", "user", "password", "local", "Sample", "Basic") hCtx = EssVGetHctxFromSheet("[SAMPVBA.XLS]Jan") If hCtx = 0 Then MsgBox ("General Error in connecting to sheet.") GoTo Quit End If If X <> 0 Then MsgBox ("Connect Failed. Error: " + Str(X)) End If End If 'Get Global Options and set the message level. If level value is set to display message, turn it off. X = EssVGetGlobalOption(5) If X < 4 Then X = EssVSetGlobalOption(5, 4) End If 'Select worksheets, perform menu lock, send data range Sheets("Jan").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]Jan", range("A1:Y20")) Sheets("Feb").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]Feb", range("A1:Y20")) Sheets("Mar").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]Mar", range("A1:Y20")) Sheets("Apr").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]Apr", range("A1:Y20")) Sheets("May").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]May", range("A1:Y20")) Sheets("Jun").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]Jun", range("A1:Y20")) Sheets("Jul").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]Jul", range("A1:Y20")) Sheets("Aug").Select X = EssMenuVLock() X = EssVSendData("[SAMPVBA.XLS]Aug", range("A1:Y20")) 'Logout all sheets X = EssVDisconnect("[SAMPVBA.XLS]Jan") X = EssVDisconnect("[SAMPVBA.XLS]Feb") X = EssVDisconnect("[SAMPVBA.XLS]Mar") X = EssVDisconnect("[SAMPVBA.XLS]Apr") X = EssVDisconnect("[SAMPVBA.XLS]May") X = EssVDisconnect("[SAMPVBA.XLS]Jun") X = EssVDisconnect("[SAMPVBA.XLS]Jul") X = EssVDisconnect("[SAMPVBA.XLS]Aug") MsgBox ("Sends Completed.") Sheets("Main").Select Quit: End Sub Sub GoExport() 'Check hCtx, a non-zero value indicates the sheet is connected. If it is zero, Connect. If hCtx = 0 Then hCtx = EssVGetHctxFromSheet("[SAMPVBA.XLS]Jan") If hCtx = 0 Then X = EssVConnect("[SAMPVBA.XLS]Jan", "user", "password", "local", "Sample", "Basic") hCtx = EssVGetHctxFromSheet("[SAMPVBA.XLS]Jan") If hCtx = 0 Then MsgBox ("General Error in connecting to sheet.") GoTo Quit End If If X <> 0 Then MsgBox ("Connect Failed. Error: " + Str(X)) End If End If End If AppName = "Sample" DbName = "Basic" PathName = "c:\export.txt" Level = ESB_DATA_INPUT Columns = ESB_NO 'Export it sts = EsbExport(hCtx, AppName, DbName, PathName, Level, Columns) If sts <> 0 Then MsgBox ("Export Failed. Error " + Str$(sts)) End If 'Check Process State until Done sts = EsbGetProcessState(hCtx, ProcState) Do While ProcState.State = ESB_STATE_INPROGRESS sts = EsbGetProcessState(hCtx, ProcState) Loop If sts = 0 Then Sheets("MAIN").Select MsgBox ("Export Completed.") Else MsgBox ("Export failed.") End If Quit: End Sub Sub ListLocks() 'Check hCtx, a non-zero value indicates the sheet is connected. If it is zero, Connect. If hCtx = 0 Then hCtx = EssVGetHctxFromSheet("[SAMPVBA.XLS]Jan") If hCtx = 0 Then X = EssVConnect("[SAMPVBA.XLS]Jan", "user", "password", "local", "Sample", "Basic") hCtx = EssVGetHctxFromSheet("[SAMPVBA.XLS]Jan") If hCtx = 0 Then MsgBox ("General Error in connecting to sheet.") GoTo Quit End If If X <> 0 Then MsgBox ("Connect Failed. Error: " + Str(X)) End If End If End If AppName = "Sample" DbName = "Basic" sts = EsbListLocks(hCtx, AppName, DbName, Count) If Count = 0 Then MsgBox ("No Locks Exist") GoTo Quit End If For n = 1 To Count sts = EsbGetNextItem(hCtx, ESB_LOCKINFO_TYPE, LockInfo) MsgBox ("Lock # " + Str$(n) + " owned by " + Trim(LockInfo.userName)) MsgBox ("Number of Locks " + Str$(LockInfo.nLocks)) MsgBox ("Elapsed Locked Time of " + Str$(LockInfo.Time) + " seconds.") MsgBox ("Login ID of " + Str$(LockInfo.LoginId)) Next Quit: End Sub