Formatted Spreadsheet Export (1 Viewer)

[Access_Help]

Hello,

I am looking for a simple script that I can manipulate to do the following:

- Opens a new spreadsheet
- Exports data from a query to it
- Wrap text the headings and apply formatting
- Apply 3 conditional formatting rules on the data

Any help would be greatly appreciated.

P.S - I am using this as an alternative to creating a dynamic report based on a crosstab.

 

[Uncle Gizmo]

See my implementation of Bob Larson's code Here:-

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table OP "avalve" asked this question on Access World Forums:- "Howdy! Let me make a small scenario. Let's say I have a table with fields CarMake/CarModel/Year/Color. What I am trying to do is to export the table to a single Excel Workbook with each CarMake having their...

www.niftyaccess.com

Everything you mentioned, except the conditional formatting...

 

[Uncle Gizmo]

Direct link to Bob Larson"s Code:-

Export A Table Or Query To Excel – Specific Worksheet on Specific Workbook | BTAB Development

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don't already have one. Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String) '...

btabdevelopment.com

 

[Uncle Gizmo]

This is a link to the methods of manipulating Excel I used before I found Bob Larsons code:-

Update Excel from MS Access - Nifty Access

Update Excel from MS Access Before I found Bob's excellent code on the BTAB Development Web Site... (To see it Click The Button Below) ... I relied on the two links from Microsoft which you can find below.One link demonstrates the automation method to transfer data cell by cell, which is fine...

www.niftyaccess.com

However it doesn't demonstrate any formatting options.

 

[Uncle Gizmo]

Here is another project where I used Bob Larson code:-

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

Notice that the field names, the headings are bold, in other words I worked out how to format the text.

 

[Access_Help]

How to I assign the function to a button to execute it?

I assigned it to a macro but it came up with an error message.

 

[Uncle Gizmo]

How to I assign the function to a button to execute it?

There isn't a function that you can assign to a button. You will have to build it first.

I assigned it to a macro but it came up with an error message.

You're not going to be able to do this with macros you're going to need to use VBA.

 

[Uncle Gizmo]

How to I assign the function to a button to execute it?

You call this:-

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel

Full instructions on Bob's website. ...

But for bolding the headings you will need to write VBA....

 

[Uncle Gizmo]

See this Video at time index 3.18 for instructions on how to create a command button.

 

[Access_Help]

Sorry guys, this is my first time with Functions/Modules.

I have created a module and saved this code to it:

Code Tags Added by UG

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strFilePath is the name and path of the file you want to send this data into.

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strPath As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler

    strPath = strFilePath

    Set rst = CurrentDb.OpenRecordset(strTQName)

    Set ApXL = CreateObject("Excel.Application")

    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True

    Set xlWSh = xlWBk.Worksheets(strSheetName)

    xlWsh.Activate

    xlWSh.Range("A1").Select

    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next

    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst

    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting.  You can comment out or delete
    ' any of this that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With

    ApXL.Selection.Font.Bold = True

    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With

    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select

    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select

    rst.Close

    Set rst = Nothing

Exit_SendTQ2XLWbSheet:
    Exit Function

err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendTQ2XLWbSheet   
End Function

I have created a command button and I want to pass the arguments from VBA:

Call SendTQ2Excel("QueryName", "sheet 1", "")

I don't have a path to a file, I want to generate a new file everytime the user clicks on the the export.

Am I going the right way??

 

[Uncle Gizmo]

I used Bobs code as a starting point for my project, however I made substantial changes to it.

One of the changes I made was to remove the necessity of supplying a UNC and file name.

From the link below, look at the bottom of the page and you will find my adapted version of Bob's code. I think it gives you what you want regarding not having to provide a filename and path.

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table OP "avalve" asked this question on Access World Forums:- "Howdy! Let me make a small scenario. Let's say I have a table with fields CarMake/CarModel/Year/Color. What I am trying to do is to export the table to a single Excel Workbook with each CarMake having their...

www.niftyaccess.com

 

[Uncle Gizmo]

The difference between my code and Bob's code regarding opening an existing Excel file and creating a new Excel file is here:-

'Bob's Code:-
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)

'Uncle's Code:-
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add

A workbook is what most people would refer to as an Excel spreadsheet. The correct name for an Excel file is a "Workbook". A workbook can contain a number of sheets, and always a minimum of 1... For further information see here:- Difference between a workbook, worksheet, and spreadsheet

So once you've done a bit of research and understand the terminology, it becomes clear that Bob's code is opening a workbook at the path provided, and my version is opening a new workbook.