# The Psychiatrists Chair



## gingerafro (Feb 10, 2006)

I need some help.  I've been using Excel now for about 7 years.  Not as long as many, but it is over a quarter of my life.  I feel that I've become reasonably accomplished in formulas, pivot tables and programming.

The reason for this is that I used to do all of my firm's statsistics in excel.  Over the last year I've discovered Access and now become proficient using that.  

Great for the company and my time, but I feel like I'm neglecting Excel.  We still have a casual thing going, but I need to make my relationship more fulfilling. I am concerned that soon we may suffer from irreconcilable differences.  What can I do?


----------



## Andrew Fergus (Feb 10, 2006)

Have you discussed this with Excel?  How do they feel about it?  How do you feel discussing this very personal problem on an international forum?

Having seen the light of Access you won't regret turning away from your old faithful.  Think of Excel as a comfort blanket.  Something very handy and comforting in your youth and something you think you simply can't do without (if you are that way inclined), but as you have progressed in your knowledge of all things Microsoft, you need to face the truth.  Repeat after me : "I have outgrown Excel".  This is normal and you are not responsible for Excel's feelings.  In any case Excel has moved on and has another poor soul in its' clutches.

I recommend a multi-table crosstab query to relieve you of your fascination and unrequited infatuation with Excel.  How does that make you feel?


----------



## gingerafro (Feb 10, 2006)

Excel has been really good about it all.  Despite the odd strop when I try to adjust a big pivot table, its always around when I need it.

I don't think that I will completely divorce Excel because it will cost me a lot, but keep it on the side to make things look nice every now and again.  I may even try getting Excel and Access to talk to each other.  In time they may become friends and get it on.  Is this geeting weird now?

I think you are right that, in general, I have outgrown Excel, but will continue to post on MrExcel (when I can figure out how to stay logged in for longer than 5 minutes!)

I feel better after constructing the multi table crosstab.  It truly is a joy to behold.  

Having said all of the above.  What happens when I make the move to SQL Server.  I not sure I can cope with all three at once...


----------



## Andrew Fergus (Feb 10, 2006)

I wouldn't recommend you get Excel and Access to talk to eachother.  Whilst it seems logical and entirely possible, they are merely fooling you into a false sense of security.  It's either one or the other but to have both 'get it on' at the same time is delusional.  To cure you of this delusion I prescribe a multi-tab form with integrated subforms and a smattering of VBA.  However, that's not to say the old faithful can't be relied upon to keep the home fire burning.  But it's Access you take to the parties.  Try the Access forum sometime!  As for courting SQL....that's a whole new topic!


----------



## gingerafro (Feb 10, 2006)

Hi Andrew.
Thank you for your pearls of wisdom and indulging me in this topic.  I feel that by talking about my software generated anxiety, I have gone a long way to relieving the burden.

Sound advice on the access front, but I've not yet explored vba, scripting and forms fully.  I like what I see though, so will persevere.

I've had a look at the question of ethics poll in the lounge, and if this falls into the same category, then access must be unethical?


----------



## Richard Schollar (Feb 10, 2006)

Ging

You two-timing b*stard! Go back to your first *true* love.  

Excel is to Access as Angelina Jolie is to Nora Batty.    

I hope it isn't too late to change your miscreant behaviour...

A _faithful_ Excel believer   :wink: 

Richard


----------



## gingerafro (Feb 10, 2006)

Hi Richard.

I consider myself to have been well and truly chastised!
In fairness, I have just been creating a macro to automate views in a pivot table for less-than-able users, along with a dynamic chart.

Its not particularly high-level, but is something that access can't do.

I now feel that my actions are less that of a two-timer and a more of someone with a harem.


----------



## Richard Schollar (Feb 10, 2006)

On a more serious note, I do think Access has its uses, but I much prefer to create presentational matter in Excel (even if the data being reported on comes from Access).

Richard


----------



## gingerafro (Feb 10, 2006)

Absolutely agree. 
We wouldn't take a spanner to knock in a nail would we?  It would probably work for small nails, but could damage the spanner for its intended use later.

I'm feeling in quite an analagous mood today.

