Reading data from an excel file

[krallek]

Hello all, I hope this hasn't been answered already. I couldn't find it anyways. All I want to do is to be able to read data from an xlsx file and use it in my VB 2010 application. Ultimately I will be using the excel file to store and load data to be used within my app. At this point I cant seem to even read the file and I am sure I am doing something silly. Here is what I have so far (after adding the two excel references 5.0 and 12):

Dim excelApp As Excel.Application
Dim excelWB As Excel.Workbook
Dim excelWS As Excel.Worksheet

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelWB = excelApp.Workbooks.Open("C:\Users\Dad\documents\visual studio 2010\Projects\ExcelImport\ExcelImport\Book1.xlsx")
excelWS = excelWB.Worksheets(1)


' this next line is just to test to see if I read the data properly ... and no I didn't
Me.Label1.Text = excelWB.Cells(1, 1).value

it is giving me an error message on my open command. I am not even sure if open is the right method to use. This seems to actually open excel and then the file. What I really want is to just read the file and grab the data. Eventually I would love to be able to write to the file as well but reading it would be great for now.

Thanks in advance.

 

[Budius]

Reading or write it's just a matter of which side of the '=' symbol you put the excell Cell.value
below it's some of my tested and working code, feel free to grab whatever bits you need.

    Dim oExcel As Object
    Dim oWB As Object
    Dim oWS As Object

            oExcel = CreateObject("Excel.Application")
            oExcel.UserControl = False
            oWB = oExcel.Workbooks.Add
            oWS = oWB.Worksheets(1)

            oWS.Name = "EEPROM"
            oWS.Rows("1:1").Font.Bold = True
            oWS.Rows("2:2").Font.Bold = True
            oWS.Cells(1, 1) = r.text(Me.Name, "Machine_SN")
            oWS.Cells(1, 2) = WTU.SerialNumber
            oWS.Cells(2, 1) = r.text(Me.Name, "DateTime")
            oWS.Columns("A:A").NumberFormat = "dd/MM/yyyy HH:mm:ss"
            oWS.Columns("A:A").ColumnWidth = 20
            oWS.Cells.EntireColumn.AutoFit()

.... etc ....

            oWS.Cells.EntireColumn.AutoFit()

            oWS = Nothing
            oWB = Nothing
            oExcel = Nothing

I did not use the Open method cause I was generating the files myself, but I know the open method should be kinda the way you're writing.
so you should be able to just replace the line
" oWB = oExcel.Workbooks.Add " with that you wrote, just make sure the filename/path is correct.
Try a System.IO.File.Exists(FilePath)

 

[JohnH]

You should remove the reference to Excel 5.0 and change to this:

Dim excelApp As New Excel.Application

remove the CreateObject line.

Then the Open should work.

Further, you write "excelWB.Cells", Workbook has Worksheets, Worksheet has cells.

 

[Budius]

Dim excelApp As New Excel.Application

because you see around ppl with a mix of Office 2003 and Office 2007, I kinda rather using late binding.
but of course, if you're sure the person will be using the same version you have, this way is 'cleaner'.

 

[JohnH]

because you see around ppl with a mix of Office 2003 and Office 2007, I kinda rather using late binding.
but of course, if you're sure the person will be using the same version you have, this way is 'cleaner'.

I'd say that is an advanced topic clearly out of OPs scope. It's fairly difficult if you don't know the object library 'by heart' to write typeless office automation code, and even those who do and have reason to usually develop the code typed and 'convert' it later. I would not recommend that approach when replying to such threads in any case.

 

[krallek]

Awesome!

Ok, that worked perfectly. The only thing I needed to add was a line to close the workbook . As I was debugging I must have had maybe 10 instances of my workbook open. Its all good now.

Does anyone know if it is possible to fetch and use the content of other excel objects? For example, is there a .text property of an inserted Excel textbox? Or perhaps to use the image inserted into an Excel image box?

Following the above model, I would think it would look something similar to the following heirarchy

What I used to get cell data --> Excel object > workbook > worksheet > Cell data

To get inserted image or textbox? Excel object > workbook > worksheet > textbox name

But I cant seem to find anywhere in Excel that names these objects.

Cheers and thanks again for your input.

DB

 

[Budius]

Ok, that worked perfectly. (...)
Does anyone know if it is possible to fetch and use the content of other excel objects? For example, is there a .text property of an inserted Excel textbox? Or perhaps to use the image inserted into an Excel image box?

Record a macro in Excel doing whatever you need to change, and then check what is the code generated for the macro ; )
It will give you the direction!