Sofia,
I will use some code from a personal spreadsheet of mine to demonstrate how
I am doing a thing that is similar to what you describe.
In Excel, you should go to the Visual Basic Editor and make sure that the
Access Object library and DAO (data object library) are checked in
Tools|References.
Then you need to dimension some Access objects that you will populate with
your actual existing database later.
Private Sub RecallLastSessionData()
Dim ws As Object 'access workspace
Dim db As Object 'access database
Dim tb As Object 'access table
Dim rst As Recordset
Dim stPath As String
Dim stTableName As String 'table name comes from worksheet name
Dim stQry As String
On Error GoTo NoDB
'Next statement: My Access db is in the same directory as My Excel workbook.
'Substitute the name of your actual Access database as indicated.
stPath = ActiveWorkbook.Path & "\YourDataBase.mdb"
'Next statement: In my app, the Table name in the DB is the same as the
worksheet name from which the macro is called.
‘Here I am creating a string variable that contains the name so that I can
use it later to ‘retrieve data from the table.
stTableName = ActiveSheet.Name
'Next: If this procedure is copied to a new sheet, this will alert user to
keep the sheet name
'to a legal length for an access table name.
If Len(stTableName) > 64 Then
stTableName = InputBox("You need to shorten this data table name. " &
stTableName & " A table name
cannot be over 64 Characters. This one is " & Len(stTableName) & "
characters long.", "Worksheet name is too long
for an Access table name.", stTableName)
End If
'Next: if the database exists, then I want ot create an Access workspace and
open the DB in it.
'Look up CreateWorkspace Method in VBA help files in Access or in Excel
after you have established a
'reference to the Microsoft Access 11.0 Object Library and one of the
Microsoft DAO Object Librarys,
'in my case the 3.6 Object Library.
If (Dir(stPath) <> "") Then
Set AXS = New Access.Application
Set ws = AXS.DBEngine.CreateWorkspace("DOH", "ADMIN", "", dbUseJet)
AXS.Visible = False 'I really don't need to see the db.
Set db = ws.OpenDatabase(stPath)
'Next: You will need to know your table name and the field (or column)
'names where the data is. At this point you are still inside the if statement.
Set tb = db.tabledefs(stTableName)
Else 'this else is in case the database is not found
GoTo NoDB
End If
'Next: use this statement to get your macro running, then remember to remove
it for mission critical apps.
'(Always work on a copy of your spreadsheet for developing.
On Error GoTo 0
'Next: Build a SQL select statement and assign it to a string variable. This
one opens the whole table,
'all fields, and does not filter. If the table is large, you might want to
add filtering to the SQL statement.
'One trick is to use Access to create a suitable query and then come back
and re-create the SQL in this string variable.
'Note the use of your table name string variable to build this larger
string. Also I used a specific known field name for ordering.
stQry = "SELECT " & stTableName & ".* FROM " & stTableName & " ORDER BY
[Time_Stamp] ASC;"
'Next: Open a recordset using that SQL statement
Set rst = db.OpenRecordset(stQry, dbOpenDynaset)
'Next: since this code is in a specific worksheet's code window, it already
knows which worksheet these ranges belong to,
'you just have to tell it the range addresses. If this code were in a
general module, you would have to specify
'both the worksheet and the ranges.
Range("I16:I17").ClearContents
Range("I23:I24").ClearContents
'Next: MoveLast is an Access VBA command; you can use it here because of
your Access references.
'Note that I already know the field names that I want to use. As you can
see, it is simple to put data from Access
'into Excel cells. 'With' statements can be nested. In other words, you can
say 'With' the recordset, as below,
'and then inside of that recordset 'With', 'With' a certain worksheet . You
can even close the worksheet 'With' when
'you are finished with it and open another worksheet "With", or ShapeRange
'With" for a worksheet, all from inside the original recordset
'With' statement. Just remember to 'End With' each 'With' statement when you
are finished with each one.
'My use of the data (on my worksheet) is below, you would substitute your
own field names from your own database table:
With rst
.MoveLast
Range("I23").Value = .Fields("Total_Wins")
Range("I24").Value = .Fields("Total_Losses")
Range("I16").Value = .Fields("Tracking_Wins")
Range("I17").Value = .Fields("Tracking_Losses")
End With
'Note that I was putting data into cells of the worksheet. Sofia wants to
put information into a Textbox, 'presumably on the worksheet. The easiest way
is to put your curser inside the text box and then go to the
'formula bar for that worksheet and click "=" and then click on a worksheet
cell that has the data that
'you want to display. That worksheet cell would be what you update with the
Database data. This cell can be on a different worksheet. For instance, if I
wanted to display some data
'retrieved above in a text box on another worksheet (in the same workbook),
then I would click the border
'of that text box and then click in the formula bar of that worksheet. Click
the equal key on the keyboard
'and then navigate to the worksheet with the ranges that are being updated
from your recordset.
'Click the cell that contains the data you want to display in the textbox on
the other sheet and hit Enter.
rst.Close
db.Close
ws.Close
AXS.Quit
PktSht
Exit Sub
NoDB:
stopit = MsgBox("There is no database set up. You must use the Send Session
To Database button first to set up
a database and give it a first record before running the update last session
from database macro.",
vbOKOnly)
If stopit = 1 Then Exit Sub
End Sub
Sofia, you can also access textboxes directly from code, but it is not as
straightforward at first as you might think.
Each worksheet has an invisible drawing layer which you might picture as
floating over it. If you draw a rectangle on that layer it looks like the
rectangle is on the worksheet. This rectangle is part of the "Shapes"
collection, called the ShapeRange, for that worksheet, as are all of the
Shapes on that worksheet. VBA is designed to handle objects in collections,
like the collection of worksheets in the workbook, and the collection cells
in a range on a worksheet, etc. For a discussion of how VBA uses the
ShapeRange to access individual Shape Objects, see Shape Object and
ShapeRange in VBA Help.
The rectangle itself does not contain text. Instead some drawing objects in
the shape collection can contain a speical OLE object called a TextFrame. For
a discussion, look up TextFrame in VBA Help.
If I was to use text boxes instead of worksheet cells, my code might look
like this:
YadaYadaYada
With rst
.MoveLast
Shapes(2).TextFrame.Characters.Text = .Fields("Total_Wins")
Shapes(3).TextFrame.Characters.Text = .Fields("Total_Losses")
Shapes(4).TextFrame.Characters.Text = .Fields("Tracking_Wins")
Shapes(5).TextFrame.Characters.Text = .Fields("Tracking_Losses")
End With
YadaYadaYada
End Sub, etc.
Note that my Shapes(1) is the CommandButton that I use to run the macro.
This is important because it tells you that you need to make note of which
rectangle in the worksheet is which shape number in the range. You could
write a short macro that writes each shape's number inside of itself, running
through all of the shapes as a collection and testing for those that have
textframes. Please do this on a copy of your worksheet because you will
replace any text in boxes that was meant to be permanent as well as finding
the ones meant to hold data. The shapes are automatically numbered as they
are added to the sheet and might not be where you expect them to be. Also,
all shapes are part of the collection (like the CommandButton in mine in the
example) and your text boxes might not be numbered 1, 2, and 3.
Sofia, Please let us know if this answers your question, or if you need
further help or clarification.
SongBear
