Results 1 to 7 of 7
  1. #1
    Menni is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3

    Export queries to excel files automatically

    I'm utilizing the DoCmd.TransferSpreadsheet acExport Function to create the excel file I need from a query. In order to improve the automation, I need to automatically replace the xxx.xlsx file name with a variable from a form which also contain the sorting parameters for the query, the user or the system can determine a different name as the file name every time. Can you please give me any idea how to determine the variable file name?
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SortedProductsQuery", "C:\Exports\xxxxx.xlsx", True
    Thank you
    Menni

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    You would use concatenation?
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SortedProductsQuery", "C:\Exports\" & Me.YourFileControlName & ".xlsx", True
    
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Menni is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3

    Thank you

    Quote Originally Posted by Welshgasman View Post
    You would use concatenation?
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SortedProductsQuery", "C:\Exports\" & Me.YourFileControlName & ".xlsx", True
    
    I don't know how it works with Access 2007. It works great with Access 2019 but the line should like this:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SortedProductsQuery", "C:\Exports" & Me!YourFileControlName & ".xlsx", True
    Use exclamation mark "!" in the exported file name (
    Me!YourFileControlName) instead of a period ".". (Me.YourFileControlName)

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Way to say Thank You

    A period works just fine.
    Plus you get the bonus of intellisense.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Menni is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3

    Sorry

    Quote Originally Posted by Menni View Post
    I don't know how it works with Access 2007. It works great with Access 2019 but the line should like this:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SortedProductsQuery", "C:\Exports" & Me!YourFileControlName & ".xlsx", True
    Use exclamation mark "!" in the exported file name (
    Me!YourFileControlName) instead of a period ".". (Me.YourFileControlName)
    Sorry it didn't work for me with period. I got an error message.
    Menni

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Maybe because your code is missing one backslash after "C:\Exports" And Welshgasman's doesn't?

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SortedProductsQuery", "C:\Exports" & Me!YourFileControlName & ".xlsx", True
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by Edwin View Post
    Go to "Object Explorer", find the server database you want to export to Excel. Right-click on it and choose "Tasks" > "Export Data" to export table data in SQL. Then, the SQL Server Import and Export Wizard welcome window pop up.
    I never saw any mention of SQL Server?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  2. Replies: 9
    Last Post: 10-31-2013, 06:51 AM
  3. import excel files into access automatically
    By jstei012 in forum Import/Export Data
    Replies: 1
    Last Post: 12-19-2011, 04:12 PM
  4. Automatically Export to Excel
    By chachie22 in forum Import/Export Data
    Replies: 2
    Last Post: 07-15-2011, 02:30 PM
  5. Replies: 6
    Last Post: 10-14-2010, 08:33 AM

Tags for this Thread

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