Results 1 to 10 of 10
  1. #1
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70

    Problem exporting table to excel

    I converted a macro to code. The macro had two queries ... the first one (Delete_tbl_Temp_Items), which deletes all entries in the tbl_Temp table. The second query appends the results of "CurrentTeachersSvcTag" to the tbl_Temp table. (I chose to delete the data and append the query results because I was having trouble with the date field transferring properly).

    "CurrentTeachersSvcTag" pulls the necessary items for a particular school into the tbl_Temp table. Users can change the school at will, and often do.

    The queries work perfectly, and after running them, the tbl_Temp ALWAYS has the correct data. After the export to excel, I open that spreadsheet and it's a crap shoot as to whether I get the old results or the results currently in the tbl_Temp file.



    It appears that the existing "1-1_Teachers.xlsx" workbook is not always overwritten. Sometimes it is and I get the new results, sometimes it's not and I get the results from the last successful export. It's kind of a crap-shoot if the file actually exports, even though the message box pops up and tells me that the export was successful.

    Any help identifying where I'm going wrong would be greatly appreciated!!

    Here is the code:

    Private Sub Command23_Click()
    On Error GoTo mcr_ExportTeacherToExcel_Err


    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Delete_tbl_Temp_Items", acViewNormal, acEdit
    DoCmd.Close acQuery, "Delete_tbl_Temp_Items"
    DoCmd.OpenQuery "CurrentTeacherSvcTag", acViewNormal, acEdit
    DoCmd.Close acQuery, "CurrentTeacherSvcTag"
    DoCmd.TransferSpreadsheet acExport, 10, "tbl_Temp", "1-1_Teachers.xlsx", True, ""
    Beep
    MsgBox "Results have been exported to your My Documents folder as a file named 1-1_Teachers.", vbOKOnly, ""


    mcr_ExportTeacherToExcel_Exit:
    Exit Sub


    mcr_ExportTeacherToExcel_Err:
    MsgBox Error$
    Resume mcr_ExportTeacherToExcel_Exit
    End Sub

  2. #2
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    expression .TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

    The FileName parameter is defined as:
    A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.

    So try changing that to
    "C:\JackieEVSC\Documents\1-1_Teachers.xlsx"
    Obviously don't change it to exactly that, but change it to whatever the path to the file is.

    That's my two cents.
    Let me know if it worked.
    If not, we can further trouble shoot!

  3. #3
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Different thought.
    Is the table open or closed when you run that code?
    What happens when you run the code when the table is closed?
    What happens when you run the code when the table is open?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Code:
    Private Sub Command23_Click()                               
     On Error GoTo mcr_ExportTeacherToExcel_Err
     
     
     DoCmd.SetWarnings False
     DoCmd.OpenQuery "Delete_tbl_Temp_Items", acViewNormal, acEdit
     DoCmd.Close acQuery, "Delete_tbl_Temp_Items"
     DoCmd.OpenQuery "CurrentTeacherSvcTag", acViewNormal, acEdit
     DoCmd.Close acQuery, "CurrentTeacherSvcTag"
     DoCmd.TransferSpreadsheet acExport, 10, "tbl_Temp", "1-1_Teachers.xlsx", True , ""
     Beep
     MsgBox "Results have been exported to your My Documents folder as a file named 1-1_Teachers.", vbOKOnly, ""
    
    DoCmd.SetWarnings True
     
     mcr_ExportTeacherToExcel_Exit:
     Exit Sub
     
     
     mcr_ExportTeacherToExcel_Err:
    DoCmd.SetWarnings True
     MsgBox Error$
     Resume mcr_ExportTeacherToExcel_Exit
     End Sub
    A word about your code:
    - remove everything that is not required; most parameters have a default so if you leave them out the default will occur; the less writing the better, the code will look cleaner and easier to quickly follow
    - NEVER leave controls on forms without a proper name, change them always to a word(s) in English that is clearly understandable, especially if you are going to use them in VBA
    - NEVER EVER set warnings off without switching them back on again. I cannot tell you the problems this can cause in your database. Believe me - been there, done that, got the scars to prove it!

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The strikethrough's didn't come out, here they are:



    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Delete_tbl_Temp_Items", acViewNormal, acEdit
    DoCmd.Close acQuery, "Delete_tbl_Temp_Items"
    DoCmd.OpenQuery "CurrentTeacherSvcTag", acViewNormal, acEdit
    DoCmd.Close acQuery, "CurrentTeacherSvcTag"
    DoCmd.TransferSpreadsheet acExport, 10, "tbl_Temp", "1-1_Teachers.xlsx", True , ""
    Beep
    MsgBox "Results have been exported to your My Documents folder as a file named 1-1_Teachers.", vbOKOnly, ""
    DoCmd.SetWarnings True

    mcr_ExportTeacherToExcel_Exit:
    Exit Sub


    mcr_ExportTeacherToExcel_Err:
    DoCmd.SetWarnings True
    MsgBox Error$
    Resume mcr_ExportTeacherToExcel_Exit
    End Sub

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It is NEVER a good idea to begin a file name with a number, but try this ON A COPY of your dB:
    (Note 1: warning -- mostly untested --)
    (Note 2: I would single step through the code until I gain confidence in the code...)
    Code:
    Private Sub Command23_Click()
        On Error GoTo mcr_ExportTeacherToExcel_Err
    
        Dim KillFile As String
        
        
        KillFile = "1-1_Teachers.xlsx"
        KillFile = CurrentProject.Path & "\" & KillFile
    
        'for debugging --------------------
        MsgBox KillFile   ' <<-- delete or comment out after testing
        'for debugging --------------------
    
        '    'Check that file exists
        If Len(Dir(KillFile)) > 0 Then
            MsgBox "file  exists"
            '    'First remove readonly attribute, if set
            SetAttr KillFile, vbNormal
            '    'Then delete the file
            Kill KillFile
        End If
    
        CurrentDb.Execute "Delete_tbl_Temp_Items", dbFailOnError
        CurrentDb.Execute "CurrentTeacherSvcTag", dbFailOnError
    
        DoCmd.TransferSpreadsheet acExport, 10, "tbl_Temp", "1-1_Teachers.xlsx", True, ""
        
        
        Beep
        MsgBox "Results have been exported to your My Documents folder as a file named 1-1_Teachers.", vbOKOnly, ""
    
    
    mcr_ExportTeacherToExcel_Exit:
        Exit Sub
    
    
    mcr_ExportTeacherToExcel_Err:
        MsgBox Error$
        Resume mcr_ExportTeacherToExcel_Exit
    End Sub

  7. #7
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    Burt,

    The table is closed when the code runs. I tried moving the close command to after the code ran, and I get the same results. I won't ever be the only one running the reports (except to test them), so it needs to go to the documents folder of whoever is running it.

  8. #8
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    Steve,

    Your code works fine, but the the file path is incorrect. The path needs to be that of the logged in user's documents folder. Your code takes them to the backend folder on the network. I tried using "Path.Combine(Environment.ExpandEnvironmentVariable s("%userprofile%"), "Documents")", but I got an "object required" error. The file could be put on the users desktop, too ... I just used the documents folder because that seemed to be the default.

    I also changed the names of the file from 1-1_Teachers to TeacherDevices.

  9. #9
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    Steve,

    I took out "KillFile = CurrentProject.Path & "" & KillFile" and it works perfectly. Thank you SO much for your help!

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad you got it working...


    Good luck with your project.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem exporting dates to Excel
    By Freebird in forum Import/Export Data
    Replies: 2
    Last Post: 07-19-2016, 06:57 AM
  2. Problem Exporting Multivalued Field to Excel
    By MatthewR in forum Queries
    Replies: 16
    Last Post: 02-11-2016, 02:14 PM
  3. Problem exporting multi-line addresses to Excel
    By mjwillyone in forum Import/Export Data
    Replies: 4
    Last Post: 01-27-2016, 11:01 AM
  4. Exporting a Table to MS Excel problem
    By nosec in forum Import/Export Data
    Replies: 9
    Last Post: 11-05-2013, 11:28 AM
  5. Exporting to Excel Problem
    By octu in forum Import/Export Data
    Replies: 1
    Last Post: 04-14-2010, 11:28 PM

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