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
0
Fred1060 (110)
1/17/2006 6:30:03 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
1172 Views

Similar Articles

[PageSpeed] 32

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" <fred@discussions.microsoft.com> wrote in message
news:5F05357F-A927-4F46-8FB6-A8E68F2F995C@microsoft.com...
> 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


0
bob.phillips1 (6510)
1/17/2006 8:14:19 AM
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/newsgroups/rdbms_in_excel/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
0
nospam2791 (369)
1/17/2006 3:41:09 PM
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" <bob.phillips@notheretiscali.co.uk> wrote in message news:uCTEH4zGGHA.3532@TK2MSFTNGP14.phx.gbl...
> 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" <fred@discussions.microsoft.com> wrote in message
> news:5F05357F-A927-4F46-8FB6-A8E68F2F995C@microsoft.com...
>> 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
>
> 


0
rondebruin (3790)
1/18/2006 6:03:26 PM
Reply:

Similar Artilces:

a sample question about excel VBA
How can I make msgbox like this? �� there are X sheets of this workbook the first page name is : Sheets(1).Name the secod page name is : Sheets(2).Name �� until to the last page the X page mane is : Sheets(X).Name How can I write this module? Sub WorksheetNames() Dim i As Long Dim strAnswer As String strAnswer = _ "There are " & Worksheets.Count & _ " sheets of this workbook" For i = 1 To Worksheets.Count strAnswer = strAnswer & vbCr & _ "Sheet " & i & " : " & _...

Excel Menus Greyed out
This only happens in Excel XP and a re-install and/or repair don't seem to fix it. All other Office apps are fine. Anyone seen this? Remedy?! ...

Default Line Weight in Excel
How do I change the default setting for the "Line Weight" when creating charts - at the moment all of my graphs are created with a very thin line weight & I would like the standard to be thicker.... You can't change the default line weight. However, you can create a custom chart with the heavier line weights and then use that custom chart instead of Excel's defaults. To do so, click once on your finished chart with the heavier line weights. Go to the standard toolbar and select Chart -> Chart Type. In the Chart Type dialog box, select the Custom Types tab. ...

Excel cannot open email program.
I often use "file.. send to... mail recipient" to send a spreadsheet as text in the body of an e-mail. I prefer that to sending excel as an attachment, since my recipients may not have excel and usually don't need to see formulas. On my new computer however I get the message "excel could not open the e-mail program" if I try the first option above. Both excel 2002 and outlook 2002 are working fine. I "repaired" office just to be sure. Still doesn't work. Only difference is that the computer came with a trial version of office 2003 which I don...

GETPIVOTDATA and Date-type field (Excel 2007)
Data Sample *************** Trip 0346210980 Statement Date 3/24/2010 Description STARBUCKS Date 03/08/2010 LocCurrAmt 3.47 Formula: =GETPIVOTDATA("LocCurrAmt",A6,"Statement Date",DATE(2010,3,24)) Problem: I keep getting a #REF error. When I evaluate the error it's always with the DATE function. Steps I've taken: 1. Confirmed that my date is a number and not text. 2. Tried using the date serial number (40261) instead of the DATE function. 3. Confirmed the GETPIVOTDATA works...

Excel 2003
Whenever a colleague updates a spreadsheet I've created it comes back 10MBytes large whereas it was originally only 50KBytes. It looks to me as if the colleague accidentally adds tens or hundreds of thousands of empty rows. How can I delete all rows below a certain row. Delete does not work. Copying the real rows into a new worksheet loses the formatting. When I apply the formatting from the big spreadsheet (paste formatting) I end up with hundreds of thousands of rows again :-( Any suggestions? Tom Selecting all the rows below your actual data. Right-click, delete cells. (this is...

access / excel link
i know that i can link excel tables to an access database. can i link an access table to a spreadsheet? -- TomMurray hi Tom, tom wrote: > i know that i can link excel tables to an access database. can i link an > access table to a spreadsheet? You may use Data/Get External Data, but this not the same as linked table. Depending on your needs, you may consider using a VBA procedure (macro) to read the data with a DAO.Recordset and copy it manually into to your cells - you can copy also a complete recordset, see CopyFromRecordset in Excel. mfG --> stefan <-- thanks stefa...

