+ Reply to Thread
Results 1 to 24 of 24

Need help to Export Data from Multiple Worksheets to one Access Table

  1. #1
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Need help to Export Data from Multiple Worksheets to one Access Table

    Hi All,

    I am very new to access part and i got the project and i need help to make it happen my query is.

    I have excel sheet which is spooled from application & after spooling total 5 worksheets will come to workbook and i want to export only 3 tab data from excel to access table..

    1) First tab first move to access table
    2) Second tab append to that first data export to access table
    3) same for 3rd tab and give table name as excel workbook name...

    i appreciate if any one help me out with this , i tried searching over in google but i unable to find the appropriate solution.

    Hoping this will happen here

    Thanks - Naveed
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Are you looking for the manual procedure or an automated one? If manual, then use the import wizard in Access. If you are looking to automate, then you will need to learn about the Do.Cmd TransferSpreadsheet command which you will have to run three times.

    http://msdn.microsoft.com/en-us/libr.../ff844793.aspx
    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
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Yes I am looking for automated because i have 11 Excel Workbook and each workbook has 5 tabs and i need data from 3 tabs only

  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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Key issues:

    Are the excel files all located in the same directory?
    Do they have similar names?
    Do the three sheets you want to import have the same names in all the files?
    Will you be creating 11 tables? Or will you be importing and appending to one table?
    Is your data normalized in all sheets?
    Does your data in all sheets have the same field names?
    What version of Windows are you running?
    Is this a one time event or will it be recurring? If recurring, will the table(s) be cleared of all data or added to the existing table(s)?
    Do the table(s) already exist? Are they bound to form(s)?
    Where will you activate this automation from? Access Form?

    In order to write VBA to accomplish your task, these are all questions that need to be addresses.

    Additionally, what is your VBA skill level?

  5. #5
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    1) Yes all the excel files will located in same directory
    2) the name of file will be like this "Purchase Register - " & two digits only differenct like (BF,CO......)
    3) Yes that three names are same in all files like this (sheet5,sheet4,_1,_2,3) & i need to export only _1,_2,3 sheets
    4) No all 11 tables, i will macro excel file once i run the macro , macro ask me to select the file and create once access file and export that 3 tabs in access one table
    5) yes
    6) yes field names are same
    7) windows xp in office and win 7 in home
    8) no macro should create access file and forms for criteria below is the explanation
    -> based on that data , first consoldate all that 3 tabs in one access table
    -> next based on that data need create four query which will be in form (1st is for Current date data filter from batchcloseddt filed , 2nd is for Purchase reg filter yesterday date from batchclosedt filed , 3rd is for match credits filter yesterday date from creditdt filed & <> current dt from batchclosedt filed, 4th is for unrealised chqs filter blanks from creditdt filed & filter blanks & current dt from rejdate filed or filter does not end with R from trtype filed)
    9) yes

    additionally , i am very good in Excel VBA & little bit in access
    ->

  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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Here is some code that I have used in the past to import multiple spreadsheets into Access. I would modify this to only bring in the sheets that you want. Each import will produce its own table. Once you have imported all 33 sheets, then I would create a Union Query to join them all into one. I would then use that Union Query to generate a Make Table Query which should be the consolidated results of the 33 sheets. I would then run delete query to delete the 33 tables, leaving only the consolidated table. Since you have VBA skills, I suggest you try this and post back with issues that you may have. In the meantime, here is some code to get you started.
    Please Login or Register  to view this content.
    Since you are using two different operating systems, you will need to put an IF statement in your Code to select the appropriate path as they are set up differently in XP and WIN7.

    As a suggestion, when you build your UNION Query, build it in the Query Builder and then copy the SQL statement to your VBA.

  7. #7
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Hi alansidman sir

    i tested your provide code and its work fine but before testing that code i make my excel workbook in proper manner like this

    after spooling excel workbook from application excel workbook look like this (Sheet5(This sheet will be empty) , Sheet4(This sheet also will empty), _1(In this sheet headers will start from row 5), _2(In this sheet headers will start from row 5), Sheet3(In this sheet headers will start from row 5))

    so first i delete sheet5 , sheet4 and then next delete first 4 rows from rest 3 sheets and then run that code and that work perfect.

    now what my observation is , is there any way macro should leave first 2 sheets and import from next rest 3 tab from 5th row

    hope my explanation is clear !

  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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Added a couple of lines to not import sheets 4 and 5.

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Thanks Sir

    but one more thing sir that code will import data from 5th row only or else from 1st row, however excel data its start from 5th row onwards...

    once its done then we can move to union query sir...

    Thanks - Naveed

  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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    You need to clean your data in the excel spreadsheet before importing to Access. Suggest you write VBA (which you claim to know) to delete the rows you don't want to import. Access likes to have its data clean or it will cause havoc with the importation. Alternative is to write some complex VBA. I don't bother and just delete the data I don't want imported as it is cleaner and will cause less chance for error.

    Here is a link on UNION queries. It needs to be written in SQL.

    http://www.w3schools.com/sql/sql_union.asp
    Last edited by alansidman; 01-23-2014 at 12:05 AM.

  11. #11
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Thanks for this link but i already started learning from this site every weekends

    i can write UNION Query by the union query i can create new table but i don't know how to delete importing tables

  12. #12
    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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table


  13. #13
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Thank you sir , i will get back for next thing tonight

  14. #14
    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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    This is a favorite site for me to refer starting Access development.

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

  15. #15
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Really nice sir thank you very much for guiding & teaching me Access & i will promise sir i will become good in Access database in coming future

  16. #16
    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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Just stumbled on this site while searching for something else. Thought you might be interested.

    http://www.databasejournal.com/featu...soft-Excel.htm

  17. #17
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Thank you sir,

    sir i need some clarification , suppose if i create union query to consoldate all the tables after that i will delete that imported tables ok, then it will not affect the consoldate the table ?

  18. #18
    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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    After creating the UNION query, you will need to do a MakeTable Query. If you do not do the MakeTable Query before deleting the imported tables, your Union will be empty.

    Once you have created the UNION query, do not delete it as the next time you import new tables, it will be automatically populated for a new make table query so long as the imported tables are named the same as the original imports. I trust this is clear. If not post back for further clarification.

  19. #19
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    sir how to create this (MakeTable Query).

    UNION Query i will not delete, however its required for next Excel file imported tables, so only i want to delete 3 imported tables after that i will consoldate by running the UNION Query right.

    so what i understand here is, after running UNION Query all the data will store in UNION Query from Union Query you want me to create New table right sir (by using the Insert Into SQL Syntax) is it right sir..

  20. #20
    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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Open the query builder. On the ribbon. Create. Select Query. Select the Union Query as your recordset. Now drag the fields you want in your new table onto the grid. At the top, change the selected icon from SELECT to MAKE TABLE. Run the query. Follow the instructions from the wizard.

    If you wish to see the SQL statement to use in VBA, once you have this working, then with the make table query in design mode, click on the upper left hand corner where you see the icon for a data sheet, and change it to SQL.

  21. #21
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Ok sir thanks for the guidness , right now i am in office once i left to home then i will try this and i will inform you

  22. #22
    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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Here is a visual explanation of what I just described to help you.

    http://www.databasedev.co.uk/make-table-query.html

  23. #23
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Sir i followed your provide steps to create make table from union query but while executing its giving error

    - first i create a union query and run also next after that i create one more query then i select the make table and drag all the headers from union query and when i click on run its giving error

    please see the attached error image & guide where i am doing mistake
    Attached Images Attached Images

  24. #24
    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,803

    Re: Need help to Export Data from Multiple Worksheets to one Access Table

    Unable to diagnose issue from the picture. I have not had this issue myself and do not understand what happened. Here is an alternative way to do it. I have not used this approach but Roger is an Access MVP.

    http://www.pcreview.co.uk/forums/mak...-t1179118.html

+ 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. 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
  2. Export Excel data into Access table - overwrite table data
    By Jonsocks in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-08-2012, 12:05 PM
  3. How do I export data with unkonwn numbers of table from Access to Excel?
    By graceey82 in forum Access Tables & Databases
    Replies: 0
    Last Post: 10-09-2009, 03:04 AM
  4. 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
  5. Export Access 97 to Excel on multiple worksheets extra sheets produced
    By syoung4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2007, 09:50 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