The Psychiatrists Chair

[gingerafro]

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]

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]

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

 

[gingerafro]

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!