software

I have designed an Access database that holds records relating to my stores
audit results going back for about 5 years plus a load more information
relating to these stores. This was used to produe a pack once a month,
however a change in senior management means that I have got to shelve this
and prodce a similar pack in Excel.

The idea would be that the user could select a month or a 12 mnth date range
that would produce data that could then be used to populate a number of excel
templates that have been designed. Having not used excel for years I would be
grateful for any suggestions on what route to take.

Thanks

Are you familiar with SQL? If so, you could keep the Access database and use
ADO to retrive the data to Excel, and then do the report in Excel. The SQL
could be parameter driven to get the month or range of data required. Here
is an example of some Access data maintenance macros

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject(quot;ADODB.Connectionquot;)
oConn.Open = quot;Provider=Microsoft.Jet.OLEDB.4.0;quot; amp; _
quot;Data Source=quot; amp; quot;c:\bob.mdbquot;

sSQL = quot;INSERT INTO Contacts (FirstName, LastName,Phone, Notes) quot; amp; _
quot; VALUES ('Bob','Phillips','01202 345678','me')quot;
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = quot;Provider=Microsoft.Jet.OLEDB.4.0;quot; amp; _
quot;Data Source=quot; amp; quot;c:\bob.mdbquot;

sSQL = quot;SELECT * From Contactsquot;
Set oRS = CreateObject(quot;ADODB.Recordsetquot;)
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) amp; quot; quot; amp; ary(1, 0) amp; quot;, quot; amp; ary(2, 0)
Else
MsgBox quot;No records returned.quot;, vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub

Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = quot;Provider=Microsoft.Jet.OLEDB.4.0;quot; amp; _
quot;Data Source=quot; amp; quot;c:\bob.mdbquot;

sSQL = quot;SELECT * From Contactsquot;
Set oRS = CreateObject(quot;ADODB.Recordsetquot;)
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox quot;No records returned.quot;, vbCritical
Else
sSQL = quot;UPDATE Contacts quot; amp; _
quot; SET Phone = 'None' quot; amp; _
quot;WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'quot;
oRS.ActiveConnection.Execute sSQL

sSQL = quot;SELECT * From Contactsquot;
oRS.ActiveConnection.Execute sSQL
ary = oRS.getrows
MsgBox ary(0, 0) amp; quot; quot; amp; ary(1, 0) amp; quot;, quot; amp; ary(2, 0)
End IfoRS.Close
Set oRS = Nothing
End Sub

--
HTH

RP
quot;fredquot; gt; wrote in message
...
gt; I have designed an Access database that holds records relating to my
stores
gt; audit results going back for about 5 years plus a load more information
gt; relating to these stores. This was used to produe a pack once a month,
gt; however a change in senior management means that I have got to shelve this
gt; and prodce a similar pack in Excel.
gt;
gt; The idea would be that the user could select a month or a 12 mnth date
range
gt; that would produce data that could then be used to populate a number of
excel
gt; templates that have been designed. Having not used excel for years I would
be
gt; grateful for any suggestions on what route to take.
gt;
gt; Thanks
Hi fred,

Thanks to ODBC you're not limited to one app over the other. Since you
have worked in Access and are likely familiar with SQL and using
queries, I recommend using MS Query. Open a blank Excel spreadsheet and
select Data | Import External Data | New Database Query. When the
Choose Data Source window opens, select MS Access Database. NOTE: MAKE
SURE that the quot;Use the Query Wizard to create/edit queriesquot; is
UNCHECKED...this is because the Query Wiz is stupid amp; does not know
about joins. Click OK. From the Select Database window, find your .mdb
file and select it. MS Query will open a GUI that looks very similar to
the Access QBE grid, and a list of database objects (click the Options
buttons and make sure all boxes are checked), including queries. Add the
desired objects to your query. Note that MS Query cannot quot;seequot; the
relational joins created in the Access db; you have to join tables on
key fields manually. Once you save the query, however, it will
remember. The commands are very similar to the Access query window, so
you should have no trouble creating queries, relating objects or setting
criteria; you can even create parameter queries that prompt the user.
After the query has been run, select File | Return data to Microsoft
Office Excel and the recordset will be exported to the spreadsheet you
originally opened, unless you specify otherwise. Save this workbook with
an appropriate name. Any time you wish to requery the data, open the
worksheet, right-click on any cell, and select Edit Query. It would
probably be fairly simple to set up an Excel User Form to automate the
process so that the user need never see the query.

Tushar Mehta has a nice tutorial with some easy-to-follow examples at
www.tushar-mehta.com/excel/ne...cel/index.html

Hope this helps!

LeAnne
fred wrote:
gt; I have designed an Access database that holds records relating to my stores
gt; audit results going back for about 5 years plus a load more information
gt; relating to these stores. This was used to produe a pack once a month,
gt; however a change in senior management means that I have got to shelve this
gt; and prodce a similar pack in Excel.
gt;
gt; The idea would be that the user could select a month or a 12 mnth date range
gt; that would produce data that could then be used to populate a number of excel
gt; templates that have been designed. Having not used excel for years I would be
gt; grateful for any suggestions on what route to take.
gt;
gt; Thanks

