Having followed this post:
http://www.access-programmers.co.uk/forums/showthread.php?t=171271
By calling (example):
I can now create an Excel file with seperate sheets.
What Im trying to do is Export my data from Access into a predefined Excel file that contains formulas.
I would link the Access data to the Excel file but when the Excel file is open it locks Access.
I presently have a command button in Access to append the data to a new table and then open my Excel file (which is linked to the new table). This avoids the locking problem.
I then auto open the Excel file however (for some unknown reason - and after exhaustive searching) The Excel file (when opened via code in Access) doesn't "refresh on open" even when directed to do so in the Excel file.
I do have a refresh cmd button in Excel but "you can take a horse to water".
Im looking for a solution to get my Access data into a mpre formula Excel file with the minimum of fuss.
thanks for reading.
http://www.access-programmers.co.uk/forums/showthread.php?t=171271
By calling (example):
Code:
Function EXPTOEXCEL()
DoCmd.TransferSpreadsheet acExport, , "MyTable1", "C:\Access\Access to Excel.xlsx", True
DoCmd.TransferSpreadsheet acExport, , "MyTable2", "C:\Access\Access to Excel.xlsx", True
End Function
I can now create an Excel file with seperate sheets.
What Im trying to do is Export my data from Access into a predefined Excel file that contains formulas.
I would link the Access data to the Excel file but when the Excel file is open it locks Access.
I presently have a command button in Access to append the data to a new table and then open my Excel file (which is linked to the new table). This avoids the locking problem.
I then auto open the Excel file however (for some unknown reason - and after exhaustive searching) The Excel file (when opened via code in Access) doesn't "refresh on open" even when directed to do so in the Excel file.
I do have a refresh cmd button in Excel but "you can take a horse to water".
Im looking for a solution to get my Access data into a mpre formula Excel file with the minimum of fuss.
thanks for reading.