With my unbound object frame control (reference to Excel spreadsheet)

[robx2309]

Hi all,


My problem is that I have an unbound object frame and I'd like to know how to do to hide the horizontal scrollbar, and the row and column headings.

And I also would like to know how can I do to retrieve the datas (having it in a variable within my form) I have pulled in my spreadsheet, because I would like, afterwards, insert them in my database.

Any help would be greatly appreciated

Have a nice day !

[June7]

What version of Access? I tested this in 2010 and have opposite issue. No scroll bars and don't see any way to get them. Doubt column headings can be hidden.

You can import spreadsheet data to Access table or link to a spreadsheet with the External Data utility.

[robx2309]

I use Access 2003.
But do you know if there is a way to retrieve the datas entered in the spreadsheet by coding ?
For example a function that is defined in the spreadsheet's code and that can be used in the Access form's code to return the value of a cell (for a given row and column) ?

[June7]

Not understanding what you mean by "function that is defined in the spreadsheet's code and that can be used in the Access form's code" But, yes, you can connect to an Excel workbook and get data from cells. Check this http://www.xtremevbtalk.com/showthre...621#post969621

Here is simple example:

Private Sub GetExcelData()
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim rowRW As Integer
Dim strFAAID As String
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("\\servername\path\filename.xlsx")
rowRW = 2
With oBook.Worksheets("Taxiway_Apron")
    Do While Not IsEmpty(.Range("A" & rowRW))
        If .Range("D" & rowRW) = "Airport" Then
            strFAAID = strFAAID & .Range("A" & rowRW) & vbCrLf
            End If
        End If
        rowRW = rowRW + 1
    Loop
End With
MsgBox strFAAID
End Sub

[robx2309]

Thanks for the code, that's what I was looking for.

Just a last question, as my excel file is not explicitly on my hard drive (it is within my form), what do I have to write to remplace this line :

Set oBook = oExcel.Workbooks.Open("\\servername\path\filename.xlsx")

[June7]

I have no idea how to refer to spreadsheet embedded on form, never done that.

[robx2309]

Right, I will try to figure this outon my own.
And which solution would you advise me : using ADO to connect to my spreadsheet or use the code you wrote in your previous message ?

[June7]

What I posted is all that I am familiar with, only method I have used.

[robx2309]

Right,
I will try to figure out how to link it to my form. Thanks a lot fo your help