I think that my original post was due to the fact that I used to open excel almost as soon as my pc was up and running, but find myself opening it several hours into the day after spending time in Access.  Plus I hadn't posted on here for a couple of weeks and so felt a bit rusty. I'll go back to the proper board now and see if there's someone I can help...


----------



## whiteghost (Mar 19, 2006)

NORA BATTY?


----------



## gingerafro (Mar 20, 2006)

que?
Is that a 'I think you are mental' comment or some Last of the Summer Wine joke that I am pleased not to understand.
explain yourself or I shall be forced to ask you about the cat...


----------



## Cbrine (Mar 20, 2006)

gingerafro,
  I've successfully used access and excel.  MSaccess query services are easy to use, as well as form building.  I used the reports a long time ago, but found that most users only had Excel, and we ended up running reports on access, doing screen prints and dumping them into excel for the user to veiw.  I've since changed my process so that I run the queries on access, and dump the data via VBA(Not a macro) using the excel object to an open instance of excel, format, add a file level password to the workbook, and save it under a specific name.  I've built an Access macro to do this.


```
Sub TransferData(QueryName As String, FileName As String, FilePath As String, Password As String)
'This subroutine was created to query information out to Excel File using the Excel.Object to do some formating and to add an Excel File Password on to the file.

Dim XL As Object
Dim WB As Object, WS As Object, Excel As Object
Dim FullPath As String
Dim Test As String
On Error Resume Next

'Check to See if selected path is C:\
If Mid(FilePath, 3) = "\" Then
    FullPath = FilePath & FileName
Else
    FullPath = FilePath & "\" & FileName
End If

'Delete any old files with same name and output to file
Kill FullPath
'DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, FullPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, QueryName, FullPath

'Build reference to Excel Objects and open sheet
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open (FullPath)
XL.Visible = False

'Format Spreadsheet and Close Excel
XL.DisplayAlerts = False

Set WB = XL.Workbooks(FileName)
Set WS = WB.Worksheets(XL.ActiveSheet.Name)
'MsgBox XL.ActiveSheet.Name

WB.Activate
WS.Cells.Select
WS.Cells.EntireColumn.AutoFit

'''''''''''''''''''''''''''''''''''''''''
With WS.Range("A1", WS.Range("A1").End(xlToRight))
    .Font.Bold = True
    .Interior.ColorIndex = 15
End With

'Auto size the cell sizes
WS.Cells.Select
XL.Selection.ColumnWidth = 30
WS.Cells.EntireColumn.AutoFit
WS.Cells.EntireRow.AutoFit

'Add new Borders
WS.Range("A1", WS.Cells.SpecialCells(xlCellTypeLastCell)).Borders.LineStyle = xlContinuous

'Change Center Header to Sheet Title Name and format it.
'Add Footer Information
'Setup Title Rows

With WS.PageSetup
    .CenterHeader = "&""Tahoma,Bold""&14 " & Mid(FileType, 2, 100)
    .LeftFooter = "&8&F"
    .RightFooter = "&8&P of &N  Produced on: " & Date
    .PrintTitleRows = "$1:$1"
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesTall = False
    .FitToPagesWide = 1
    WS.PageSetup.Orientation = xlLandscape
    WS.PageSetup.PaperSize = xlPaperLegal
    
End With
WB.SaveAs FullPath, , Password
WB.Close
XL.Quit
End Sub]
```

I've made some changes to the code to make it more flexible for you.  So if something bombs let me know.

HTH
Cal


----------



## whiteghost (Mar 22, 2006)

sorry  the cat has sworn me to silence and i was well behind the camera at the time (ergo nowhere near the cat)


----------



## gingerafro (Mar 23, 2006)

Hi Cal,

Thanks for your reply and code.  I've taught myself access over the last 12months and therefore solved only the problems that I needed to in order to get my job done.  Some of my work involves complex database theory (many to many table joins) and a lot doesn't (I've only recently discovered Xtab queries).

As such, I haven't got anywhere near VBA in access yet, but can understand what your macro is doing.  Once I work out how to incorporate it, I will certainly test the code and get back to you.

Thanks again for the advice!


----------

