You are here:

Excel/inserting data into Excel from vb


I am using Visual Basic 2008 Express Edition and Office 2010.

I have a VB programme that reads data from an existing Excel spreadsheet but when I attempt to insert data into the spreadsheet nothing is inserted but I do not get any errors in the VB programme.

The code I am using is:
Dim Rng as Excel.Range

xlsheet is already defined and is OK when I read from the spreadsheet. Can you help?


ANSWER: Can you show me the code where xlsheet is defined and set, and where x and y are set?

---------- FOLLOW-UP ----------

Many thanks for your reply, this is part of the code I am using.

Public Class Form1
   Dim xl As New Excel.Application
   Dim xlwbook As Excel.Workbook
   Dim xlsheet As Excel.Worksheet

   Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

       Dim location As String
       location = "C:\Users\Tony\Documents\Vets Golf\game matrix13.xlsx"
       xlwbook = xl.Workbooks.Open(location, [ReadOnly]:=False, [Editable]:=True)
       xlsheet = xlwbook.Sheets.Item(1)

   End Sub

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       Dim cplayed As Boolean
       Dim x As Integer
       Dim y As Integer
       Dim lowhandicap As Integer = 0
       Dim a As Integer = 0
       Dim b As Integer = 0
       Dim Rng As Excel.Range
       Dim first As Boolean = True
       Dim found As Integer
       Dim player1 As String
       Dim player2 As String
       Dim handicap1 As String
       Dim handicap2 As String
       Dim totalseniors(1, 1) As String
       ReDim totalseniors(12, 1)
       totalseniors = getteam()
       For y = 0 To 5
         found = 0
         lowhandicap = 0
         first = True
         player1 = totalseniors(y, 0)
         handicap1 = totalseniors(y, 1)
         For x = 6 To 11
         player2 = totalseniors(x, 0)
         If player2 = player1 Then Continue For
         handicap2 = totalseniors(x, 1)
         cplayed = checkifplayed(player1, player2, handicap1, handicap2)
         If cplayed = True Then
         If first Then
         lowhandicap = CInt(handicap2)
         first = False
         found = x
         If CInt(handicap2) < lowhandicap Then
         lowhandicap = CInt(handicap2)
         found = x
         End If
         End If
         End If

         If found = 0 Then
         MsgBox("no player found")
         End If
         TextBox3.Text = TextBox3.Text & player1 & " (" & handicap1 & ") " & totalseniors(found, 0) & " (" & totalseniors(found, 1) & ")" & vbNewLine
         Rng = xlsheet.Cells(y, found)
         Rng.Value = "played"

   End Sub

Hi Tony,

Looking at this code, most of it seems to be okay and nothing obviously wrong.

The one thing I did pick out is that you run an outer loop for the y variable of 0 to 5. y is later used when in defining the rng variable , Rng = xlsheet.Cells(y, found). If y is 0 at this point, this will generate at error. Have you checked what the value of y is when the error occurs?
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Bob Phillips


Can - VBA, Functions and formulas, PowerPivot, conditional formatting, data validation, charting


I have been using Excel for in excess of 20 yeras, and I am a 9 year Microsoft MVP

PASS UK Developer Group SQL Soton SQL FAQ

BSc Mathematics

Awards and Honors
Microsoft MVP since 2005

©2017 All rights reserved.