You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- inserting data into Excel from vb

Advertisement

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

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?

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

Answers by Expert:

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**Organizations**

PASS
UK Developer Group
SQL Soton
SQL FAQ**Education/Credentials**

BSc Mathematics**Awards and Honors**

Microsoft MVP since 2005