Read Excel in VB 2005

[Anonymous]

Hello everyone,

I have created an Add-In successfully and need to access some data in an Excel sheet. The data will be read in (possibly into an array) to provide the user with options in some ComboBoxes on a form. These will be easily editable by the user in Excel. There doesn't have to be a dynamic link, like an SQL datasource could provide, though this would be useful.

I have been surprised how difficult this is proving to be. I (wrongly) assumed Excel to Datagrid would be a well trodden path. So far I can only think I will have to establish a connection with a running Excel application (using Interop). Does anyone have experience with VB (Visual Studio 2005) and Excel explaining how I can simply read the data in from the .xls file?

Thank you.

Regards

Chris Molland

[Anonymous]

' used for excel operations
Public appDescription As Excel.Application
Public wbDescription As Excel.Workbook

Sub Setup()

On Error Resume Next
Set appDescription = GetObject(, "Excel.Application") ' Look for running copy of Excel
If Err.Number 0 Then 'if Excel is not running then
Set appDescription = CreateObject("Excel.Application") 'run it
End If
Err.Clear 'clear err object in case error occured.

On Error GoTo 0 'Resume normal error processing

Set wbDescription = appDescription.Workbooks.Open("C:\Program Files\Autodesk\Descriptions.xls")

End Sub

Sub CleanUp()
'Set the objects to nothing
appDescription.Quit
Set appDescription = Nothing
Set wbDescription = Nothing

End Sub

Sub FillDescriptionList()

Dim shtContinent As Excel.WorkSheet
Dim intColumnOfDescription As Integer
Dim rngDescriptionList As Excel.Range
Dim intFirstBlankCell As Integer
Dim loop1 As Integer

Set shtContinent = wbDescription.Sheets("Description")

intColumnOfDescription = shtContinent.Rows(1).Find("Description").Column

Set rngDescriptionList = shtContinent.Columns(intColumnOfDescription)

If (rngDescriptionList.Cells(1, 1) = "") Then
intFirstBlankCell = 0
Else
intFirstBlankCell = rngDescriptionList.Find("").Row
End If

For loop1 = 2 To intFirstBlankCell
frmSipe.CmbDescription.AddItem rngDescriptionList.Cells(loop1, 1)
Next

Set shtContinent = Nothing
Set rngDescriptionList = Nothing

End Sub



this was in VB6
Now using VB2005 I totally Changed to MSAccess Data Base I feel more comfertable

[Anonymous]

Here's an article I found that demonstrates accessing Excel as a database.
I suspect it will be faster than going through the Excel application.
--
Brian Ekins
Autodesk Inventor API

[Anonymous]

An interesting article indeed. Took me no time at all to
completely digest and memorize it fully! Wish they were
all that easy to memorize and implement 😉


Brian Ekins (Autodesk) wrote:
> Here's an article I found that demonstrates accessing Excel as a database.
> I suspect it will be faster than going through the Excel application.

[Anonymous]

I hope everyone got as much out of that last post as Bob did. 🙂

Here's the link.

http://www.codeproject.com/useritems/Excel_Connectivity.asp
--
Brian Ekins
Autodesk Inventor API

"Bob S." wrote in message
news:5551678@discussion.autodesk.com...
An interesting article indeed. Took me no time at all to
completely digest and memorize it fully! Wish they were
all that easy to memorize and implement 😉


Brian Ekins (Autodesk) wrote:
> Here's an article I found that demonstrates accessing Excel as a database.
> I suspect it will be faster than going through the Excel application.

[Anonymous]

Thank you Brian and saseendrankombath

I decided this morning that I didn't want my user to have to have another application (Excel) running. I think we all feel similarly about this.

Having not seen your post Brian I placed my data into an Access database containing 1 table with 2 fields (ID and list for ComboBox). I created a new data source using the wizard (Jet ODBC) and it previews fine. I ticked the box for only the field I want to use in the ComboBox. I select ComboBox from the pop-down on the Data Source window and drag and drop the control onto my form.

Everything looks fine but when I build the project I get 4 Errors: Type myprojectname.nameofdatasource is not defined (though the actual project name and name of datasource of course). This error points to lines in the form's Designer code. The error correction suggests adding "Global" before my project name, but the comments suggests I do not edit this code.

I don't understand it, it seems possibly to be a namespace or declaration problem but I don't understand the structure well enough to solve this. I would try your Excel example Brian but if the Access method could work it seems too simple to ignore.

[Anonymous]

The below is the way I use Access Data Base Hope This will be usefull

on a module
Option Explicit On

Module ExtDie
Public conMouldBase As New ADODB.Connection


Public Sub Connect_MouldBase()
If conMouldBase.State = 1 Then
conMouldBase.Close()
End If

conMouldBase.Open("Mould_Base") 'Mould_Base is my ODBC Name

End Sub
End Module
' Then on The Form class
Private rsMouldSize As New ADODB.Recordset
Private Sub DlgExtDie_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Try

Connect_MouldBase()

rsMouldSize = conMouldBase.Execute("Select Mould_Size From MouldSize order by Mould_Size") 'Mould_Size is Field Name and MouldSize is Table Name

Me.cmbDieSize.Items.Clear()
While Not rsMouldSize.EOF
Me.cmbDieSize.Items.Add(rsMouldSize.Fields(0).Value)
rsMouldSize.MoveNext()
End While

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

[Anonymous]

Hello,

Well I seem to have solved it.

I began my project using the Inventor 2008 Wizard in Visual Studio which has been fine. This morning I selected and excluded the StandardAddInServer item (created by the wizard) which then allowed me to Build the project. Having identified the troublesome item I then included it back into the project and looked at the code. Beneath the Imports statements is Namespace Strategy_01 (Strategy_01 being the name of my project). I commented out this and the End Namespace lines and everything works fine. I tested the add-in in Inventor and my form displays the pop-down combobox populated with the data from the Access table.

Is there an unforseen problem lurking around the corner because this seems too easy.

Regards

Chris