Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Collins is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    107

    Source Code for Formatting an Excel spreadsheet when Exporting a Query

    I found some source code online that will allow me to format an excel spread sheet when I export a query or table from access by pressing a button. It allows me to set the width of columns, add headers and footers, bold text and set fonts. All kinds of stuff.



    It's exactly what I need for my database if I could only figure out how to get the code to use my queries.

    I know nothing about VBA... At all...

    Can someone tell me how to use source code? Or is it more complicated than that?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Somewhere at the beginning this mystery code probably exported to Excel using one of several available methods. That's where you would use your queries. Then it likely used automation to format the spreadsheet. Automation isn't rocket science but it isn't beginner level either.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    modify as needed....

    Code:
    '---------------
    Public Sub ExportXLFile()
    '---------------
    Dim xl As Excel.Application
    dim vFile
    
    vFile = "c:\folder\MyFile.xls"
    
      'export the file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsQuery", vFile, True
    
      'now edit/format the data
    Set xl = CreateObject("excel.application")
    With xl
        .Visible = True
        .Workbooks.Open vFile 
         
           'format all headers
        .Cells.Select
        .Cells.EntireColumn.AutoFit
    
    
            'colorize the headers
        .Range("A1").Select
        .Range(.Selection, .Selection.End(xlToRight)).Select
        With .Selection.Interior
            .Color = vbYellow
            .Pattern = xlSolid
        End With
        .Range("A1").Select
        
        .activeworkbook.save
    End With
    Set xl = Nothing
    End Sub

  4. #4
    Collins is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    107
    Quote Originally Posted by ranman256 View Post
    modify as needed....

    Code:
    '---------------
    Public Sub ExportXLFile()
    '---------------
    Dim xl As Excel.Application
    dim vFile
    
    vFile = "c:\folder\MyFile.xls" I entered my path here... 
    
      'export the file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsQuery", and the name of my query here. vFile, True
    
      'now edit/format the data
    Set xl = CreateObject("excel.application")
    With xl
        .Visible = True
        .Workbooks.Open vFile 
         
           'format all headers
        .Cells.Select
        .Cells.EntireColumn.AutoFit
    
    
            'colorize the headers
        .Range("A1").Select
        .Range(.Selection, .Selection.End(xlToRight)).Select
        With .Selection.Interior
            .Color = vbYellow
            .Pattern = xlSolid
        End With
        .Range("A1").Select
        
        .activeworkbook.save
    End With
    Set xl = Nothing
    End Sub
    See red...

    I also put this code in the event of button on click. Was that a bad idea?

    Nothing happened. No error. No file... nothing...

    Plus I need an area where I can format the cells. I'll try to post the source code so you guys can read it.

    Suggestions?

  5. #5
    Collins is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    107
    Private Sub cmdTransfer_Click()
    On Error GoTo SubError

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim SQL As String
    Dim rs1 As DAO.Recordset
    Dim i As Integer

    'Show user work is being performed
    DoCmd.Hourglass (True)

    '*********************************************
    ' RETRIEVE DATA
    '*********************************************
    'SQL statement to retrieve data from database
    SQL = "SELECT PartNo, PartName, Price, SalePrice, " & _
    "(Price - SalePrice) / Price AS Discount " & _
    "FROM Parts " & _
    "ORDER BY PartNo "

    'Execute query and populate recordset
    Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

    'If no data, don't bother opening Excel, just quit
    If rs1.RecordCount = 0 Then
    MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
    GoTo SubExit
    End If

    '*********************************************
    ' BUILD SPREADSHEET
    '*********************************************
    'Create an instance of Excel and start building a spreadsheet

    'Early Binding
    Set xlApp = Excel.Application

    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)

    With xlSheet
    .Name = "Discount"
    .Cells.Font.Name = "Calibri"
    .Cells.Font.Size = 11

    'Set column widths
    .Columns("A").ColumnWidth = 13
    .Columns("B").ColumnWidth = 25
    .Columns("C").ColumnWidth = 10
    .Columns("D").ColumnWidth = 10
    .Columns("E").ColumnWidth = 2
    .Columns("F").ColumnWidth = 10

    'Format columns
    .Columns("A").NumberFormat = "@"
    .Columns("C").NumberFormat = "$#,##0.00;-$#,##0.00"
    .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"
    .Columns("F").NumberFormat = "#,##0.0#%;-#,##0.0#%"

    'build report heading
    .Range("A1", "F1").Merge
    .Range("A2", "F2").Merge
    .Range("A1").HorizontalAlignment = xlCenter
    .Range("A2").HorizontalAlignment = xlCenter
    .Range("A1").Cells.Font.Bold = True
    .Range("A2").Cells.Font.Bold = True
    .Range("A1").Cells.Font.Name = "Cambria"
    .Range("A2").Cells.Font.Name = "Cambria"
    .Range("A1").Cells.Font.Size = 14
    .Range("A2").Cells.Font.Size = 12

    .Range("A1").Value = "Discount Listing"
    .Range("A2").Value = Date

    'build column headings
    .Range("A4").Value = "Part Number"
    .Range("B4").Value = "Part Name"
    .Range("C4").Value = "Price"
    .Range("D4").Value = "Sale Price"
    .Range("F4").Value = "Discount"

    'Format Column Headings
    .Range("A4:F4").HorizontalAlignment = xlCenter
    .Range("A4:F4").Cells.Font.Bold = True

    'provide initial value to row counter
    i = 5
    'Loop through recordset and copy data from recordset to sheet
    Do While Not rs1.EOF

    .Range("A" & i).Value = Nz(rs1!PartNo, "")
    .Range("B" & i).Value = Nz(rs1!PartName, "")
    .Range("C" & i).Value = Nz(rs1!Price, 0)
    .Range("D" & i).Value = Nz(rs1!SalePrice, 0)
    .Range("F" & i).Value = Nz(rs1!Discount, 0)

    i = i + 1
    rs1.MoveNext

    Loop

    i = i + 2
    'Create footnote just for fun
    .Range("A" & i, "F" & i).Merge
    .Range("A" & i).Value = "* Caveat Emptor! Discounts can change at any time!"
    .Range("A" & i).Cells.Font.Size = 10
    .Range("A" & i).Characters(30, 10).Font.Bold = True
    .Range("A" & i).Characters(30, 10).Font.Italic = True
    .Range("A" & i).Characters(30, 10).Font.Color = vbRed

    End With


    SubExit:
    On Error Resume Next

    DoCmd.Hourglass False
    xlApp.Visible = True
    rs1.Close
    Set rs1 = Nothing

    Exit Sub

    SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
    "An error occurred"
    GoTo SubExit

    End Sub


    It said in the video that the code would allow me to add spaces on the column headings, bold them, center them and define column widths. Said I could add columns and have cells do calculations. A lot of the area under "BUILD SPREADSHEET" I get. Not all but most. If I can figure out how to hook any table or query into this code, it will blow things wide open.

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    thats all it takes. BUT be sure your ACCESS has Excel checked in the
    VBE menu, TOOLS, REFERENCES.

    check EXCEL ver x.0
    then run it.

  7. #7
    Collins is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    107
    Quote Originally Posted by ranman256 View Post
    thats all it takes. BUT be sure your ACCESS has Excel checked in the
    VBE menu, TOOLS, REFERENCES.

    check EXCEL ver x.0
    then run it.
    Could it be Microsoft Excel 14.0 Object Library?

    My only other options are:
    Excel8
    Excel8Fromulas
    Excel8Interface
    Excel8Recourds

    Also do I put this code in a button event?

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If you are going to do this
    Dim xlApp As Excel.Application

    The reference you want is Microsoft Excel 14.0 Object Library. The 14.0 is the version of Excel you have installed on the current PC.

  9. #9
    Collins is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    107
    It is not creating a spreadsheet...

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Collins View Post
    It is not creating a spreadsheet...
    Okay ......

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why export to Excel and then have to do all that formatting? Why not Access report?

    Step debug. Refer to link at bottom of my post for debugging guidelines. Comment out the On Error GoTo error handler line so you can debug.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Collins is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    107
    Quote Originally Posted by June7 View Post
    Why export to Excel and then have to do all that formatting? Why not Access report?

    Step debug. Refer to link at bottom of my post for debugging guidelines. Comment out the On Error GoTo error handler line so you can debug.
    English June... Remember I have no clue what I'm doing... You gotta spell it out... But I'll check the link for sure...

  13. #13
    Collins is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    107
    Also I forgot to add the end user would like it in an excel spreadsheet.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    An understanding of debugging techniques is critical if you want to be an effective developer. Review the debugging link and then let us know what's still fuzzy.

    Any line in VBA preceded with an apostrophe is a comment and not executable code. So to disable a line of code, place an apostrophe in front.

    Reports can be exported and some of the formatting you want will be retained.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To address this:

    Quote Originally Posted by Collins View Post
    If I can figure out how to hook any table or query into this code, it will blow things wide open.
    The part that gets data is:

    'SQL statement to retrieve data from database
    SQL = "SELECT PartNo, PartName, Price, SalePrice, " & _
    "(Price - SalePrice) / Price AS Discount " & _
    "FROM Parts " & _
    "ORDER BY PartNo "

    So you can replace that SQL with your own. In other words, your fields in the SELECT clause and your table or query instead of Parts. At the very simplest:

    SQL = "SELECT * FROM YourTableOrQuery"

    which would return all fields and all records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 08-13-2014, 11:07 AM
  2. Replies: 1
    Last Post: 02-19-2014, 11:26 AM
  3. Replies: 1
    Last Post: 11-16-2013, 09:01 AM
  4. Replies: 3
    Last Post: 09-08-2013, 08:50 AM
  5. Exporting To Excel And Formatting, Office 2010
    By DazJWood in forum Programming
    Replies: 2
    Last Post: 11-23-2011, 08:35 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