Solved Import Data from Access To Excel same WorkSheet.

[alayash03]

I have an Excel file which file have 29 sheets. And all of the data records in a sheet which name is [DataStore]. Others all sheet with linked this workSheet for data collected for analysis sales and others things. I want to import daily records data from ACCESS in query name is [QrySalesDailyRecord] to EXCEL file which worksheet name is [DataStore]. In excel in already have 4815 Records. Everyday end of the work I have to open both file and copy and paste ALL data.
Need to complete this work with a button.

What is the best ways to import data from (a) call in EXCAL file to importing or (b) call ACCESS file to export ?

NOTE: I have an access app with FE & BE.

 

[ebs17]

Counter question: What is your mental control center for all these processes. There can only be one boss and only one control center. You have to know what you are doing, log it if necessary and keep an overall overview.

For me that would be Access, because it's much easier and nicer to create forms and thus a user interface. Data storage in a database is more structured and sustainable - you can certainly do that better from Access.

 

[arnelgp]

you can do it in Access.
Copy query, QrySalesDailyRecord and name the New copy, same name as your Worksheet, DataStore.

Now, use DoCmd.TransferSpreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "DataStore", "thePathAndWorkbooknameHere", True

everytime you run this, it will append New (if there are new records), to DataStore Sheet of Workbook "thePathAndWorkbooknameHere".

 

[alayash03]

you can do it in Access.
Copy query, QrySalesDailyRecord and name the New copy, same name as your Worksheet, DataStore.

Now, use DoCmd.TransferSpreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "DataStore", "thePathAndWorkbooknameHere", True

everytime you run this, it will append New (if there are new records), to DataStore Sheet of Workbook "thePathAndWorkbooknameHere".

Nice job ! its work.
I tried many times with
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "QrySalesDailyRecord", "E:\POS\Data File\Excel_File\Sales And Invoice Recovery", , , , True

Now it work only replace with
acSpreadsheetTypeExcel12Xml

Thank you.

 

[Isaac]

I have an Excel file which file have 29 sheets. And all of the data records in a sheet which name is [DataStore]. Others all sheet with linked this workSheet for data collected for analysis sales and others things. I want to import daily records data from ACCESS in query name is [QrySalesDailyRecord] to EXCEL file which worksheet name is [DataStore]. In excel in already have 4815 Records. Everyday end of the work I have to open both file and copy and paste ALL data.
Need to complete this work with a button.

What is the best ways to import data from (a) call in EXCAL file to importing or (b) call ACCESS file to export ?

NOTE: I have an access app with FE & BE.

All things considered, I have had fewer problems come up when using a combination of the two that begins with exporting from Access.
If you begin the process using Excel, you will start to find many limitations (depending on what's inside the Access query), whereas you will find virtually no limitations on exporting from access to excel. You can then follow that with some Excel automation to massage the data if needed.