Results 1 to 6 of 6
  1. 01-18-2019, 03:00 PM #1
    megan2478
    megan2478 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    2

    MS Access Excel Output - Formatting the excel file

    Hi -
    I need help with the code for a button - it will run this macro...the first part runs fine on its own where it exports the spreadsheets to a file with 4 tabs. I wanted the file to also be formatted. I ran a macro recorder in Excel to get the code and altered it shortly, but the debugger is giving me errors. PLEASE HELP

    Thank you for your help!
    Megan




    Private Sub Command136_Click()
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
    TableName:="APL Tracker Query", _
    FileName:="C:\Users\mfrey\Desktop\TRANSFER\Databas es\APL Database\Reports\APL Tracker (COMPLETE) " & Format(Date, "mm-dd-yyyy") & ".xlsx")
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
    TableName:="qryTrackerDrew", _
    FileName:="C:\Users\mfrey\Desktop\TRANSFER\Databas es\APL Database\Reports\APL Tracker (COMPLETE) " & Format(Date, "mm-dd-yyyy") & ".xlsx")
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
    TableName:="qryTrackerGreg", _
    FileName:="C:\Users\mfrey\Desktop\TRANSFER\Databas es\APL Database\Reports\APL Tracker (COMPLETE) " & Format(Date, "mm-dd-yyyy") & ".xlsx")
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
    TableName:="qryTrackerNelson", _
    FileName:="C:\Users\mfrey\Desktop\TRANSFER\Databas es\APL Database\Reports\APL Tracker (COMPLETE) " & Format(Date, "mm-dd-yyyy") & ".xlsx")




    With Selection.Font
    .Name = "Tahoma"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
    .Name = "Tahoma"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    With Selection
    .HorizontalAlignment = xlGeneral
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Columns("A:E").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Columns("G:H").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    Worksheet.Range("G:H,S:V,W:W,X1,X:X,O:O,N:N,R:R"). Select
    Worksheet.Range("R1").Activate
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Range("N1").Select
    ActiveCell.FormulaR1C1 = "Config."
    Worksheet.Range("N2").Select
    Worksheet.Columns("K:K").ColumnWidth = 32.14
    Worksheet.Columns("J:J").ColumnWidth = 24.29
    Worksheet.Columns("I:I").ColumnWidth = 17.14
    Worksheet.Columns("F:F").ColumnWidth = 15.57
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    Worksheet.Columns("L:M").Select
    Selection.ColumnWidth = 13.43
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Columns("Q:Q").ColumnWidth = 26
    Worksheet.Columns("P:P").ColumnWidth = 26.43
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    Worksheet.Columns("Y:Y").ColumnWidth = 25.86
    Worksheet.Range("S1:Y1").Select
    Worksheet.Range("Y1").Activate
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Worksheet.Rows("1:1").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Range("J2").Select
    ActiveWindow.FreezePanes = True
    Worksheet.Range("A1:O1").Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = 0.399975585192419
    .PatternTintAndShade = 0
    End With
    Worksheet.Range("S1:Y1").Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = 0.399975585192419
    .PatternTintAndShade = 0
    End With
    Worksheet.Columns("P:R").Select
    Worksheet.Range("R1").Activate
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Worksheet.Range("F4").Select


    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    Worksheet.Range("C2").Select
    ActiveWindow.SmallScroll Down:=-12
    Worksheet.Range("D3").Select
    Worksheet.Sheets("qryTrackerDrew").Select
    Worksheet.Cells.Select
    With Selection.Font
    .Name = "Tahoma"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
    .Name = "Tahoma"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    Worksheet.Columns("A").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Sheets(Array("qryTrackerDrew", "qryTrackerGreg", "qryTrackerNelson")).Select
    Worksheet.Sheets("qryTrackerDrew").Activate
    Worksheet.Columns("A").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection.Font
    .Name = "Tah]"
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
    .Name = "Tahoma"
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    Worksheet.Columns("A").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Cells.Select
    With Selection
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Range("J2").Select
    Worksheet.Columns("J:J").ColumnWidth = 52.29
    Worksheet.Columns("I:I").ColumnWidth = 35.86
    Worksheet.Columns("H:H").ColumnWidth = 24.71
    Worksheet.Columns("F:G").Select
    Worksheet.Selection.ColumnWidth = 13
    Worksheet.Columns("E:E").Select
    Worksheet.Selection.ColumnWidth = 24.86
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    Worksheet.Range("K:L,S:S,N:O,P:P").Select
    Worksheet.Range("P1").Activate
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    With Selection
    .HorizontalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Range("Q2").Select
    Worksheet.Columns("Q:Q").ColumnWidth = 33
    Worksheet.Columns("R:R").ColumnWidth = 34.86
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Worksheet.Cells.Select
    Worksheet.Cells.EntireRow.AutoFit
    Worksheet.Range("I2").Select
    ActiveWindow.FreezePanes = True
    Worksheet.Range("A1:P1").Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = 0.399975585192419
    .PatternTintAndShade = 0
    End With
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    Worksheet.Columns("Q:S").Select
    Worksheet.Range("S1").Activate
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    Worksheet.Cells.Select
    With Selection
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Range("F5").Select
    Worksheet.Sheets(Array("qryTrackerDrew", "qryTrackerGreg", "qryTrackerNelson")).Select
    Worksheet.Sheets("qryTrackerGreg").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    Worksheet.Sheets(Array("qryTrackerDrew", "qryTrackerGreg", "qryTrackerNelson")).Select
    Worksheet.Sheets("qryTrackerNelson").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    Worksheet.Sheets(Array("qryTrackerDrew", "qryTrackerGreg", "qryTrackerNelson")).Select
    Worksheet.Sheets("qryTrackerDrew").Activate
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Worksheet.Range("K1:P1").Select
    Worksheet.Range("P1").Activate
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Worksheet.Range("M2").Select
    Worksheet.Columns("M:M").ColumnWidth = 36.71
    Worksheet.Range("M4").Select
    Worksheet.Sheets(Array("qryTrackerDrew", "qryTrackerGreg", "qryTrackerNelson")).Select
    Worksheet.Sheets("qryTrackerDrew").Activate
    Worksheet.Cells.Select
    With Selection.Font
    .Name = "Tahoma"
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
    .Name = "Tahoma"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    Worksheet.Sheets(Array("qryTrackerDrew", "qryTrackerGreg", "qryTrackerNelson")).Select
    Worksheet.Sheets("qryTrackerDrew").Activate
    Worksheet.Columns("K:L").Select
    Selection.ColumnWidth = 11.86
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    Worksheet.Columns("N:P").Select
    Worksheet.Range("P1").Activate
    Selection.ColumnWidth = 8.57
    Worksheet.Sheets(Array("qryTrackerDrew", "qryTrackerGreg", "qryTrackerNelson")).Select
    Worksheet.Sheets("qryTrackerDrew").Activate
    Worksheet.Rows("1:1").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    Worksheet.Range("A1:S1").Select
    Worksheet.Range("S1").Activate
    Selection.Font.Bold = True
    Worksheet.Sheets("APL Tracker Query").Select
    Worksheet.Rows("1:1").Select
    Selection.Font.Bold = True
    Worksheet.Range("I5").Select
    ActiveWindow.SmallScroll Down:=-15


    End Sub
    Reply With Quote Reply With Quote
  2. 01-18-2019, 05:20 PM #2
    Micron
    Micron is offline Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,423
    I'm going to suggest, based on 65 views of your post yet no answers, that you go back and cut your code, add code tags (# on toolbar of post) and paste the code back in between those tags. Preview your changes and add appropriate code indentation.

    Alternatively, go back to your code which is probably already indented, copy it, wipe your post and paste the newly copied code in between code tags. Why? Because if everyone feels the same way about this, I won't even bother to read what you posted. It's too difficult as presented.
    Reply With Quote Reply With Quote
  3. 01-19-2019, 05:40 PM #3
    pbaldy's Avatar
    pbaldy
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,652
    Welcome Megan. While code tags and indenting make code easier to read, your initial problem is easy to spot. Using the macro recorder in Excel is a great way to get the basic code, but it has to be adapted to be used from Access. In general terms, you have to open the Excel file programmatically from Access, then manipulate it. You also have to use the Excel object you opened the file with to manipulate things. Daniel has some great code here that might get you started. Jump in and then post back here if you get stuck.

    https://www.devhut.net/2016/07/07/vba-automating-excel/
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com
    Reply With Quote Reply With Quote
  4. 01-19-2019, 06:10 PM #4
    megan2478
    megan2478 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    2

    Thumbs up

    Quote Originally Posted by pbaldy View Post
    Welcome Megan. While code tags and indenting make code easier to read, your initial problem is easy to spot. Using the macro recorder in Excel is a great way to get the basic code, but it has to be adapted to be used from Access. In general terms, you have to open the Excel file programmatically from Access, then manipulate it. You also have to use the Excel object you opened the file with to manipulate things. Daniel has some great code here that might get you started. Jump in and then post back here if you get stuck.

    https://www.devhut.net/2016/07/07/vba-automating-excel/

    THANK YOU SO MUCH PBALDY!

    I'll attempt it using the steps you suggest and let you know.


    "In general terms, you have to open the Excel file programmatically from Access, then manipulate it"

    So, I export the table to excel, turn the macro recorder on, and then make the changes... is that where you say I should start?

    Thank you :0)
    Reply With Quote Reply With Quote
  5. 01-19-2019, 06:24 PM #5
    pbaldy's Avatar
    pbaldy
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,652
    No, the macro recorder is just a tool you can use during development to get an idea of the code necessary. It will always need to be adapted.

    In your case, there are a couple of options. You can leave your export code alone, then right after that open the file with Daniel's code and manipulate it. You could also use automation to both export and manipulate the data. I'm on a mobile device right now so can't get to my apps, but play with Daniel's code and see if it starts to make sense. I can get to my apps and post code tomorrow.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com
    Reply With Quote Reply With Quote
  6. 01-19-2019, 06:26 PM #6
    June7's Avatar
    June7
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,771
    As Paul noted, post code within CODE tags to retain indentation and readability. And prevent strings converted to smiley faces as happened in your code. Use the # button above the post editor.

    If you are exporting to a new workbook, then code does not exist in that workbook. You need to execute code to do the foramatting from a VBA module in Access. This is where the code must be modified to run from. You already have code generated by macro recorder; now have to modify it to run in Access VBA procedure. This won't be simple.

    As Paul also said, can do the export and open the new file then manually do all the formatting every time, but then why bother with recording code? You need generic code that can be used with every export. That code should be in Access VBA.

    Note that the example in link does not use TransferSpreadsheet.

    Alternative involves code in Excel pulling in data from Access. I have employed that approach for users who don't work with the Access db but need data for import to another proprietary program and that program cannot connect directly to Access.
    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.
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Stored Procedure output to excel file on button click
    By anavagomez in forum Programming
    Replies: 3
    Last Post: 05-24-2017, 09:24 PM
  2. Formatting of output tables to excel
    By hinkwale in forum Access
    Replies: 2
    Last Post: 01-26-2015, 06:41 PM
  3. Formatting of output to excel
    By hinkwale in forum Access
    Replies: 0
    Last Post: 01-23-2015, 12:13 PM
  4. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  5. Output tables to an excel file on a network drive
    By GraemeG in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2011, 03:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Other Forums: Microsoft Office Forums