+ Reply to Thread
Results 1 to 17 of 17

need vba code for MS Access to split the table and export it to excel

  1. #1
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    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.

    Regards,
    Girish

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: need vba code for MS Access to split the table and export it to excel

    Here is an example for you

    http://stackoverflow.com/questions/3...xcel-using-vba

    or

    https://bytes.com/topic/access/answe...e-export-excel
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    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
    Attached Images Attached Images

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.

  5. #5
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    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.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: need vba code for MS Access to split the table and export it to excel

    I've given you comments in the code in the lines you need to change.

    Please Login or Register  to view this content.
    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.

  7. #7
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    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.
    Attached Images Attached Images

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.

  9. #9
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    Re: need vba code for MS Access to split the table and export it to excel

    Thanks for the information, can you please let assist us on the 2nd requirement which I have given above. Please

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.

    http://r937.com/relational.html

    http://www.accessmvp.com/strive4peace/

  11. #11
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    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.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    Re: need vba code for MS Access to split the table and export it to excel

    Hello,

    Please someone assist me on the above requirements at the earliest possible. Thank you.

  13. #13
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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"

  14. #14
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    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.

  15. #15
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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
    PHP Code: 
    SELECT Details.[Service Provider], Details.[Account Number], Details.[Account Name], Details.[Bill Date], Details.[Service Number], Details.[Charge Category], Details.[Carrier Code], Details.[Charge Description], Details.[Call Date], Details.[Start Time], Details.[Called Number], Details.[Terminating Number], Details.SourceDetails.[Discount Amount], Details.[Analysis Code], [Analysis Code].TYPEDetails.[Analysis Code Desc], Details.[Service Class], Details.[End Date], Details.MessagesDetails.SecondsDetails.[Data KB], Details.[Cost Excl Tax], Details.[Cost Inc Tax]
    FROM Details INNER JOIN [Analysis CodeON Details.[Analysis Code] = [Analysis Code].[Analysis Code]; 
    Next using the above query, create a new query, in this case TAX using the following SQL statement.
    PHP Code: 
    SELECT Details.[Service Provider], Details.[Account Number], Details.[Account Name], Details.[Bill Date], Details.[Service Number], Details.[Charge Category], Details.[Carrier Code], Details.[Charge Description], Details.[Call Date], Details.[Start Time], Details.[Called Number], Details.[Terminating Number], Details.SourceDetails.[Discount Amount], Details.[Analysis Code], [Analysis Code].TYPEDetails.[Analysis Code Desc], Details.[Service Class], Details.[End Date], Details.MessagesDetails.SecondsDetails.[Data KB], Details.[Cost Excl Tax], Details.[Cost Inc Tax]
    FROM Details INNER JOIN [Analysis CodeON Details.[Analysis Code] = [Analysis Code].[Analysis Code]
    WHERE ((([Analysis Code].TYPE)="TAX")); 
    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.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    06-12-2013
    Location
    india
    MS-Off Ver
    Office 365
    Posts
    163

    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.

  17. #17
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Export whole table with headers from Excel to Access
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-06-2016, 03:59 AM
  2. VB Code to export data from excel to Access
    By rizmomin in forum Access Tables & Databases
    Replies: 6
    Last Post: 11-29-2015, 07:33 PM
  3. VB Code to export data from excel to Access
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-25-2015, 12:58 PM
  4. Using Excel VBA to Export data to Ms.Access Table
    By ahmed_one in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2013, 01:54 AM
  5. Export an Excel Tab to Access (Table)
    By ewong in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-04-2012, 07:12 PM
  6. Opening Excel from Access, running Code in Excel then export back to Access
    By JungleJme in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-28-2012, 08:49 AM
  7. How to export Excel data to an Access table
    By Pixar in forum Excel General
    Replies: 4
    Last Post: 10-28-2008, 01:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1