Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Specific Cell in Excel from VB.NET

Posted on 2014-01-07
4
Medium Priority
?
449 Views
Last Modified: 2014-01-07
Imports Excel = Microsoft.Office.Interop.Excel    

Public Sub WhatsMyVal()
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(TextBox1.Text)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        Dim myString = xlWorkSheet.Range("A1").Value.ToString

        MsgBox("this is myString: " & myString)
    End Sub

Open in new window


is it possible to access cells in a worksheet by using something like xlWorkSheet.cells(1,1) instead of  xlWorkSheet.Range("A1").Value.ToString.  It seems it would be easier to navigate through a worksheet if I could just use 2 numbers.

Thanks for your help!!!
0
Comment
Question by:slightlyoff
  • 2
4 Comments
 
LVL 9

Expert Comment

by:s_chilkury
ID: 39762927
How about this:

// Get a reference to cell A1 and write the formatted value to the console.
            IRange a1 = worksheet.Cells["A1"];
            Console.WriteLine("A1={0}", a1.Text);
            // Get a reference to B2 and write the formula / value / text to the console.
            IRange b2 = worksheet.Cells[1, 1];
            Console.WriteLine("B2 Formula={0}, Value={1}, Text={2}", b2.Formula, b2.Value, b2.Text);


Also check the source here:

http://support.microsoft.com/kb/219151
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 total points
ID: 39762930
Yes, it is very easy... you already gave the answer.

Just go... xlWorkSheet.Dells(1,1).Value.ToString
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39762940
I'm a moron.  It didn't give "Value" in intelisense, so I figured it wasn't an option and didn't try it.  Thanks for the help!!!
0
 
LVL 40
ID: 39762986
For some reason, IntelliSense gives up after a few references (dots) when working with COM objects from .NET.

For instance, in something like Workbooks(1).Worksheets(1).Cells(1,1).Font.Bold = True, IntelliSense usually doest not go all the way.

However, the following will go all the way:

Dim rng As Range = Workbooks(1).Worksheets(1).Cells(1,1)
rng.Font.Bold=True
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question