Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136

    Exporting to Excel

    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?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    docmd.OutputTo acOutputQuery ,"qsMyQuery" ,acFormatXLS, sFileName

    or


    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12, sQry, sFile, true, sSheetName

  3. #3
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by ranman256 View Post
    docmd.OutputTo acOutputQuery ,"qsMyQuery" ,acFormatXLS, sFileName

    or


    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12, sQry, sFile, true, sSheetName

    Sorry, I'm fairly new to Access. Where does this script go?

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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.

  5. #5
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by ranman256 View Post
    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.

  6. #6
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see the IMPORT/EXPORT DATA in the box you described.
    Did you poke around?
    Click image for larger version. 

Name:	ImportExport.jpg 
Views:	43 
Size:	20.4 KB 
ID:	43904
    Hovering over one of the macro input fields opens a help dialog with a "Tell me more" link.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by ranman256 View Post
    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.....

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Even just outputting the query output into a table is not achievable in a macro.
    Turn the select query into an action query and a macro can run it?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by Minty View Post
    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.
    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.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by matey56 View Post
    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.
    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

  13. #13
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Micron View Post
    Turn the select query into an action query and a macro can run it?
    Sorry @Micron I meant a formatted table range in Excel.
    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 ↓↓

  14. #14
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  15. #15
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    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?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting to Excel
    By dccjr3927 in forum Programming
    Replies: 2
    Last Post: 05-29-2019, 02:18 PM
  2. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  3. Exporting to excel
    By Ray67 in forum Import/Export Data
    Replies: 8
    Last Post: 07-26-2012, 10:24 AM
  4. Exporting to Excel
    By RAMSES955i in forum Import/Export Data
    Replies: 1
    Last Post: 07-04-2012, 02:06 PM
  5. exporting to Excel
    By johnririe in forum Import/Export Data
    Replies: 6
    Last Post: 08-23-2011, 10:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums