Hi,
I have a nicely formatted report in Access (with headers, shading, column widths, etc.) and the customer would like to be able to export the report into a similarly formatted Excel file. Is this possible?
Hi,
I have a nicely formatted report in Access (with headers, shading, column widths, etc.) and the customer would like to be able to export the report into a similarly formatted Excel file. Is this possible?
docmd.OutputTo acOutputQuery ,"qsMyQuery" ,acFormatXLS, sFileName
or
docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12, sQry, sFile, true, sSheetName
you can put it in a macro.
just pick items from the box: choose IMPORT/EXPORT DATA
or put the code in a BUTTON CLICK event.
I can't get it to work. Obviously I'm not doing it right. I'm currently using the ExportWithFormatting action macro....but obviously there's no formatting. I don't see the IMPORT/EXPORT DATA in the box you described.
I also tried via a button click event, but I don't know where to put my DB-specific info into the script.
Like I said, I'm pretty new to Access.
then use the OUTPUTto, it does little formatting (headers only)
youd have to do some programming if you want formats in various columns.
this is done after the export, then code would open the file, and run formatting on various columns/cells.
Is there a way to export the data then just either automatically or click a button to run a macro to format it in Excel? I can do the macro but how to you get it to export the data into a file where the macro is already there?
Or I can just format a template and export the data into it? Not sure which method is easier.....
You can either, I personally do all my automated excel formatting of exports from Access, it's a bit of a learning curve if you are unfamiliar with VBA.
There are instances where if some really complex Excel workings are required it is easier to use a template, but your requirements don't sound like that.
I'm afraid you certainly won't achieve anything beyond a basic export without using VBA.
Even just outputting the query output into a table is not achievable in a macro.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Turn the select query into an action query and a macro can run it?Even just outputting the query output into a table is not achievable in a macro.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Ok, well that's good to know. Yeah, unfortunately I don't know VBA very well. Is it possible to find a sample script somewhere? All I want to do is change the font, shade the headers, etc.You can either, I personally do all my automated excel formatting of exports from Access, it's a bit of a learning curve if you are unfamiliar with VBA.
There are instances where if some really complex Excel workings are required it is easier to use a template, but your requirements don't sound like that.
I'm afraid you certainly won't achieve anything beyond a basic export without using VBA.
Even just outputting the query output into a table is not achievable in a macro.
Use the Excel macro recorder to do the basics of what you want, then amend to suit.
That gives you a head start.?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
The routine below takes an excel file path and sheet name and will format the cells used from A1 into a formatted table range.
You can add an option to not leave the file open after it is formatted
You can see various other style and formatting options commented out, you can apply them as you see fit.
Code:Public Sub XLFormatTable(sFile As String, sSheet As String, Optional bOpen As Boolean = True) On Error GoTo XLFormatTable_Error ' Late binding to avoid reference: Dim xlApp As Object 'Excel.Application Dim xlWB As Object 'Workbook Dim xlWS As Object 'Worksheet Dim tbl As Object Dim rng As Object Debug.Print sFile, sSheet ' Create the instance of Excel Set xlApp = CreateObject("Excel.Application") xlApp.Visible = bOpen Set xlWB = xlApp.Workbooks.Open(sFile) 'Debug.Print xlWB.Name Set xlWS = xlWB.worksheets(sSheet) ' Format the sheet xlApp.range("A1").Select With xlWS ' With .UsedRange ' .borders.LineStyle = xlContinuous ' .borders.ColorIndex = 0 ' .borders.TintAndShade = 0 ' .borders.Weight = xlThin ' End With ' ' 'format header 90 degree ' With .Range("i1:y1") ' .HorizontalAlignment = xlCenter ' .VerticalAlignment = xlBottom ' .WrapText = False ' .Orientation = 90 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With ' .UsedRange.Rows.RowHeight = 15 ' .UsedRange.Columns.AutoFit With xlWB.Sheets(sSheet) Set rng = .Cells(1, 1).CurrentRegion End With Set tbl = xlWS.ListObjects.Add(xlSrcRange, rng, , xlYes) tbl.TableStyle = "TableStyleMedium2" tbl.ShowTotals = False xlWS.Cells.EntireColumn.AutoFit End With xlWB.Save If Not bOpen Then xlApp.Workbooks.Close Set xlApp = Nothing Else xlApp.ActiveWindow.WindowState = xlMaximized End If On Error GoTo 0 Exit Sub XLFormatTable_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure XLFormatTable, line " & Erl & "." End Sub
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
I appreciate all the help, but these solutions are too advanced for me. Right now I'm trying to do something simple. I created a template with the formatting I want. Then I use the ExportWithFormatting macro to export the data into that template file. Problem is, it overwrites the formatting. Can that be done?