Import data from excel to VB form 1

[trenta87]

Does anyone know if there is a way to transfer data from excel to certain textboxes into VB.net forms?

 

[maytel]

did you mean
getting a cell value from excell to through vb?
if so here it is

Private Sub LoadCont(ByVal sPathFile As String)

Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
Dim oWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim oWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

oExcelApp = CreateObject("Excel.Application")
oWorkBook = oExcelApp.Workbooks.Open(sPathFile)
oWorkSheet = oWorkBook.Worksheets(1)

txtbox.text = oWorkSheet.Range("E1").Value

oWorkBook.Saved = False
oWorkBook.Save()
oWorkSheet = Nothing
oWorkBook.Close()
oExcelApp = Nothing

end sub

 

[Bluejay07]

I came across this thread and found it helpful, except the return values for me are incorrect.

There are 3 worksheets in the excel file I am referecing.
Worksheet 1 has 98 rows used.
Worksheet 2 has 1246 rows used.
Worksheet 3 has 5516 rows used.

oExcelApp = CreateObject("Excel.Application")
oWorkBook = oExcelApp.Workbooks.Open(pFile)
oWorkBook.ReadOnly.Equals(True)
oWorkSheet = oWorkBook.Worksheets(1)

System.Diagnostics.Debug.WriteLine(oWorkSheet.UsedRange.Rows.Count)
[COLOR=red]Returns 99 - One line too many[/color]

oWorkSheet = oWorkBook.Worksheets(2)
System.Diagnostics.Debug.WriteLine(oWorkSheet.UsedRange.Rows.Count)
[COLOR=red]Returns 65536 - Total cell rows[/color]

oWorkSheet = oWorkBook.Worksheets(3)
System.Diagnostics.Debug.WriteLine(oWorkSheet.UsedRange.Rows.Count)
[COLOR=red]Returns 5516 - exact amount[/color]

oWorkSheet = Nothing
oWorkBook.Close()
oExcelApp = Nothing

Can anyone tell me what is going wrong?

Thank you.

 

[Bluejay07]

I have changed the debug line in order to verify that the worksheet is in fact changing.

System.diag.....(oWorkSheet.UsedRange.Rows.Count & ", " & oWorkSheet.UsedRange.Columns.Count & ", " & oWorkSheet.Name)

The number of columns is correct and the worksheet name is correct but still not the row count.

I would appreciate any help.

 

[RonRepp]

This will give you the last row in a range.

Function FindLastRow() As Integer
    Dim R As Integer
    Dim LastRow As Integer
    R = XL.ActiveSheet.UsedRange.Rows.Count
    LastRow = XL.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    FindLastRow = LastRow
End Function

I can't take credit for it, since I found it some time back on the VBA site.

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[Bluejay07]

RonRepp,

Thank you for your reply. I am trying to incorporate this code now.

Unfortunately, this code is providing me with the same results.

Both method are working exactly as they should, however, there is an issue.

Here's my problem:
- Say you have a spreadsheet of 100 rows.
- Now if you delete 30 rows, the last used row should be row 70.
- If you click on a cell and press CTRL + END, the active cell remains in Row 100.

As the spreadsheet is still seeing the deleted cells as 'active' cells, so does the code, thus producing an invalid used row count.

I know this is sort of getting of track, but is there a way to fix this?

Thanks.

 

[Sorwen]

Both ways work fine for me. How are you deleting the rows?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[PRPhx]

Ok, there IS a way to reset the last cell in Excel. Here is a link that explains what you will need to do. I've tried it and it works. You'll have to figure out the programming required, but it shouldn't be too difficult (LOL).

http://support.microsoft.com/kb/244435.

 

[Bluejay07]

Sorry for the late reply. I had to change some items in other projects.

Sorwen,
I tried deleting rows by either selecting rows and pressing 'Delete' or selecting several rows, right click and select 'Delete'.

PRPhx,
Thanks for the tip. Referencing the items in that link seems to work.

I had to delete the rows, save the excel file, close the application and re-open it.

Not sure if this can be done programatically, but in this case, doing it manually works fine.

Thank you for the comments.

 

[Sorwen]

Sorwen,
I tried deleting rows by either selecting rows and pressing 'Delete' or selecting several rows, right click and select 'Delete'.

Ummm, back up a bit. When you say this the only thing that comes to mind is that you have the excel workbook open and are deleting the cells. If you are querying the workbook from from another program while your editing the workbook in excel itself or some other strange thing along those lines, then yes it is not going to return the correct data.

PRPhx,
Thanks for the tip. Referencing the items in that link seems to work.

I had to delete the rows, save the excel file, close the application and re-open it.

Not sure if this can be done programatically, but in this case, doing it manually works fine.

Possibly. Each step is something that can be done in a program. The problem is you've left a lot out of what is actually happening so it is impossible to say.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!