none
getting "Run Time error 13 with Access 2010 command DoCmd.TransferSpreadsheet acExport RRS feed

  • Question

  • I am trying to export a file to a worksheet"BGWE" with the script for a button below, but get every time the error : "Run Time  - error 13"? The commands and objects or data definition looks to be fine, but can't workout why I get the error still 

    Private Sub CmdBGWtofile_Click()
    '------------------------------------------------------------
    ' CmdBGWtofile_Click
    '
    '------------------------------------------------------------
        Dim App As Object
        Dim File As Object
        Dim rs As Recordset
        Set App = CreateObject("Excel.Application")
        Set File = App.Workbooks.Open("C:\Users\Documents\Template.xlsx")
        Set ws = File.Worksheets("BGWE")
        ws.Activate
        DoCmd.TransferSpreadsheet acExport, "Setting BGWE final 2015", acSpreadsheetTypeExcel12Xml, "BGWE!", "C:\Users\Documents\Template.xlsx", True
        App.Workbooks.Close
    End Sub

    Friday, September 18, 2015 10:58 AM

Answers

  • There is no need to open the workbook in Excel - just DoCmd.TransferSpreadsheet will do that.

    The order of the arguments to TransferSpreadsheet is incorrect. It should be like this:

    Private Sub CmdBGWtofile_Click()
     '------------------------------------------------------------
     ' CmdBGWtofile_Click
     '
     '------------------------------------------------------------
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
             "Setting BGWE final 2015", "C:\Users\Documents\Template.xlsx", True, "BGWE!"
     End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 18, 2015 11:09 AM
  • For example:

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
            "Setting BGWE final 2015", _
            "C:\Users\Documents\Template_" & Format(Date, "yyyymmdd") & ".xlsx", True, "BGWE!"

    You can specify another date format of course, but you cannot use / in the date format, since / is not allowed in file names.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 21, 2015 3:14 PM

All replies

  • There is no need to open the workbook in Excel - just DoCmd.TransferSpreadsheet will do that.

    The order of the arguments to TransferSpreadsheet is incorrect. It should be like this:

    Private Sub CmdBGWtofile_Click()
     '------------------------------------------------------------
     ' CmdBGWtofile_Click
     '
     '------------------------------------------------------------
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
             "Setting BGWE final 2015", "C:\Users\Documents\Template.xlsx", True, "BGWE!"
     End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 18, 2015 11:09 AM
  • Excellent !! It worked very well in acccess 2010.  
    Monday, September 21, 2015 8:48 AM
  • The last outstanding question I have is how to save the template with a date extention?  Think here about the excel function Saveas in excel.
    Monday, September 21, 2015 8:50 AM
  • For example:

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
            "Setting BGWE final 2015", _
            "C:\Users\Documents\Template_" & Format(Date, "yyyymmdd") & ".xlsx", True, "BGWE!"

    You can specify another date format of course, but you cannot use / in the date format, since / is not allowed in file names.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 21, 2015 3:14 PM
  • Assuming you want today's date concatenate the date using the Date() function:

    "C:\Users\Documents\Template_" & Date() & ".xlsx"


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 21, 2015 3:14 PM
  • The first line is working well as it’s saving it in a worksheet of an existing template.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Setting BGW final 2015", "C:\Users \Template_BGW.xlsm", True, "BGW"

     The second and third one it’s working as it comes back with a run Time error 3027 and 3044?  So I wonder how can I use the "save as" function in access  for saving the file with the date format?

     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, " Setting BGW final 2015", "C:\Users\ Template_BGW" & Format(Date, "yyyymmdd") & ".xlsm", True, "BGW"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, " Setting BGW final 2015", "C:\Users\ Template_BGW" & Date() & ".xlsm", True, "BGW"

    Thursday, October 29, 2015 8:44 PM
  • Any suggestion?
    Thursday, November 5, 2015 7:41 PM
  • It isn't clear to me why the second example wouldn't work.

    The last example won't work if your default date format contains "/", since "/" isn't allowed in a file name.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 5, 2015 7:50 PM
  • Any suggestion?

    Hi Anri2018,

    Thanks for your post.

    For your issue, I suggest you post a new thread for your own issue, and share us more information.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 6, 2015 4:58 AM