Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2017
    Posts
    2

    Unanswered: MS Access Macro's Result Workbook Table Formatting Across All Worksheets

    Hello,

    I apologize if this has been posted somewhere else on this forum, but I couldn't find anything on it and didn't know how to word it. The issue I'm running into deals more so with MS Excel, but I am thinking the cause may be from how my MS Access DB's macro exports the spreadsheets. The macro is set up to export multiple select queries into one workbook.

    This seems to work fine up until I begin formatting the first sheet of the workbook with borders. When putting borders around the data in the first sheet, the same changes are being mimicked across all of the other worksheets. For example, if I'm applying borders to a table of data that is 30 rows and 7 columns and on my other sheet there is a table of data that is only in 20 rows and 4 columns - the 30x7 table border style is applied to the other sheet as if there was data populating the "missing" 10 rows and 3 columns. I hope this makes sense? I do not want this, I want the worksheets to work independently when formatting (as they normally behave). Please see VBA code for MS Access DB macro below. Thanks in advance.

    Code:
    DoCmd.TransferSpreadsheet acExport, 10, "a11 - HIREs, REHIREs, UPDATEs", "D:\7 - FG Error\FG Access\EDM Deliverables\FG EDM Key " & Format(Date, "mm\.dd") & ".xlsx", True, ""
    DoCmd.TransferSpreadsheet acExport, 10, "6 - JSID UPDATEs", "D:\7 - FG Error\FG Access\EDM Deliverables\FG EDM Key " & Format(Date, "mm\.dd") & ".xlsx", True, ""
    DoCmd.TransferSpreadsheet acExport, 10, "a16 - Employement Data Corrections", "D:\7 - FG Error\FG Access\EDM Deliverables\FG EDM Key " & Format(Date, "mm\.dd") & ".xlsx", True, ""
    DoCmd.TransferSpreadsheet acExport, 10, "FG Source", "D:\7 - FG Error\FG Access\EDM Deliverables\FG EDM Key " & Format(Date, "mm\.dd") & ".xlsx", True, ""
    DoCmd.TransferSpreadsheet acExport, 10, "Active Departments", "D:\7 - FG Error\FG Access\EDM Deliverables\FG EDM Key " & Format(Date, "mm\.dd") & ".xlsx", True, ""
        Beep

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    You cannot have slashes in filenames.
    you can
    format(date,"yymmdd")

  3. #3
    Join Date
    Jan 2017
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    You cannot have slashes in filenames.
    you can
    format(date,"yymmdd")
    I am not putting slashes in the filename. The path in the code is simply exporting with the file name 'FG EDM Key 02.03', for example. This shouldn't be the issue as the issue resides WITHIN the excel spreadsheet.

Posting Permissions

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