Write to Remote Excel Spreadsheets

Category:

Network/Internet

Type:

Snippets

Difficulty:

Advanced

Author:

Anonymous

Version Compatibility: Visual Basic 6

More information:

This block of server-side and client-side code allows you to write data onto a remote Excel Sheet. The date and time will be written in Cell(A,1) and data in Cell(B,10).

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Server side :

Dim exapp As Excel.Application
Dim exbook  As Excel.Workbook
Dim exsheet As Excel.Worksheet
Dim c_line As Integer

Private Sub Command1_Click()
    If Not sock.State = sckClosed Then
        sock.Close
    End If
    sock.LocalPort = Text1.Text
    sock.Listen
    Form1.Caption = "Listening at port " & Text1.Text
End Sub

Private Sub Command2_Click()
If exapp.Visible = False Then
    Command2.Caption = "Hide excel"
    exapp.Visible = True
Else
    Command2.Caption = "Show excel"
    exapp.Visible = False
End If
End Sub

Private Sub Command3_Click()
exapp.Application.Quit
End
End Sub

Private Sub Form_Load()
Me.Move 6000, 2000
Set exapp = New Excel.Application
Set exbook = exapp.Workbooks.Add
Set exsheet = exbook.Sheets(1)
exsheet.Name = "Here is data from client"
c_line = 1
Command1_Click
End Sub

Private Sub sock_ConnectionRequest(ByVal requestID As Long)
        If sock.State = sckListening Then
        sock.Close
        sock.Accept (requestID)
    End If
End Sub

Private Sub sock_DataArrival(ByVal bytesTotal As Long)
    Dim s As String
    sock.GetData s
    exsheet.Range("a" & c_line).Value = Date & " - " & Time
    exsheet.Range("b" & c_line).Value = s
    c_line = c_line + 1
    'MsgBox "received " & bytesTotal & vbCrLf & s
End Sub

Private Sub sock_Error(ByVal Number As Integer, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, CancelDisplay As Boolean)
    MsgBox Description
End Sub

Client side :

Private Sub Command1_Click()
    sock.RemoteHost = Text1.Text
    sock.RemotePort = Text2.Text
    sock.Connect

End Sub

Private Sub Command2_Click()
    sock.SendData Text3.Text
    Text3.Text = ""
End Sub

Private Sub Command3_Click()
End
End Sub

Private Sub sock_Connect()
 'MsgBox "Client connect"
    Command2.Enabled = True
End Sub