I add also a few examples on my site yesterday
Will add more soon

www.rondebruin.nl/accessexcel.htm
--
Regards Ron de Bruin
www.rondebruin.nlquot;Bob Phillipsquot; gt; wrote in message ...
gt; Are you familiar with SQL? If so, you could keep the Access database and use
gt; ADO to retrive the data to Excel, and then do the report in Excel. The SQL
gt; could be parameter driven to get the month or range of data required. Here
gt; is an example of some Access data maintenance macros
gt;
gt; Sub AddData()
gt; Dim oConn As Object
gt; Dim oRS As Object
gt; Dim sSQL As String
gt;
gt; Set oConn = CreateObject(quot;ADODB.Connectionquot;)
gt; oConn.Open = quot;Provider=Microsoft.Jet.OLEDB.4.0;quot; amp; _
gt; quot;Data Source=quot; amp; quot;c:\bob.mdbquot;
gt;
gt; sSQL = quot;INSERT INTO Contacts (FirstName, LastName,Phone, Notes) quot; amp; _
gt; quot; VALUES ('Bob','Phillips','01202 345678','me')quot;
gt; oConn.Execute sSQL
gt;
gt; oConn.Close
gt; Set oConn = Nothing
gt; End Sub
gt;
gt; Sub GetData()
gt; Const adOpenForwardOnly As Long = 0
gt; Const adLockReadOnly As Long = 1
gt; Const adCmdText As Long = 1
gt; Dim oRS As Object
gt; Dim sConnect As String
gt; Dim sSQL As String
gt; Dim ary
gt;
gt; sConnect = quot;Provider=Microsoft.Jet.OLEDB.4.0;quot; amp; _
gt; quot;Data Source=quot; amp; quot;c:\bob.mdbquot;
gt;
gt; sSQL = quot;SELECT * From Contactsquot;
gt; Set oRS = CreateObject(quot;ADODB.Recordsetquot;)
gt; oRS.Open sSQL, sConnect, adOpenForwardOnly, _
gt; adLockReadOnly, adCmdText
gt;
gt; ' Check to make sure we received data.
gt; If Not oRS.EOF Then
gt; ary = oRS.getrows
gt; MsgBox ary(0, 0) amp; quot; quot; amp; ary(1, 0) amp; quot;, quot; amp; ary(2, 0)
gt; Else
gt; MsgBox quot;No records returned.quot;, vbCritical
gt; End If
gt;
gt; oRS.Close
gt; Set oRS = Nothing
gt; End Sub
gt;
gt; Sub UpdateData()
gt; Const adOpenForwardOnly As Long = 0
gt; Const adLockReadOnly As Long = 1
gt; Const adCmdText As Long = 1
gt; Dim oConn As Object
gt; Dim oRS As Object
gt; Dim sConnect As String
gt; Dim sSQL As String
gt; Dim ary
gt;
gt; sConnect = quot;Provider=Microsoft.Jet.OLEDB.4.0;quot; amp; _
gt; quot;Data Source=quot; amp; quot;c:\bob.mdbquot;
gt;
gt; sSQL = quot;SELECT * From Contactsquot;
gt; Set oRS = CreateObject(quot;ADODB.Recordsetquot;)
gt; oRS.Open sSQL, sConnect, adOpenForwardOnly, _
gt; adLockReadOnly, adCmdText
gt;
gt; ' Check to make sure we received data.
gt; If oRS.EOF Then
gt; MsgBox quot;No records returned.quot;, vbCritical
gt; Else
gt; sSQL = quot;UPDATE Contacts quot; amp; _
gt; quot; SET Phone = 'None' quot; amp; _
gt; quot;WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'quot;
gt; oRS.ActiveConnection.Execute sSQL
gt;
gt; sSQL = quot;SELECT * From Contactsquot;
gt; oRS.ActiveConnection.Execute sSQL
gt; ary = oRS.getrows
gt; MsgBox ary(0, 0) amp; quot; quot; amp; ary(1, 0) amp; quot;, quot; amp; ary(2, 0)
gt; End If
gt;
gt;
gt; oRS.Close
gt; Set oRS = Nothing
gt; End Sub
gt;
gt; --
gt; HTH
gt;
gt; RP
gt; quot;fredquot; gt; wrote in message
gt; ...
gt;gt; I have designed an Access database that holds records relating to my
gt; stores
gt;gt; audit results going back for about 5 years plus a load more information
gt;gt; relating to these stores. This was used to produe a pack once a month,
gt;gt; however a change in senior management means that I have got to shelve this
gt;gt; and prodce a similar pack in Excel.
gt;gt;
gt;gt; The idea would be that the user could select a month or a 12 mnth date
gt; range
gt;gt; that would produce data that could then be used to populate a number of
gt; excel
gt;gt; templates that have been designed. Having not used excel for years I would
gt; be
gt;gt; grateful for any suggestions on what route to take.
gt;gt;
gt;gt; Thanks
gt;
gt;