Access Licensing (distribution question)
Hi New to this group, let me introduce myself quickly. Im Jamie Brown and a programmer from the uk. Nice to meet you all. I have developed a system, front end Access 2003, backend SQL 2000. I currently distribute the front end (ade) to over 50 pcs, each one currently has Access 2003 installed on it. My question is, is there a way of compiling the front end so I dont actually need Access installed on the 50 pcs? I have a license for development on my PC but so I really require a further 50 licenses? The bosses are keen to cut costs (arnt they all) and this is one area I can see potential. ...

How do I assign a shortcut key to an existing macro in Excel 2003.
ntahall, Alt+F8 to bring up the choose macro box, select your macro, options, assign a shortcut from there -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "ntahall" <ntahall@discussions.microsoft.com> wrote in message news:21B6102C-4B88-4131-A9AC-C48879896A17@microsoft.com... > ...

Creating the occurrence frequency chart in Excel
I had a need to put a series of numbers into buckets of size 5. I created the following formula, and "dragged it down" =A1 + IF(MOD(A1,5), 5-MOD(A1,5), 0) I am sure that there is a better way of doing this. Can someone suggest it? Also, I wanted to come up with an occurrence frequency chart for my data. For example if I have numbers 4, 9, 13, 13, 17, 19, 19, 19, then I would like the occurrence frequency chart to plot the following informatiuon in a 2D chart: 0-4: 1 5-9: 2 10-14:2 15-30: 3 Thanks, Song Hi: This will work out the buckets for you. Paste it into the first cel...

Conditional Opening of Excel file
Hi, I have a complex workbook that relies on various SUMIF's from othe reference workbooks. If these aren't open, then obviously the formula don't work. As it's for someone else; I want a piece of VB so that when they *open the main workbook, it checks to see if the ref file is open, and i not, it automatically opens it for them. A bit like this sub worksheetopen '(Not sure where this should go in parent book) if workbook("Referencefile.xls") is NOT open then workbook("referencefile.xls").open endif endsub Any ideas folks?:confused -- Message p...

Access mouse wheel does not scroll in datasheet view of table, Win
I am aware of the issues with mouse wheel scrolling of forms. But those who have posted about those invariably say that mouse wheel scrolling works in datasheet view. For me it does not. On Win XP Access 2003, if I opened a table in datasheet view it initially would not scroll using the mouse wheel. But if one scrolled down a bit using the righthand scroll bar, thereafter the mouse wheel worked to scroll the records. I've just moved to Win 7 64-bit and have Access 2003 and 2007 installed. It won't scroll a table in datasheet view using the mouse wheel with either ve...

Suppressing zeros after the decimal point
How can I force zeros to display following a decimal point in Excel 2002? Whenever I type a number, such as 3.200, Excel drops the zeros and only enters 3.2 into the cell. Is there a setting that can be changed to fix this? (Using the "0.00" custom format seems to help, but if I have another number, such as 4.70, which only needs one zero to display, then I need another custom format for that one, and the next one, etc.) If anyone has a simpler suggestion, I'd appreciate it. Thanks! Hi AFAIK you can't achieve this with a custom format. Either you have three decimals or...

web access subject problem
Hello everybody, I'm working at home at this time and I'm reading my e- mails from my job's server by outlook web access. But I can't read messages which has subject with dot or any non- alphabetic characters (.&! etc.) Could anybody help to me ? thx Gyuri ...

how many characters i can put into one cell in Excel
If i typed a whole paragraph into one cell of Excel, only a part of para show up in that cell. I tried wrap text, increase the height of the column. Nothing happen. Any one have idea about it or there are some limit to view in Excel in one cell Hi Search for 'specifications' in Help: It says '32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.' -- Andy. "Gary" <Gary@discussions.microsoft.com> wrote in message news:747E6CFE-88D9-4F4C-950E-56F0885C3096@microsoft.com... > If i typed a whole paragraph into one cell o...

Can an Excel file be converted into Visio?
Does anyone know if this is even possible? Is there a converter pack or a website that would illustrate this process? Thanks, Jim ...

