You are here:

Excel/inserting data into Excel from vb

Advertisement


Question
QUESTION: Hi Bob
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
Rng=xlsheet.cells(x,y)
Rng.value="mytext"

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

Tony

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

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

QUESTION: Hi Bob
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
         Else
         If CInt(handicap2) < lowhandicap Then
         lowhandicap = CInt(handicap2)
         found = x
         End If
         End If
         End If

         Next
         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"
       Next

   End Sub

Answer
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Phillips

Expertise

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

Experience

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

Organizations
PASS UK Developer Group SQL Soton SQL FAQ

Education/Credentials
BSc Mathematics

Awards and Honors
Microsoft MVP since 2005

©2016 About.com. All rights reserved.