need vba code for MS Access to split the table and export it to excel
Hello Team,
I have a table in the access which is having more than 10 lakh rows, So I need the code to export the data to excel by a specific range of rows. For example I need to export 65000 rows for some work book and 75000 or more for other work books. Please help me to get the code to split and export the data to the multiple work books at the earliest possible. Thank you.
Note - I am new to the access, please give me an step by step guide to run the query.
Re: need vba code for MS Access to split the table and export it to excel
I tried to use the code given in the link https://bytes.com/topic/access/answe...e-export-excel but getting an error as shown in the screen shot given below. please assist me in getting the VBA code at the earliest possible. You can just assume that I have 135000 rows in my table and want to split 65000 in one table and another 65000 in one table and remaining in other table. In each table I need to copy 65000 rows. Kindly help me to get the correct code.... Please please please
Re: need vba code for MS Access to split the table and export it to excel
did you change the code to reflect your tables and files. This is just an example. You will have to modify it for the names of your files, and tables and field names. I cannot do that for you as I do not have your files.
Re: need vba code for MS Access to split the table and export it to excel
can you please highlighted the things on the below code that what are the changes that I need to do. so that I can replace those things with my file name and table name. Kindly do the need full at the earliest possible. Thank you.
In order for this code to post, I had to place a space in the word S ELECT. When you use the code make sure to remove the space. Note this code only splits the table. You will then have to manually export the tables to excel. The other code which is similar also exports the tables,but you will have to amend that code also for your particular situation.
Last edited by alansidman; 09-10-2016 at 08:58 AM.
Re: need vba code for MS Access to split the table and export it to excel
Hello Team,
I am still getting error after did all the changes as suggested above. please find the screen shot given below for you reference and one more thing if it not works, you can suggest me one more thing which is given below.
1. I have two table, in 1st table I have 3 letter codes in the 3rd column.
2. In 2nd table 1st column is having 3letter code and in 2nd column i have the code description.
3. So in the 1st table I need to insert one column after the 3rd column and need to do the vlookup/dlookup to get the code description and split the table based on the code description value. the table name should be same as the code description.
4. then I need to export all the tables to one excel, one table in each sheet and sheet name should be same as the table name. Kindly do the needful.
Re: need vba code for MS Access to split the table and export it to excel
I'm guessing that you don't ADODB activated in your references. Open the VBE, click on references and make sure that Microsoft Active X Data Objects is selected. Also check to see if you have any Missing References listed in the reference section. Make sure that you add them if they are listing as missing.
Re: need vba code for MS Access to split the table and export it to excel
In order to provide you with a SQL statement for that, you will need to provide
1. the table names and the field names for each table. Which fields are the primary and foreign keys .
2. the criteria for which you wish to filter the tables.
3. vlookup is an excel function and not an Access function. You will need to build a query to do that and the above information is needed.
Have you considered a tutorial on Access. It seems that you are getting deeper into this and need more expertise before jumping into what seems to be some advanced needs.
Before proceeding, I suggest you go to the following links and prepare yourself for fully understanding Access. It is not as intuitive as Excel and has a very steep learning curve.
Re: need vba code for MS Access to split the table and export it to excel
Thanks for sharing the link, please find my answers given below and kindly do the needful.
a.I have attached two spread sheets for your reference, the table name will be same as excel file name I will import the same files to the access.
b.in details workbook/table I have the analysis code in the "O" column and in other workbook/table named Analysis code I have analysis code in C column and Type in E column.
c. what I need is create one column in details workbook/table after the analysis code column and name the column as "type".
d. on the new column I need to update the type based on the anlysis code by matching with the 2nd table/workbook.
e. then split the table based on the names given in the type column and table name should be same as the names given in the type column.
f. export the all the splitted tables to one workbook with different sheet and the sheet name should be same as the table name.
Re: need vba code for MS Access to split the table and export it to excel
What is the common field that the two tables will be joined on to get the Type field. If I look at Analysis Code Description in both which appears to be logical, I would have expected some commonality. But I don't see that. Please clarify the field that the two tables should be joined on to get your "TYPE"
Re: need vba code for MS Access to split the table and export it to excel
On both file you can find the similar column named Analysis Code.
For example, on the details work book you can find analysis code stating TAX,SMS,INR,DIS.
In Analysis code work book also you can find the column Analysis code, What I need is match the analysis code between two workbooks/tables to get the "TYPE" (eg: If SMS then the type should be SMS-MMS National-International, even if the code is MMS then also the type should be SMS-MMS National-International, if its INR then the type should be Voice International Roaming.)
Please let me know if you need further details. Thank you.
Re: need vba code for MS Access to split the table and export it to excel
I hope I am understanding this correctly.
c. what I need is create one column in details workbook/table after the analysis code column and name the column as "type".
d. on the new column I need to update the type based on the anlysis code by matching with the 2nd table/workbook.
For the above, here is your SQL statement to create this query
Do this for each Type you want, changing the criteria for the TYPE. Save each one with a new query name. Once you have done this for each TYPE, You can export each query to excel by highlighting the query in the Navigation Menu and then clicking on the ribbon on External Data and selecting Excel.
This will give each type in a separate Excel file.
In the attached, I have created the join query (query1) and then created a Tax query. In this manner, you can develop your further queries from this example.
Re: need vba code for MS Access to split the table and export it to excel
Thank you for taking the time and build the query, on your query we need to manually export the table based on the value given in the Type but my requirement is that it has to build the all the tables automatically as per the values given in the Type column. Please assist me on how to do that. Once the table has been created I will manually export all the tables to one work book. Kindly do the needful. Thank you.
Re: need vba code for MS Access to split the table and export it to excel
This is a teaching forum. It is not a please do my work for me forum. Suggest you build all the queries manually. Once you have them created come back and ask instructions on how to convert one of them to VBA and then you will be able to build them all in VBA. If you require that someone do all the work for you, then you will either need to hire a consultant or use our Commercial Services Forum. I have given you the tools to build the queries. It is now time for you to do some of the heavy lifting.
Bookmarks