Results 1 to 13 of 13
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    Output to Excel From Access Form

    Every time I go to output a form to excel, it looks like it has saved export settings on how the outputted form should look and what it is named. Where can I find these settings?



    Thanks,
    Nick

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't save forms to excel.
    do you mean,save the datasheet ON the form to excel?
    if so, docmd.transferspreadsheet....

  3. #3
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Yes, it is filtered data from data on the first form. I will try the transfer spread sheet method.

    Thanks,
    Nick

  4. #4
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    OK, I tried the transfer spreadsheet method and it did not work for me. It said that the range I specified already existed. I guess what I am looking for is basically when the data from the form is outputted to xls. The new column that I have added is never in the right place. I also have a datasheet view as a button and all I had to do was drag and drop the column to the right area. I just want to be able to adjust the outputted columns in the excel worksheet but it always puts it by itself and you have to move the column each time.

    Thanks,
    Nick

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You have to supply a query with the columns ordered the way you want.
    the form is only available to the user ,not to the transfer.

  6. #6
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    I tried the transfer again and it just thinks when the button is clicked and does nothing.

    This is what I have in my macro:

    Action: TransferSpreadsheet

    Transfer Type: A-B-Detail
    Spreadsheet Type: Microsoft Excel 8-9
    Table Name: A-B-Detailed
    File Name: ABDetailed
    Has Field Names: No
    Range:

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Put the full file path that you want the file to go to in the File Name argument.
    Refer to this for each argument: https://msdn.microsoft.com/en-us/lib.../ff844793.aspx

  8. #8
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Ok, I am able to import the access query to excel but It continues to not import the new column. I have checked all the queries and tables that I have built for this form and they seem to have the correct structure. I also tried importing the table using excel and when I imported the query I wanted, it was still not correct but when I look at the query in access it shows as correct. Am I missing something?

    Thanks for your all your help,
    Nick

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So is "A-B-Detailed" the name of your Table or Query?
    And the new field displays in there?
    Delete the old file first before trying running it again to ensure that you are getting a new file.

    It almost sounds like it is remembering some old export settings. Maybe try re-creating the macro and see if that helps.

  10. #10
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Yes, AB detailed is the query. Yes the new field displays in the ab details query. I just decided to use the previous export technique that used code to output the form. I recreated the same thing with a macro assigned to a new button and it still incorrectly copied over the structure. It would always take the new column and put it all the way to the right in a unnamed column. So we need to rearrange that column to the correct area but I am not sure how to change those. I believe there is some type of export settings that is doing this to me?

    Here is the code
    Code:
    Private Sub Command68_Click()On Error GoTo Err_Command68_Click
    
    
        Dim stDocName As String
    
    
        stDocName = Me.Name
        DoCmd.OutputTo acForm, stDocName, "MicrosoftExcel(*.xls)", "C:\DBList.xls", True, ""
    
    
    Exit_Command68_Click:
        Exit Sub
    
    
    Err_Command68_Click:
        MsgBox Err.Description
        Resume Exit_Command68_Click
        
    End Sub
    This is a form created from another form with a filter on the previous form depending on where I double clicked on a different query in the form.

    Thanks,
    Nick

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are they in the correct order in your Query?
    I would expect it to export in the same order that they are in the query that you are exporting.

    Note: You can also export the Query using "DoCmd.OutputTo" (an alternative to using TransferSpreasheet).
    See: http://access-excel.tips/access-vba-...tputto-method/

  12. #12
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Yes, it is in the correct order in the query.

  13. #13
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    I have figured it out. When I went to originally output the form itself. When I right click in the form there is a tab order button that you click.This contains the custom order that access exported to excel. Once I changed that order in the detail section, it outputted it correctly to excel.

    Thank you for all your help,

    Nick

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

Similar Threads

  1. Replies: 4
    Last Post: 12-30-2013, 05:51 PM
  2. Output to Excel from VBA
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 10-08-2012, 03:33 PM
  3. MS Access to Excel Output
    By rjbautista20 in forum Programming
    Replies: 7
    Last Post: 02-22-2012, 07:23 PM
  4. Replies: 4
    Last Post: 01-05-2012, 08:31 PM
  5. Display MULTIPLE tabs on EXCEL output from FORM
    By taimysho0 in forum Programming
    Replies: 8
    Last Post: 12-12-2011, 02:07 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