Ever wanted to lock and send data from multiple worksheet in a just one click button ? Dont worry, I write a simple code in VBSCript which you can embeded in your excel file :
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
Global stCancel As Boolean
Global LastRowLog
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 = 5 ‘ 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
Function SendData(WorkBookFile, Server, AppName, DBName, UserID, Pwd) As Integer
Dim wb As Workbook
Dim ws As Worksheet
Dim WorkBookName As String
Dim fso As New FileSystemObject
Dim firstws
Dim temp
Dim lastusedcell
Dim i
Dim j, k
DoEvents
If stCancel = True Then
SendData = -2
Exit Function
End If
WorkBookName = fso.GetFile(WorkBookFile).Name
If Not WorkbookOpen(WorkBookName) Then
Workbooks.Open WorkBookFile, False, True
End If
Set wb = Workbooks(WorkBookName)
firstws = GetFirstActiveSheet(WorkBookName)
DoEvents
If stCancel = True Then
SendData = -2
wb.Close
Exit Function
End If
temp = “[" & WorkBookName & "]” & firstws
x = EssVConnect(temp, UserID, Pwd, Server, AppName, DBName)
hCtx = EssVGetHctxFromSheet(temp)
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
DoEvents
If stCancel = True Then
SendData = -2
wb.Close
Exit Function
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
j = 0
For Each ws In wb.Worksheets
DoEvents
DoEvents
If stCancel = True Then
SendData = -2
wb.Close
Exit Function
End If
If ws.Visible = xlSheetVisible Then
temp = “[" & WorkBookName & "]” & ws.Name
LastRowLog = LastRowLog + 1
Workbooks(WorkBookName).Activate
Sheets(ws.Name).Select
DoEvents
DoEvents
If stCancel = True Then
SendData = -2
wb.Close
Exit Function
End If
x = EssMenuVLock()
x = EssVSendData(temp, Range(”A1:” & lastusedcell))
j = j + 1
DoEvents
DoEvents
If stCancel = True Then
SendData = -2
Exit For
End If
End If
Next
‘Logout all sheets
k = 0
For Each ws In wb.Worksheets
DoEvents
If ws.Visible = xlSheetVisible Then
k = k + 1
If k > j Then
Exit For
End If
temp = “[" & WorkBookName & "]” & ws.Name
x = EssVDisconnect(temp)
End If
Next
wb.Close
SendData = 1
Exit Function
Quit:
wb.Close
If hCtx = 0 Then
SendData = -1
End If
If x <> 0 Then
SendData = -1
End If
End Function
