If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel not Access
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 |
#2
|
|||
|
|||
Excel not Access
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("ADODB.Connection") oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _ " VALUES ('Bob','Phillips','01202 345678','me')" 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 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "SELECT * From Contacts" Set oRS = CreateObject("ADODB.Recordset") 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) & " " & ary(1, 0) & ", " & ary(2, 0) Else MsgBox "No records returned.", 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 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "SELECT * From Contacts" Set oRS = CreateObject("ADODB.Recordset") oRS.Open sSQL, sConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText ' Check to make sure we received data. If oRS.EOF Then MsgBox "No records returned.", vbCritical Else sSQL = "UPDATE Contacts " & _ " SET Phone = 'None' " & _ "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'" oRS.ActiveConnection.Execute sSQL sSQL = "SELECT * From Contacts" oRS.ActiveConnection.Execute sSQL ary = oRS.getrows MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0) End If oRS.Close Set oRS = Nothing End Sub -- HTH RP "fred" wrote in message ... 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 |
#3
|
|||
|
|||
Excel not Access
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 "Use the Query Wizard to create/edit queries" is UNCHECKED...this is because the Query Wiz is stupid & 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 "see" 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 http://www.tushar-mehta.com/excel/ne...cel/index.html Hope this helps! LeAnne fred wrote: 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 |
#4
|
|||
|
|||
Excel not Access
I add also a few examples on my site yesterday
Will add more soon http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... 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("ADODB.Connection") oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _ " VALUES ('Bob','Phillips','01202 345678','me')" 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 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "SELECT * From Contacts" Set oRS = CreateObject("ADODB.Recordset") 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) & " " & ary(1, 0) & ", " & ary(2, 0) Else MsgBox "No records returned.", 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 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "SELECT * From Contacts" Set oRS = CreateObject("ADODB.Recordset") oRS.Open sSQL, sConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText ' Check to make sure we received data. If oRS.EOF Then MsgBox "No records returned.", vbCritical Else sSQL = "UPDATE Contacts " & _ " SET Phone = 'None' " & _ "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'" oRS.ActiveConnection.Execute sSQL sSQL = "SELECT * From Contacts" oRS.ActiveConnection.Execute sSQL ary = oRS.getrows MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0) End If oRS.Close Set oRS = Nothing End Sub -- HTH RP "fred" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | MEGTOM | New Users | 5 | October 27th, 2005 03:06 AM |
Using Excel charts in Access 2003??? | PZStraube | General Discussion | 0 | March 1st, 2005 01:02 PM |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
Data from Excel to Access | Database Design | 2 | August 20th, 2004 12:53 PM |