Results 1 to 2 of 2
  1. #1
    Join Date
    May 2013
    Posts
    7

    Unanswered: Multiple Tables to 1 workbook, multiple tabs

    I am trying to export 5 different tables, with different number of columns and rows, to an Excel File as different tabs.


    Function Output()

    Dim WkBkPath As String

    WkBkPath = CurrentProject.Path & "\Default_Timeline - " & Format(Date, "yyyymmdd")

    DoCmd.TransferSpreadsheet acExport, 10, "SAI", WkBkPath, True
    DoCmd.TransferSpreadsheet acExport, 10, "Conventional", WkBkPath, True
    DoCmd.TransferSpreadsheet acExport, 10, "VA", WkBkPath, True
    DoCmd.TransferSpreadsheet acExport, 10, "FHA", WkBkPath, True
    DoCmd.TransferSpreadsheet acExport, 10, "HELOC", WkBkPath, True

    End Function

    If I run this Function, it gives me the "3274 Run Time External table is not in the expected format" Error.


    The issue is, if I run each DoCmd 1 at a time in its own function, it will open the workbook, and ad the tab with each table name as the tab name. I can do this 5 times and it works, but if I try to run them all together in the 1 Function, it randomly selects a DoCmd and gives me the 3274 error.

    I am thinking that each DoCmd is running before the file is done transfering the table.

    Any help would be appreciated.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can, indeed, cause timing problems! The usual way to change this type of behavior is to use DoEvents.

    Code:
    DoCmd.TransferSpreadsheet acExport, 10, "SAI", WkBkPath, True
    DoEvents
    DoCmd.TransferSpreadsheet acExport, 10, "Conventional", WkBkPath, True
    DoEvents
    DoCmd.TransferSpreadsheet acExport, 10, "VA", WkBkPath, True
    DoEvents
    DoCmd.TransferSpreadsheet acExport, 10, "FHA", WkBkPath, True
    DoEvents
    DoCmd.TransferSpreadsheet acExport, 10, "HELOC", WkBkPath, True


    DoEvents returns control to Windows, allowing one transfer to be completed before starting to run next transfer. DoEvents is an easy, quick approach to resolve what seems to be timing issues, and if it shouldn't solve the issue, at least it won't cause problems!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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