I am trying to automatically update excel every month end whose data source is from access.
Is there anyway I can do that?
I am trying to automatically update excel every month end whose data source is from access.
Is there anyway I can do that?
You can do an export, docmd.transferspreadsheet...
or
in excel, create a connection, and refresh the data.
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?
Sounds like you are trying to automate access from excel.
Start by checking these sites:
https://answers.microsoft.com/en-us/...3-9fcd9052472a
https://www.microsoftaccessexpert.co...utomation.aspx
https://www.mrexcel.com/forum/micros...ort-excel.html
https://www.excelforum.com/
Yes I am trying to automate.
I used the below code in the access button.
I wanted to load the data in the specific sheet and also in specific Name Range.Code:Private Sub Command0_Click() DoCmd.TransferSpreadsheet acExport, 8, "Query Name", "C:\Users\Desktop\Test.xlsx", True, "#1 Raw Time Data" End Sub
This query helps me in loading or refreshing the data into a separate sheet.
Can anyone help me with this.
Sounds like you are trying to automate access from excel.
Start by checking these sites:
https://answers.microsoft.com/en-us/...3-9fcd9052472a
https://www.microsoftaccessexpert.co...utomation.aspx
https://www.mrexcel.com/forum/micros...ort-excel.html
https://www.excelforum.com/
OK, lets look at the TransferSpreadsheet method.
The syntax is
expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
You haveLooking at the parameters (from Help):Code:DoCmd.TransferSpreadsheet acExport, 8, "Query Name", "C:\Users\Desktop\Test.xlsx", True, "#1 Raw Time Data"
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
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
Omg!! Thank you so much for the explanation. This made me learn in depth .
I will try the query.
OK, lets look at the TransferSpreadsheet method.
The syntax is
expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
You haveLooking at the parameters (from Help):Code:DoCmd.TransferSpreadsheet acExport, 8, "Query Name", "C:\Users\Desktop\Test.xlsx", True, "#1 Raw Time Data"
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