Results 1 to 8 of 8
  1. #1
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142

    Automatically update Excel where the data source is connected to Access

    I am trying to automatically update excel every month end whose data source is from access.


    Is there anyway I can do that?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can do an export, docmd.transferspreadsheet...
    or
    in excel, create a connection, and refresh the data.

  3. #3
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142
    Thanks for your reply.
    Its working on the access database query without any criteria. how to export a query to excel with the criteria?

    I am trying to not open the access database every month end instead just refresh the excel. Problem is with the access database query and its criteria.
    Can I give the criteria selections through excel?


    Quote Originally Posted by ranman256 View Post
    You can do an export, docmd.transferspreadsheet...
    or
    in excel, create a connection, and refresh the data.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  5. #5
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142
    Yes I am trying to automate.

    I used the below code in the access button.

    Code:
    Private Sub Command0_Click()
    DoCmd.TransferSpreadsheet acExport, 8, "Query Name", "C:\Users\Desktop\Test.xlsx", True, "#1 Raw Time Data"
    End Sub
    I wanted to load the data in the specific sheet and also in specific Name Range.
    This query helps me in loading or refreshing the data into a separate sheet.
    Can anyone help me with this.





  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, lets look at the TransferSpreadsheet method.

    The syntax is
    expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

    You have
    Code:
    DoCmd.TransferSpreadsheet acExport, 8, "Query Name", "C:\Users\Desktop\Test.xlsx", True, "#1 Raw Time Data"
    Looking at the parameters (from Help):
    TransferType - acExport - OK

    SpreadsheetType - 8 - SpreadsheetType 8 is for a Microsoft Excel 2000 format spreadsheet. Yet you have an extenstion of "xlsx".
    You should use/have
    "acSpreadsheetTypeExcel12xml" (which is type 10) see https://docs.microsoft.com/en-us/off...preadsheettype

    TableName
    - "Query Name" -
    because you have used a space in the table/query name, it MUST be enclosed with brackets ("[Query Name]"). FYI, You should NEVER use spaces in object names.

    FileName
    - "C:\Users\Desktop\Test.xlsx" -It appears you have/use Office 2013 (hence the ".xlsx" extension) which affects the spreadsheet type parameter.

    HasFieldNames
    - TRUE - OK

    Range
    - "#1 Raw Time Data" -
    This argument applies only to importing
    . <snip> When you export to a spreadsheet, you must leave this argument blank.
    If you enter a range, the export will fail

    UseOA
    - omitted -
    OK, This argument is not supported


    Maybe try
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xml, "[Query Name]", "C:\Users\Desktop\Test.xlsx", True


    Quote Originally Posted by Shamli View Post
    Its working on the access database query without any criteria. how to export a query to excel with the criteria?
    The criteria in a query limits records returned, so just add the criteria to the query......


    Quote Originally Posted by Shamli View Post
    I wanted to load the data in the specific sheet and also in specific Name Range.
    You cannot use the range parameter.
    You should read this: Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)

    Also, you might look at Ken Snell's site

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Why you don't do it in reverse?

    In Excel, create an ODBC query which reads data from Access database. Set query to be refreshed when the workbook is opened (You can set the query to be refreshed at certain time interval too, but this may be annoying when your workbook is freezing periodically when you are working with it. Alternatively you can refresh the query manually too.).

    You use Access2013 - obviously you have Office2013 (or higher version too). This means the query's datarange is defined as Table and you can rename this Table as you want (e.g. tYourAccessData). In your Excel workbook, you always can refer to it as e.g. tMyAccessData, or you can define a Named Range based on Table: nYourAccessData = tYourAccessData
    Last edited by ArviLaanemets; 09-18-2018 at 11:49 PM. Reason: editing

  8. #8
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142
    Omg!! Thank you so much for the explanation. This made me learn in depth .

    I will try the query.

    Quote Originally Posted by ssanfu View Post
    OK, lets look at the TransferSpreadsheet method.

    The syntax is
    expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

    You have
    Code:
    DoCmd.TransferSpreadsheet acExport, 8, "Query Name", "C:\Users\Desktop\Test.xlsx", True, "#1 Raw Time Data"
    Looking at the parameters (from Help):
    TransferType - acExport - OK

    SpreadsheetType - 8 - SpreadsheetType 8 is for a Microsoft Excel 2000 format spreadsheet. Yet you have an extenstion of "xlsx".
    You should use/have
    "acSpreadsheetTypeExcel12xml" (which is type 10) see https://docs.microsoft.com/en-us/off...preadsheettype

    TableName
    - "Query Name" -
    because you have used a space in the table/query name, it MUST be enclosed with brackets ("[Query Name]"). FYI, You should NEVER use spaces in object names.

    FileName
    - "C:\Users\Desktop\Test.xlsx" -It appears you have/use Office 2013 (hence the ".xlsx" extension) which affects the spreadsheet type parameter.

    HasFieldNames
    - TRUE - OK

    Range
    - "#1 Raw Time Data" -
    This argument applies only to importing
    . <snip> When you export to a spreadsheet, you must leave this argument blank.
    If you enter a range, the export will fail

    UseOA
    - omitted -
    OK, This argument is not supported


    Maybe try
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xml, "[Query Name]", "C:\Users\Desktop\Test.xlsx", True



    The criteria in a query limits records returned, so just add the criteria to the query......



    You cannot use the range parameter.
    You should read this: Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)

    Also, you might look at Ken Snell's site

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

Similar Threads

  1. Replies: 13
    Last Post: 08-30-2018, 01:41 PM
  2. Replies: 2
    Last Post: 06-02-2017, 04:43 PM
  3. Replies: 2
    Last Post: 02-13-2017, 02:40 PM
  4. Replies: 0
    Last Post: 05-15-2016, 11:18 AM
  5. Replies: 30
    Last Post: 06-26-2011, 10:47 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