Transfering Data from VB to Excel

I wrote a program to interface an external data source with Excel using a winsock and a database control.

Under the current circumstances I am able to send only 1 column of data to Excel.

If possible please modify my code to allow for 2 or more columns of data.
If not then please give an alternate solution.

(As will you will see when you look at my code, I am in no way an expert so please explain whatever you do, Thanks)

Here it is
----------------------------------------

Dim ncount As Integer

Private Sub cmdconnect_Click()
sckdvt.Connect
End Sub

Private Sub Cmddisconnect_Click()
sckdvt.Close
End Sub

Private Sub Cmdend_Click()
Unload Me
End Sub

Private Sub CmdIP_Click()
sckdvt.Close
sckdvt.RemoteHost = Txtip.Text
LblIP.Visible = False
Txtip.Visible = False
CmdIP.Visible = False
cmdconnect.SetFocus
Beep
MsgBox ("You must now reconnect")
End Sub

Private Sub Cmdport_Click()
sckdvt.Close
sckdvt.RemotePort = Txtport.Text
Lblport.Visible = False
Txtport.Visible = False
Cmdport.Visible = False
cmdconnect.SetFocus
Beep
MsgBox ("You must now reconnect")
End Sub

Private Sub Form_Load()
frmSplash.Show vbModal
Load Frmmain
Load frmAbout
End Sub

Private Sub mnuabout_Click()
frmAbout.Show vbModal
End Sub

Private Sub mnuExit_Click()
Unload Me
End Sub

Private Sub mnuIP_Click()
LblIP.Visible = True
Txtip.Visible = True
CmdIP.Visible = True
Txtip.SetFocus
End Sub

Private Sub mnuPort_Click()
Lblport.Visible = True
Txtport.Visible = True
Cmdport.Visible = True
Txtport.SetFocus
End Sub

Private Sub sckdvt_DataArrival(ByVal bytesTotal As Long)
Static ncount As Integer
Dim strdata As String
Dim dmeasurement As Double
ncount = ncount + 1
If ncount < 60 Then
    sckdvt.GetData strdata, vbString
    Txtdata.Text = strdata
    If Len(strdata) < 15 Then
        Dtacontrol.Recordset.AddNew
        dmeasurement = Val(strdata)
        Txtexcel.Text = dmeasurement
    Else
        sckdvt.GetData strdata, vbString
        Txtdata.Text = strdata
    End If
End If
End Sub
Crono15MAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tureCommented:
Instead of using a data control that connects to an Excel sheet, you may access Excel this way.

First, set a reference to the Microsoft Excel Object Library.

Then use code similar to this... (I hope it's understandable)

Private Sub sckdvt_DataArrival(ByVal bytesTotal As Long)
  Static ncount As Integer
  Dim strdata As String

  Static xl As Excel.Application
  Static wb As Excel.Workbook
  Static ws As Excel.Worksheet
  Static rng As Excel.Range

  If xl Is Nothing Then
    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Open("c:\test\test.xls")
    Set ws = wb.Sheets("Sheet1")
    Set rng = ws.Range("A1")
  End If

  ncount = ncount + 1

  If ncount < 60 Then
    sckdvt.GetData strdata, vbString
    Txtdata.Text = strdata
    If Len(strdata) < 15 Then
      rng.Value = Val(strdata)
      rng.Offset(0,1).Value = "This goes into column B"
      rng.Offset(0,2).Value = "And column C"
      Set rng = rng.Offset(1,0)
    Else
      sckdvt.GetData strdata, vbString
      Txtdata.Text = strdata
    End If
  Else
    wb.Save
    wb.Close
    xl.Quit
    Set xl = Nothing
  End If
End Sub

Ture Magnusson
Karlstad, Sweden
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Crono15MAuthor Commented:
How do I reference to an Object Library?
I don't know if it makes a difference but, I am using Excel2000.
0
Crono15MAuthor Commented:
I figured out the referencing. Now just give me a chance to try it and I'll get back to you. Thanks
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rmarshlCommented:
Just curious..

Why would this fail?

If xl Is Nothing Then
    Set xl = New Excel.Application
   

also noticed "New" ... does this allow user to have Excel open in another window?
0
Crono15MAuthor Commented:
ture,
   As of now, I have only a few questions.
Given that I know that
     wb.Save
    wb.Close
    xl.Quit
    Set xl = Nothing
 
are all executing, The application does not close excel completely and thus I can not get at the data unless I force quit.

1. How do I completely close out of excel?

2. Can and how do I get at the spreadsheet while the program is running?

3.Every time I quit, it says the file already exists, "Do you want to replace it". I want the program to auotmatically replace the old file.

Thanks Again,
       Crono15M
0
Crono15MAuthor Commented:
ture,
   As of now, I have only a few questions.
Given that I know that
     wb.Save
    wb.Close
    xl.Quit
    Set xl = Nothing
 
are all executing, The application does not close excel completely and thus I can not get at the data unless I force quit excel.

1. How do I completely close out of excel?

2. Can and how do I get at the spreadsheet while the program is running?

3.Every time I quit, it says the file already exists, "Do you want to replace it". I want the program to auotmatically replace the old file.

Thanks Again,
       Crono15M
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.