excel charts look drastically different on different computers
When we open excel charts on different computers in our office the formatting looks drastically different (for example, data labels and arrows on charts are in different locations). This is a huge problem because it means that the formatting has to be fixed each time someone opens an excel chart. This was never a problem until recently when some of our staff got new computers and for a short time they were using Office 2007 (everyone else in the office was using Office 2003). We've switched everyone back to Office 2003, but the problem with excel charts persists. When u ad...

excel 2000 convert to excel 2003
Hi I would like to move current excell 2000 spread sheets and open them in 2003 excel. Please help in any way. I have copied my excell 2000 data onto a floppy however it will not open in Excel 2003 XL2000 and XL2003 share exactly the same file format and so should open just fine in either. However, you should never open XL files on floppies - XL uses temporary files, which may exceed the capacity of the floppy. Instead, copy the file from the floppy to your HD before opening. In article <72864281-728A-4B27-8317-A760E6440BB2@microsoft.com>, cityof <cityof@discussions.microsoft...

Font size changes in Excel 2003 charts when re-opened
For certain charts that I create within Excel 2003 (usually bar charts), I use a font size of 8 (eg for data labels, axis labels, and scale). If I close the file and then re-open it, all these fonts will have become size one (title and legend are not effected). Oddly, if I have created a bar chart and made several copies of it within a worksheet, it will only be the original and not the copies that are affected by this. If anyone can shed any light on why this may be happening and how it could be fixed, I would really appreciate it. Lucie ...

Using VBA to change Excel layouts
Our company has designed 4 different spreadsheet designs. The same data gets stored in any of the 4 spreadsheet layouts....the only difference is that some spreadsheets use different colors, have different fonts and cell sizes and etc... I thought it would be nice to create just ONE spreadsheet that contains a combo-box or list-box or drop-down box which allows the user to select 1 of the 4 layout choices, then I want VBA to programattically setup the spreadsheet layout according to the users choice. Does that make sense? Does anybody know any good ways to implement something lik...

Excel Crash
Multiple users here have this same problem that whenever they open a particular Excel file, it gives them a "Send Report Error." Weird thing about it is it doesnt happen in all their Excel files, only happens to some of them. Also tried opening the very same file/s they are having problems with on my machine and it worked fine. I have patched Office (SP2), updated Acrobat Reader, updated signatures, uninstalled/reinstalled Office, and done all the possible troubleshooting on these machines but still error occurs. Any help appreciated, thanks! Isolate the problem as best yo...

Error on Importing Access Query
I am trying to import Access Query to Excel so I can generate pivot table and chart with live data. However importing external data reads column header only, no values. I have done this before, a while ago. I don't understand why this is not working. Can somebody help me? ...

why can I not format a secondary axis in excel x?
Despite being able to graph data on a secondary axis in Microsoft Excel X, I have been unable to format said axis (ie make it start from a value other than zero). No amount of trying to click on and select the axis or the values has helped. Are you saying that double clicking does not bring up the Format dialog box for the axis? Try tapping the 'navigation (arrow) keys while watching the Name box. When it reads Secondary Value.... use the Format menu item to open the dialog box and then open the Scale tab to set the Min and Max values best wishes Bernard "mackie99" <...

Delegate constructor compiles in VB.NET, but not in Excel VBA
I have created a DLL callback through a delegate function. It works fine when the client is C#, or VB.NET. I can't get it to compile in Excel VBA. I have this problem both in Excel 2003 and Excel 2007. The DLL references setting is ok, because when I take the reference out the compiler errors out on RemoteProcess. What am I missing? The snippets below are the dll code, the working vb.net client code, and the failing vba code. =========================================== The remoteprocess class is compiled as dll: =========================================== Public Cl...

How can I type more than one line of text into a cell in Excel?
I am using Excel to organize contact information, names, addresses, phone numbers, etc. I would like all of this information in one cell and in the standard format of: name address phone etc. I do not know how to make Excel accept more than one line of text, unless I cut and paste it from a Word document. Could someone please tell me how to format the cells so that more than one line of text can be accepted in a cell? Hi use ALT+ENTER for inserting line breaks -- Regards Frank Kabel Frankfurt, Germany "watermark" <watermark@discussions.microsoft.com> schrieb im Newsb...